進度條

「不是工程師」可以邏輯推斷出來就不要多加欄位?淺談資料庫第三正規化(3NF)

抽象的正規化數學邏輯讓你覺得很困惑嗎?不然來看看這個講法吧!

作者: 進度條編輯群 更新日期:

此文章也有影片介紹,可以搭配影片一起學習!

01. 第三正規化與正規化想法探討 (所屬課程)


以下正式開始文章


通常一個好的資料庫設計,都會「盡可能的」符合第一至第三正規化,而在前面的章節中,相信各位對第一及第二正規化的目的及概念,已經都了初步的理解,那接下來,小編會為各位介紹第三正規化的範疇。


(為何說「盡可能的」而不是「一定」,因為例如MySQL 8.0裡面加上了JSON 的資料欄位型態,這個幾乎100%違反第一正規化,但是實務上的取捨還是讓JSON型態進入了各大關聯性資料庫,甚至可以說各大熱門「NoSQL」資料庫幾乎可以等於一個大型的JSON資料表)
 

 

 

adult-adventure-background-220147.jpg

 

第三正規化(3NF)的目的,是在滿足第一(1NF)及第二正規化(2NF)的條件下讓所有非鍵值欄位,都只相依於該表的主鍵,換句話說,所有的非鍵值欄位都只跟主鍵值有依賴性,這樣的目的就是為了消除資料表中的遞移相依(Transitive Dependency),來確保每個非鍵值欄位都是獨立無關的。

 

 

白話來說,如果可以用其他欄位計算或推斷得出,就不要設立一個欄位(field)去紀錄。

 

 

例如設計一個訂單的資料表

那你可能會想要這樣寫

Table: order (訂單)

 id (PK 訂單編號) 

 ordered_date (訂單建立時間)

 user_id (FK) 

 note

 status

1  Wed  5 Feb 2020 00:00:01 CST   1  寫一些額外注意事項   未付款 

 

Table: order_items (訂單商品)

 id (PK) 

 name

 price 

 quantity(數量) 

 total 

 order_id (FK)

 1  雞腿飯  100  2  200  1
 2  紅茶  30   1  30   1

 

所以可以很簡單的利用Total算出這筆訂單 order_id = 1 
總合是 200 + 30 = $260

 

不過這裡就違反了第三正規化(3NF),因為total 其實是由 price x quantity 來的,移除掉也不會影響到功能實現,並且也跟PK鍵無直接關係,這就是所謂的遞移相依(Transitive Dependency)。

 

而且像這樣的欄位可能會導致所謂的「更新異常」(Modification Anomalies)。例如這個訂單未付款而且還沒出貨之前,使用者打電話給廠商說他雞腿飯和紅茶要各加一。結果工讀生只改了quantity這項,忘了改total,就會導致於結果錯誤。

 

Table: order_items (訂單商品)

 id (PK) 

 name

 price 

 quantity(數量) 

 total 

 order_id (FK)

 1  雞腿飯  100  3  200 (應該是300)  1
 2  紅茶  30   2  30 (應該是60)  1

 

當然系統寫得好,工讀生都用程式提供的介面改就比較不會有問題,但是試想今天如果是一個工程師在維護資料庫,他不小心漏看了quantity 與 total 之間的關係,就有可能造成資料的不一致。第三正規化(3NF)的實現就可以避免這個問題。

 

charts-data-desk-669615.jpg

 

以上就是第三正規化的概念介紹。在這邊小編對第一到第三正規化來做出一個簡單的複習與總結:

 

  • 第一正規化:定義主鍵值(primary key或unique key)以及剔除重複資料,來打好資料表的基礎
  • 第二正規化:符合第一正規化,釐清資料表裡面每一個資料欄位的關係,把部分相依的資料另開表格作儲存,來確保每一非鍵值欄位必須「完全功能相依」(Functional Dependency)於主鍵
  • 第三正規化:符合第二正規化,且每一個非鍵值欄位都必須不得和其他非鍵值欄位產生相關性

 

而正規化最重要的目的,就是重新整裡關聯式資料庫的表格及欄位,降低資料重複及減少相依性的過程。而這個過程中,對於減少資料庫空間上的浪費,以及強化維護與操作上的效率和正確性,都有很大的幫助。

 

另外值得一提的就是,基本上執行正規化就等於是原本的Table中的column會變少,而用新的Table 表來表示相關性,故一定會增加Join的次數與SQL指令的複雜度。

 

adult-back-view-data-1181345.jpg

 

當然,在每一個龐大且複雜的系統中,也許因為業務條件與商業邏輯上,未必能完全符合這規劃的條件來做設計,而實際上正規劃也並非只有上述三式而已,越高程度的正規化,也許對系統的彈性有大幅度的影響,但一定程度的正規化,絕對是未來維護與效能上,不可或缺的考量。

 

 

 

接下來廣告一下,如果對資料庫設計和網站架設有興趣的話,可以考慮參加我們課程:


從零開始的SQL語法與資料庫設計 - 以MySQL來攻略 是MySQL的初階至中階課程,除了基本SQL語法的教學外,也包含資料庫設計理念與實作。對於一直搞不懂資料庫的同學會有相當的幫助喔!


快速開發,從頭教起的Ruby on Rails後端之旅 是後端網站APP課程開發課程,課程中包含詳細的購物網站架設資料庫設計教學,內容涵蓋SQLite, MySQL, PostgreSQL,以及最後的募資平台(含資料表設計),可以參考看看喔!

 


最後,如果你喜歡我們的文章,別忘了到我們的FB粉絲團按讚喔!!

圖文系列教學: 不是工程師也可以看得懂的程式名詞解說!

Small logo

進度條編輯群

進度條編輯團隊