最後的兩回中研究別人怎麼設計資料庫。
這回主要探討電商，因為沒有現成的範例，所以只好去找一個大略可以講的範例。

分析

因為電商，所以最主要是販售物品為主，會有庫存、商品、結帳，讓顧客如何順利地購買產品、保留過往資料以供糾紛調查是最重要的。
以下是我找到的電商模擬資料表:

從資料表上分析

先從user來說，首先要建立的就是顧客資料。
於是有了user、user_address、user_payment三張表。
而關係到能否正常運送、付款，所以把關鍵功能拿出來開表也是很正常的。
為了避免欄位過多，把不相干的項目拉出去就是資料庫正規化。
不斷比較各個欄位與該列表的主鍵關係，把表拆出，即2NF、3NF。

再來是product，產品。
因此有了product_category、product_inventory、discount三張表。
產品的分類、庫存、折扣，而其中庫存最重要，因為它關係到能否正常賣出東西。
雖然我不知道為什麼不直接把product_inventory的外來鍵設定成product的主鍵，這樣能更直覺的去Mapping。總之，多開這一張庫存表可以知道販售的情況、庫存異動情況。在價格變動上，想追蹤也是可以這麼做的。
而discount表，則是拉出來，獨立處理這商品套用的折扣，有個active，可以啟用、關閉。我覺得這張折扣表上可以加一個修正價錢，等於有一段時間打折能直接套用這折扣價，照原本設計是用百分比去折扣。
我覺得這商品是最難的規劃，會有多種不同價格，怎麼記錄他異動等等。

再來是購物車shopping_session、cart_item。
因為我們可能會換電腦操作，所以不能放在Cookie，會需要購物車幫我們紀錄要買哪些資料。
在這cart_item裡面就是不斷添加產品product，可能有N個產品，之後這N個產品都算在同一個shopping_session上。
這段我自己覺得只要cart_item就好，就是購物車裡放哪些東西，因為只是用來暫存用的。

結帳完成訂單order_details、order_items、payment_details。
在最後流程就是使用者要能夠結帳、產生訂單。
把cart_item的資料都拿出來，放到order_items，之後點下結帳時，用程式的”交易”的寫法，去檢查能正常扣除庫存，就建立這筆訂單。
此外，訂單建立就是付款了，只要照著付款流程付完款，在更新payment_details即可。
而order_details則是記錄這筆訂單總金額、備註等等。
如果被刪除了，是否要自動把庫存加回去就看個人需求，總之能做到。
如果怕有糾紛問題就是多存一份沒關聯的字串資料。把當時結帳的商品名稱、款項都寫明，已備不時之需使用。

此外，商品那些都做成軟刪除，因為未來可能會需要關聯，所以能看到有deleted_at欄位。
deleted_at、modified_at、created_at在資料庫的表建議都建立，以供人工好追蹤。
然後，發現好像大多數資料表設計如type類型都設定成varchar，應該在拉出去一層關聯會比較好，往後類型被改會比較容易更新，只取代一個跟取代N個的差別。

從型別上分析

時間它是用timestamp，但如果SQL server用datetime2會更好。
商品的價格上price用decimal，跟我前面幾回探討過的型別有關，就是選沒有誤差精確數值。
在id上是int，但其實也可以用bigint如wordpress就是用bigint。
文字用varchar。

總結

從這個資料庫來看，已能滿足大多的需求，我覺得關鍵在這系統主要想幹嘛，在把那部份特別補強設計架構，就八九不離時了。
主要的功能表能設計的穩固，後面要擴建就能容易些。
此外，我覺得還能再加上個折價券表，可以套用在最後的結帳上。

參考資料
https://resources.fabric.inc/blog/answers/ecommerce-database-design-example?fbclid=IwAR388b0qI1maAfEDWguD9y4pOEywv3TG2gJIEq4jDW2jNQsZWdYfdTSVr-I

先讓自己吃飽，身體的能量夠。

我從遊戲裡面學到一件很重要的事，就是「選擇面對、迴避都是一條好路」。
在過去，我往往每件事都選擇直接面對，但發現這樣並不是好的，因為壓力會很大。

以遊戲來說

聊一下這遊戲叫做「血源詛咒」，作者是宮崎英高。
我們一進遊戲就會被一堆殭屍、亡靈給追殺，如果遇到單一隻那還能免強解決，如果一次遇上三隻以上，那就難逃一命。
而在這遊戲中，我們的目標就是打倒每個關卡點的王，所以遇到的那些小怪都是能免就免，除非自己想切磋、有必要，不然不要主動去引小怪。

以人生來說

我從上面的遊戲經驗得到一個自己的見解。
把人生的小目標比喻成關卡點的王，而你就是只是在選擇一條相對好走的路。
遇到一個工作，如果發現不適合，還有別條路可走，那就自己決定要不要硬撐。有時及早退回上一步重新選擇也是方法。

調解壓力的方法

壓力這種無形的東西，容易放在腦子裡一直積著，很抽象，你想解它，卻無從而解的被約束。
我的方法很簡單，其實就是拿筆、紙，把面對的壓力寫下來(具象化它)，寫完後把它放到一個封印的盒子裡面，告訴自己我下次打開盒子前它都跟我無關。

雖然這看起來有點神奇，但對我而言是有效的。
而有些被封印的壓力，時間一過後也煙消雲散了，就只是一時的不愉快、解不了。

把那些壓力封印後，繼續專注在現實那些能發生改變”踏實”的事，走在一條新的路上。

最後，還是一樣，肯定有人也遇到相同問題，所以我在這提供自己的想法，協助遇到跟我一樣問題的人。

剩下兩篇就30了，後面想看一下其他有規模的資料庫是怎麼設計的。
這回以wordpress為例子，然後注意wordpress是用mysql，所以在型別上會跟SQL server有些不一樣。

資料型別上

表的主鍵ID是使用BIGINT，可以存(-9223372036854775808 ~ 9223372036854776807 )
時間則是用DATETIME，MYSQL的DATEIME可以存‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
如果是用SQL Server要用DATETIME2，可以存0001-01-01 ~ 9999-12-31
除了主鍵ID外的整數用INT
文字則是VARCHAR
在MYSQL上
TINYTEXT: 256 bytes
TEXT: 65,535 bytes => ~64kb
MEDIUMTEXT: 16,777,215 bytes => ~16MB
LONGTEXT: 4,294,967,295 bytes => ~4GB

單一資料表上的設計

wp_comments:
1.透過comment_ID、comment_parent搭配可以設計出樹狀階層留言。
2.發現comment_type是varchar，不是再開一張表用ID關聯回去，就直接存文字。
3.發現有兩種時間comment_date_gmt、comment_date，可能是要處理跨時區問題。
4.發現很多需要另鍵表再做關聯的都直接用varchar，如comment_approved、comment_type。

wp_users:
1.ID是wp_post、wp_comments的外來鍵，可以知道操作者。
2.email只要varchar(100)。

wp_posts:
1.ID是wp_comments的comment_post_ID外來鍵。
2.對日期都多存一個gmt。
3.多存一個comment_count，可能是為了讓要取得留言數不用再做一次sum，讓效能好些。
4.很多需要另鍵表再做關聯的都直接用varchar，如post_status、comment_status、post_type。
5.使用了GUID(255)不知道是要做什麼，因為本身已有個ID可以識別了，且在其他表也沒看到它的存在。
6.有建立時間、修改時間。

wp_term_relationships:
1.object_id、term_taxonomy_id用來做多對多的表，文章、分類。一篇文章有很多個分類，一個分類有很多個文章。

wp_term_taxonomy:
1.ID、parent可以跟留言一樣做出樹狀結構的分類。

wp_options:
1.options_name、options_value，儲存像是字典型別的系統資料，每個資料名稱與數值都1對1。

wp_usermeta、wp_postmeta、wp_commentmata:
1.key、value，儲存字典，用於再網頁上印出metadata，SEO使用。

小結

wordpress很明顯就是文章、留言導向，所以這兩張表設計得比較用心，有多去紀錄一些額外資訊如修改日期。
其他相對不重要的表，則設計的堪用就好，像是一些型別應該要在開個表做個ID在關聯回去，但他直接用varchar文字來做。
此外，考量到跨區問題，所以有多存一個gmt時間，但目前不知道他會怎麼用它。
且一些計算欄位的count都直接開出來，而不是用資料庫算(我是覺得不太好，但如果為了效能是可以加)。
而在多對多的表命名上則是命名relationships，這樣一眼就知道這是個來做關聯用的表。
對於系統定義值以字典方式儲存，就只要key、value，就能避免無限增加的欄了。

最後，多參考一些套裝軟體的資料庫規劃，會更直覺得設計出彈性的資料表，以上。

參考資料
https://blog.longwin.com.tw/2009/10/mysql-text-field-type-length-limit-2009/

為什麼要學資料庫呢?為了工作?為了自己興趣?為了學歷?

我學操作資料庫的原因比較跟興趣有關。
為了能夠更知道像是undertale(地下傳說)、魔物獵人:世界(MHW)、血源詛咒Bloodborne那樣的遊戲背後的儲存體該如何設計。
如果我不會資料庫，把他們全存在Excel，一個數值一個欄位，可能要存到天荒地老。

寫個電玩遊戲出來會用到，如果有從事後端工作也會用到，學一下其實算一石二鳥。
雖然說以這淺談並不會講到很深，但卻能幫助我先整理好這片地圖，需要時在快速回來參考即可。

雖然市面上大多數都用來設計應用類程式，因為身在台灣就這比較好賺。
但老實說要帶給人成就感還是遊戲更好一些。至少它不是冰冷的(?)

再來學習設計資料庫有助於鍛鍊我抽象思考的能力，把屬性分類歸位。
能夠從看到畫面轉化成數值再變成表格，即一種逆向工程的能力。

總之，資料庫對我而言大概就是這樣了，協助我提升未來再儲存資料上的架構、存取方式。
然後，我並不是追求完美的那種大神，我只要符合需求、好維護就好，雖然有各種奇淫技巧能夠不斷改善他的效能，但對我來說它就是個分類好的文件夾…，我的目標是放在程式本身外的目標跟整體上。就是把他們組再一起成為一個獨一無二的創作，讓後帶給自己感動就這樣。

這回稍微帶一下可能會用到的數學函式。

ROUND 四捨五入

1

SELECT ROUND(235.41500, 2) AS RoundValue;

結果:235.42000

SQUARE 平方

1

select SQUARE(6);

結果:36

SQRT 開根號

1

select SQRT(36);

結果:6

FLOOR 地板

1

SELECT FLOOR(25.75) AS FloorValue; 

結果:25

CEILING 天花板

1

SELECT CEILING(25.1) AS CeilValue; 

結果:26

Abs 絕對值

1

SELECT Abs(-243.5) AS AbsNum; 

結果:243.5

通常在寫表單時，我們不會一次索取所有資料，因為資料若有百萬筆，那效能可能會不好、浪費流量。
所以，這時候就需要分頁了。
但是SQL server沒有Mysql的limit功能，所以要自己組出來QQ。

稍微查了一下Google，發現已有不少現成做法，就當作紀錄記下來。

他的做法其實就是用子查詢。
就是內層先做一個有row欄位，這row欄位透過 ROW_NUMBER() OVER 去產生順序編號
製作好內部的子查詢後，再由外部把這子查詢視為一張temp_table
之後就可以用where去判斷想取哪段的row了。

但是我比較好奇的是，如果是很多left join、inner join好像用這方法會變得很亂。(?)

要:1~5筆資料。

1
2
3
4
5

SELECT * FROM ( 
 SELECT *, ROW_NUMBER() OVER (ORDER BY id desc)  row 
 FROM test
 ) as temp_table
WHERE row >= 1 and row <= 5

結果:

要:6~10筆資料。

1
2
3
4
5
6

SELECT * FROM ( 
 SELECT *, ROW_NUMBER() OVER (ORDER BY id desc)  row 
 FROM test
 ) as temp_table
WHERE row >= 6 and row <= 10

結果:

參考資料
http://bioankeyang.blogspot.com/2013/03/ms-sql-servermysql-limit.html

這回介紹日期函數的使用。
在大多的資料庫都會有一些可以用的函式，讓我們做一些特殊操作。
在這回以SQL server資料庫為例子。

SQL server日期函式

GETDATE() 取得現在的日期時間
DATEADD() 增加或減少指定的時間間隔
DATEPART() 取出日期時間中特定的部分
CONVERT() 格式化日期時間顯示
DATEDIFF() 日期相減

可用的關鍵字

可用的關鍵字參數:

GETDATE

取得現在的日期時間

1
2

Select 
GETDATE() as 現在的日期時間

結果

DATEADD

增加或減少指定的時間間隔
可以輸入負值，代表回推
正值則是往後推

1
2

Select 
DATEADD(day, 2, GETDATE()) as 兩天後

結果

DATEPART

取出日期時間中特定的部分

1
2
3

SELECT DATEPART(yyyy, GETDATE())  as 年,
DATEPART(mm, GETDATE())  as 月,
DATEPART(dd, GETDATE())  as 日

結果

CONVERT

格式化日期時間顯示，可以用格式化代碼快速轉成指定規範格式。

格式代碼:

1

SELECT CONVERT(varchar, GETDATE(), 100) AS Converted

DATEDIFF

日期相減

1

SELECT DATEDIFF(day, '2008-06-10', '2008-08-10') AS 日期相差

結果

結論

在SQL語法上比較常用的是DATEDIFF、DATEADD、CONVERT。GETDATE。
如:前1個月、今天-7天、上禮拜資料這種需求還蠻常見的。

參考資料
https://www.fooish.com/sql/sql-server-getdate-function.html
https://www.w3school.com.cn/sql/func_convert.asp

當我們一開始安裝SQL server時，系統會幫我們預設一組帳號，即最高管理員帳號。
但當我們實際開發時，勢必不會使用這組帳號，因為太過危險了。
他能夠刪除資料庫、表，哪天被入侵、盜用時整個資料庫都會有危險。
所以，在此要介紹一下新增自訂權限的管理員。

新增自訂的資料庫管理員

1.新增登入

2.填入帳、密資料

3.可以在左方看到剛新增的管理員

4.屬性

5.使用混和模式登入

6.開啟SQL Server 2019 Configuration Manager，重啟SQL server。
注意，重新開機是沒效的，一定要用這種方法重新運作SQL server

7.登入帳號

8.成功登入

9.登入後選擇原本資料庫會發現沒有權限

10.回去編輯test01帳號，把Orders的db_owner勾起來即可。

結論

這樣一來就能限制某些帳號管理特定資料庫了。

參考資料

https://ithelp.ithome.com.tw/articles/10214386

因為SQL server會有匯入舊版問題，像是把2019版的匯入到2008版的，所以要在探討另一個做法，即向下兼容的備份與還原。

備份

1.選擇generate scripts

2.會跳出框框，選擇進階、儲存成script

3.可選擇想降級的SQL server版本

4.可選擇只要匯出schema還是data

5.點Next下一步

6.也是點Next下一步

7.等待生成完畢，點OK

8.到資料夾就可以找到.sql檔案了

還原

1.雙擊剛剛匯出的.sql

2.執行他Execute

3.這時會一直跑都跑不完，中止後發現錯誤，因為用.sql匯入要把原本資料庫給砍了。
不能有相同名稱的資料庫

4.把現有資料庫給刪除

5.勾選關閉資料庫連線後點擊OK

6.此時，重新再跑一次，可以發現成功執行
刷新左方的資料庫，可以發現成功還原了。

使用.sql的優缺點

優點:
1.可以選擇產出舊版 SQL Server 的 script
2.可以自行調整 script
可能會有問題的部分:
1.特殊字元或是 binary 無法匯入
參考:https://social.technet.microsoft.com/Forums/zh-TW/1fc3c435-5b24-45bf-8cb9-1427e3392de2/293052753023383208032338420837sqlserver2001335722251043439930340?forum=sqlservermanagementzhcht
(但我發現新版2019的都已經有自動幫你補上N了所以好像不太會有問題。)
補上:
在國際性資料庫中管理字元資料，最容易的方式是使用 Unicode nchar、nvarchar 和 nvarchar(max) 資料類型，而不是相對的非 Unicode char、varchar 和 text。

2.如果想要建立相同的資料庫需要調整的內容較多。
(所以我上面做法是直接砍了。)

總結

如果.bak不行才出此下策，通常是對方機器太舊才可能用這種方式。

參考資料
https://blog.yowko.com/sql-server-backup-restore/