Database►SQL Server►Topic►2021 SQL Server Challenge

[Day5] 規劃資料表，透過資料庫正規化 - SQL Server資料庫入門

前言

這回主要淺談一下資料庫正規化，並且規劃我們的訂單資料表。

資料庫正規化

正規化的目的:

  1. 避免資料重複或相互矛盾的情形。
  2. 使資料庫在使用時能更有效率、更容易維護。

正規化:

  1. 資料庫的正規化共可分為1NF (Normal Form)、2NF、3NF、BCNF (Boyce-Codd Normal Form)、4NF、5NF 等多個階段, 不過對於一般資料庫設計來說, 通常只需要執行到BCNF即可, 而其他更高階的正規化只有在特殊的情況下才用得到。

  2. 在資料表正規化的過程(1NF 到BCNF) 中, 每個階段都是以欄位的相依性, 做為分割資料表的依據之一。

什麼是相依性?
欄位相依:就是在資料表中，乙欄位的值必須搭配甲欄位才有意義，則我們說『乙欄位相依於甲欄位』。

第一正規化規則(1NF):

1.決定主鍵:資料表中有主鍵, 而其他所有的欄位都相依於主鍵。
2.一個欄位只能有單一值:每個欄位中都只有儲存單一值,例如: 姓名欄位中不能存放2 個人的姓名。
3.消除意義上重複的欄位:資料表中沒有意義相同的多個欄位, 例如: 姓名1、姓名2…等重複的欄位。

如果不符合一階可能會造成:存取效率低、資料長度無法確定

第二正規化規則(2NF):

1.符合1NF 的格式
2.去除『部分相依性』:各欄位與主鍵間沒有部分相依的關係。

部分相依: 只有在主鍵是由多個欄位組成時才會發生，部分相依的欄位只與主鍵中的部分欄位有相依性，而與另一部份的欄位沒有相依性。

要除去資料表中的部分相依性, 只需將部份相依的欄位分割成另外的資料表即可。
因為完成第一階時容易會有資料重複問題，因此要執行第二階。

第三正規化規則(3NF):

1.符合2NF 的格式。
2.去除『間接相依性』:各欄位與主鍵間沒有間接相依的關係。

間接(遞移)相依: 是指二個欄位並非直接相依，而是藉助其他欄位來達成資料相依的關係。例如A與B相依且B與C相依，則我們說A與C為間接相依。

消除遞移相依：非主鍵屬性的欄位都只能和候選鍵相關，非主鍵屬性的欄位彼此間應該要是獨立無關的

候選鍵：欄位組合讓資料能是唯一的，並且是最小唯一

要除去資料表中的間接相依性, 其方法和除去部分相依性完全相同。例如:可再分割成N個資料表。

Boyce-Codd 正規化(BCNF)

對於大部分資料庫來說, 通常只需要執行到第三階段的正規化即足夠了。如果資料表的主鍵是由多個欄位組成的, 則必須再執行Boyce-Codd 正規化

1.符合2NF 的格式。
2.各欄位與主鍵沒有間接相依的關係。
3.主鍵中的各欄位不可以相依於其他非主鍵的欄位。

其他

正規化的另類思考:
1.不必要的分割:正規化的工作有時候不必做的太徹底, 因為過多的資料表可能會降低系統執行的效能。
2.人工的分割:有時為了增加資料處理的效率, 我們可以將已經符合BCNF 的資料表再做分割。一個資料表擁有很多欄位, 而其中又有
很多欄位根本很少用到。那麼, 我們就可以將這些很少用到的欄位分離出來, 存放到另外一個資料表中。

正規化實作－訂單

步驟1:正規化前

步驟2:1NF後

1.決定主鍵:資料表中有主鍵, 而其他所有的欄位都相依於主鍵。
2.一個欄位只能有單一值:每個欄位中都只有儲存單一值,例如: 姓名欄位中不能存放2 個人的姓名。
3.消除意義上重複的欄位:資料表中沒有意義相同的多個欄位, 例如: 姓名1、姓名2…等重複的欄位。

把因欄位單一值的欄位獨立成另一個表、決定主鍵，因為沒有重複欄位所以不用消除。

步驟3:經過2NF & 3NF 後

2NF:
1.符合1NF 的格式
2.去除『部分相依性』:各欄位與主鍵間沒有部分相依的關係。
3NF:
1.符合2NF 的格式
2.去除『間接相依性』:各欄位與主鍵間沒有間接相依的關係。

不斷比較各個欄位與該列表的主鍵關係，把表拆出。

*出貨單號、出貨日期、運送地址、客戶簽名、總計，有部分相依性
客戶姓名、客戶電話，有部分相依性
所以各自拆成兩張表

*明細流水號、出貨單號、數量、金額、備註，有部分相依性
產品、單價，有部分相依性
所以各自拆成兩張表

出貨單表的總計容易因為出貨單明細的數量*金額而變動，因此可考慮刪掉，但有時實際上又會因廠商變動而實際上硬壓上一個金額，所以還是先留著。

我在處理時覺得2跟3很難分開做，所以做完已經3階。

然後關聯

20210905修正
為了避免未來產品單價變動，造成日後撈訂單時價格改變，因此在訂單明細增加單價

結語

第一正規化做好後，第二正規化、第三正規要注意一點去做，讓結構拆開來，日後擴充會比較容易。
我自己在2~3階時有點對定義模糊，就是不斷把不相關跟主鍵去比，然後拆出來、拉關聯。(日後如果發覺錯誤會回來改正。)
此外補充一下，我覺得這種抽象的理論比實作重要，因為他沒辦法直接複製貼上，要透過動腦去分析，是累積功力的一個好方法。

Q1:我有一點疑問在＂單價＂比如往後需要調用歷史資料，而商品的單價改動，會接連造成訂單對不上，所以是否該把明細流水號增加單價?
ANS:20210905補上，針對這種日後可能會調度欄位、有糾紛問題的數值，存當時寫入的值比較穩定。因為是”歷史資料”，通常會限制成不可改，只能把整筆訂單刪除。
後來看了:https://ithelp.ithome.com.tw/m/questions/10197039?fbclid=IwAR1sdXPCl2JtFvhauc76aUcEuNIEtfcvH2TzodALPmC08HL8R-7mp74eLZc
這個問題是「需求」問題，看PM或SA想怎麼呈現
簡單的答案:沒必要
複雜的答案:要看資料欄位的「變化性」

參考資料
http://140.118.9.79/UML-SASD/%E6%AD%A3%E8%A6%8F%E5%8C%96.pdf
http://9715109.blogspot.com/2010/10/12.html
https://ithelp.ithome.com.tw/articles/10229472
https://www.youtube.com/watch?v=FgQ4QrwVNk0
http://spaces.isu.edu.tw/upload/19225/0/news/postfile_349.pdf
https://www.ptt.cc/bbs/Soft_Job/M.1623612131.A.33C.html
https://ithelp.ithome.com.tw/m/questions/10197039?fbclid=IwAR1sdXPCl2JtFvhauc76aUcEuNIEtfcvH2TzodALPmC08HL8R-7mp74eLZc