進度條

「不是工程師」外鍵Foreign key(FK)是什麼?從第二正規化(2NF)去除冗余資料談起吧!(database normalization)

資料越整理越肥?整串資料看得密密麻麻都重複的?那可能是你第二正規化沒有做好喔!

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

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

01. 第二正規化與範例 (所屬課程)


以下正式開始文章


上一篇跟各位討論到,關於資料庫第一正規化的目的與重要性,接下來會在這個章節跟大家分享第二正規化的目的與設計概念。

 

 

 


close-up-coding-contemporary-1342460.jpg

 


先讓我們來複習一下第一正規化要解決的問題。

 

資料庫就是給電腦使用的,電腦擅長的就是處理大量重複性的事物,比方說加總資料。

 

 

但是如果今天資料本身並未有一致性的話,電腦其實也無法處理,或是算出沒有意義的結果。比方說上篇提到的【整數】與【整數陣列】相加對電腦來說是需要額外判斷式處理的(比方說迴圈與if-else)。如果在放資料的時候就已經先把資料先分開來,做出一致性。那在做運算的時候就會很單純。

 

 

不過分門別類後,就會開始出現了浪費。就像你嘗試買了一堆櫥櫃整理房間,最後很可能發現櫥櫃本身比你所擁有的物品還佔空間。為了整齊,卻又花費了更多的資源空間。

 

 

 

如果換到程式上比喻,相信使用Excel過的各位一定都有經驗,隨著紀錄的筆數變多,Excel的表格也會隨之越長越肥大,很多欄位其實內容都一樣,卻還是要一直複製貼上才整齊。資料庫也是一樣,冗餘的資料就出現了。對系統而言,重複且不具重點的資料,只是浪費空間罷了。

 

 

 

architectural-design-architecture-belgio-2634202.jpg

 

 

所以如何有效降低資料重複性,就是第二正規化的目的,用上一個章節提到的表格舉例如下:
 

 

id

user

date

pay

item

shop

shoop_address

1

小明

9/30

100

螺絲

好記

台北市信義區信義路五段7號

2

小明

9/30

200

軸承

好記

台北市信義區信義路五段7號

3

小華

10/1

-200

廠商退款

興記

臺北市中正區北平西路3號

 


 

上次我們提到,在第一正規化後,我們把資料都變成單一值了。並且加入id為主鍵值(primary key),隱含著id為這張表中不可重複且具代表性的唯一值。


接下來為了方便講解,我們先把這張表命名為Order (訂單)。隨著時間的推移,這張表會擁有更多的資料,如下圖。

 

 

Table: Order
 

id

user

date

pay

item

shop

shop_address

1

小明

9/30

100

螺絲

好記

台北市信義區信義路五段7號

2

小明

9/30

200

軸承

好記

台北市信義區信義路五段7號

3

小華

10/1

-200

廠商退款

興記

臺北市中正區北平西路3號

4

小明

10/2

300

螺絲

好記

台北市信義區信義路五段7號

5

小明

10/4

200

螺絲

好記

台北市信義區信義路五段7號


 

 

這時候可以發現,許多欄位如item、user、  shop和shop_address等欄位都開始出現重複的現象,且表格的易讀性也開始降低,儘管我們用肉眼可辨識這些資料的關聯,但系統並非人腦,這樣重複的欄位已經造成容量的消耗,表格維護的難度也隨之增高。

 

 

 

而第二正規化的目的,就是完成第一正規化後,透過完全相依或部分相依(Partial Dependency)的判定方式,以減少資料重複性。所以正規化是有順序性的,可以想成是先展開後再減少冗余資料。

 

 

 

這張Order的表裡面,最冗余的資料一般會是shop_address,畢竟它最長而且一直重複。

 

 

為了解決這個問題,我們需要再多加一個新的欄位shop_id,也就是商店編號,這邊因為只有兩個店家,所以先以 s1 與 s2表示。


Table: Order
 

id

user

date

pay

item

shop_id

shop

shop_address

1

小明

9/30

100

螺絲

s1

好記

台北市信義區信義路五段7號

2

小明

9/30

200

軸承

s1

好記

台北市信義區信義路五段7號

3

小華

10/1

-200

廠商退款

s2

興記

臺北市中正區北平西路3號

4

小明

10/2

300

螺絲

s1

好記

台北市信義區信義路五段7號

5

小明

10/4

200

螺絲

s1

好記

台北市信義區信義路五段7號

 

 

拉出shop_id後很容易可以發現,其實shop_xxx可以獨立出來成Shop 表(Table),而且不會影響到原本的內容邏輯,但是還是要留一個欄位表示關聯性。所以會變成下面的樣子。

 

Table: Shop 
 

shop_id

shop

shop_address

s1

好記

台北市信義區信義路五段7號

s1

好記

台北市信義區信義路五段7號

s2

興記

臺北市中正區北平西路3號

s1

好記

台北市信義區信義路五段7號

s1

好記

台北市信義區信義路五段7號

 

這時候,我們就可以把重複的資料給拿掉,畢竟單看這個表,重複的內容沒有意義。

 

Table: Shop
 

shop_id

shop

shop_address

s1

好記

台北市信義區信義路五段7號

s2

興記

臺北市中正區北平西路3號

 

這樣看起來就乾淨許多了。

 

所以Order 表就可以整理成下面的樣子,必須要保留shop_id,不然Table Order的資料會不完整。只是現在當取用Order的資料的時候,會多一個動作,需要先去Table Shop 看這個shop_id的內容值是包含哪些 

 

Table: Order
 

id

user

date

pay

item

shop_id

1

小明

9/30

100

螺絲

s1

2

小明

9/30

200

軸承

s1

3

小華

10/1

-200

廠商退款

s2

4

小明

10/2

300

螺絲

s1

5

小明

10/4

200

螺絲

s1

 

 

 

所以例如,我現在取用Order id 為2的資料,所以我會取得

Table Order: id = 2
 

id

user

date

pay

item

shop_id

2

小明

9/30

200

軸承

s1


 

這時我就要用裡面的shop_id 值去 Table Shop查,所以我會查到

Table Shop: shop_id = s1
 

shop_id

shop

shop_address

s1

好記

台北市信義區信義路五段7號

 

 

最後再利用shop_id 當黏著劑組合起來,所以有點像連連看,用shop_id當連接點。
 

id

user

date

pay

item

shop_id

shop

shop_address

2

小明

9/30

200

軸承

s1

好記

台北市信義區信義路五段7號


就可以拿到完整的值了,只是當拿到完整的值的時候,shop_id「可能」就沒有用處,可以刪除。(為什麼這邊說可能,到文章後面再解釋。)。

 

 

所以可以想成第二正規化基本上就是在做拆分Table 表來減少冗余資料。
 


此外這邊可以先提到,通常做這樣的Table表連接查詢是需要SQL指令的,用的就是大名鼎鼎的Join 語法。Join語法與其使用需要注意的事項並不在本文範圍。但是要注意的是Join本身是額外程式動作,也就是他是會消耗效能的。所以第二正規劃切分Table表後確實有機會導致單一目的行為效率變差。但是多半在整體上來說是大幅提升的。
  
  
提及這個的意思是希望大家可以多用程式的邏輯去猜測後面的行為。這樣才不會落到只會公式般的運用這些技巧。

但是一如往常的,如果是新手的話,先實作做幾次踩雷後再開始探討會比較有實際一些。
 


 

另外item 與 user 其實也可以拆開來,變成User 與 Item的獨立表格會比較好。

 

Table: User

user_id

item

u1

小明

u2

小華


 

Table: Item 

item_id

user

i1

螺絲

i2

廠商退款

i3 

軸承

 

 

所以現在Order 表就變成下面的樣子
 

Table: Order
 

id

user_id

date

pay

item_id

shop_id

1

u1

9/30

100

i1

s1

2

u1

9/30

200

i2

s1

3

u2

10/1

-200

i3

s2

4

u1

10/2

300

i2

s1

5

u1

10/4

200

i2

s1

 

 

這樣用人眼看很不直覺,不過對電腦儲存來說是比較有效率的,除了各項id會重複外,其他的幾乎都不重複。

 

pay 與 date 也是有重複的地方,不過date一般不會只存到日期,會直接存datetime系列的資料格式,這種類型的格式最小單位為微秒(10的負6次方),如果是用電腦產生的時間戳記(timestamp)是不太容易重複的,另外一個表如果除了id外只有一個欄位不划算的可能性很高,尤其datetime現在常見是用 Int64 來記錄的,所以這樣做沒有減少空間使用,反而增加。


pay的話,其實也是一樣,基本上他就是個數字,所以多建一個表去紀錄他也只是浪費空間與運算資源而已。而且更重要的是每個付款的數字所代表的是紀錄當時「真實」的付款金額,有時間上的概念。所以不能發生改一個欄位,其他的資料也跟者改的情況。


假設我真的拉出一個table 叫做 pay,它的第一筆資料叫做200,那今天員工算錯,應該是300,他就必須要把200 改成 300 。但是這樣一定有問題,因為會動到其他的資料,其他資料也跟著一起改變了。或是新增一個pay 300,再把pay_id連過去,這又會發生浪費空間的問題。造成事倍工半的結果。
 

 

back-view-bald-blue-2244330.jpg

 

接下來,我們可以再調整一下欄位的名稱。所以最後會變成下面的樣子。


Table: Shop
 

id

shop

shop_address

1

好記

台北市信義區信義路五段7號

2

興記

臺北市中正區北平西路3號

 

Table: User
 

id

item

1

小明

2

小華

 

Table: Item 

id

user

1

螺絲

2

廠商退款

軸承

 

 

Table: Order
 

id

user_id

date

pay

item_id

shop_id

1

1

9/30

100

1

1

2

1

9/30

200

3

1

3

2

10/1

-200

2

2

4

1

10/2

300

1

1

5

1

10/4

200

1

1


 

這個表整理完以後,你會發現s1, i1, u1 等,都拿掉前綴的字串變成一般數字1 2 3。原因是因為如果有前綴是必須要用String 格式儲存,比較浪費空間,而且在程式比對上也會比較消耗資源,一般而言電腦對於處理數字的效率還是比較高一些。
  


另外把在Shop表等分切出來的表中的 xxx_id 改成 id,原因就如同上一篇說的,table 表中的key都是獨立的,shop 的 id 不會是 order 的 id。但是慣例上,如果要記錄在別人的表中,就要把前綴加回去,畢竟column不能用同樣的名稱。


所以shop 的 id 在 order裡面被記錄的話,會在order表中以shop_id命名,以此類推。
 

 

這時候我們就可以把shop_id 設成 foreign key 外(來)鍵,在Shop 表中的 id 是 Shop表中的primary key 主鍵(PK),但是對應過去的user_id 在 Order 表中則為foreign key 外鍵(FK)。一個Table表中只能有一個主鍵,但是可以有很多外鍵。主鍵與外鍵的配對就形成了Relational 關聯性 ,所以才稱作Relational database 關聯性資料庫。

 

 

這邊要特別注意的是,在部分的應用程式框架中的ORM系統裡面並沒有規定關聯性之間要明確設定 foreign key constraint (外鍵約束) 。foreign key在資料表建立的時候是要宣告的。它會加快搜尋的速度。但是會強制一定要先刪掉包含 foreign key 的資料才能刪包含primary key的資料。
 


以我們的範例來說,

如果要刪掉 Shop  id 2
 

id

shop

shop_address

2

興記

臺北市中正區北平西路3號

 

那必須先把 Order表中的 id 3 的shop_id 設成2 以外的值或空值,然後Shop表的id 2 才能刪掉。 不然資料庫會不讓你刪並且報錯。
 

id

user_id

date

pay

item_id

shop_id

3

2

10/1

-200

2

2 <= 這個會綁死

 

所以新手在使用ORM的時候,常常就會發生一下可以刪,一下又不能刪的神秘事件。這點尤其要注意。

 


當然,Foreign Key到底要不要設定還是看個人主觀認定,正確的答案通常是要。但是如果以應用程式為主體的概念去看,不要設定對應用程式來說可能比較有主導性。限制也可以都設定在ORM的Model層。在實務上小編也常做反第二正規化去避免Join Table太多層出現太過於複雜的情況。但專案的特性也是需要考量的地方所以這邊就先不多提以免誤導大家。
 

 

 

總之第二正規化的目的,就是釐清資料表裡面每一個資料欄位的關係,把不重要(部分相依)的資料另開表格作儲存,而這樣的做法,也正是關聯式資料庫的核心觀念,透過關聯的方式,來降低表內資料的重複性,並降低資料新增或刪除可能的風險,但光是這樣其實還不夠,只有做完第三正規化之後,才算是完成了真正的資料庫正規化作業!

 

 

 

最後回來上面所提到的shop_id 要不要留的問題
 

id

user

date

pay

item

shop_id

shop

shop_address

2

小明

9/30

200

軸承

1

好記

台北市信義區信義路五段7號

 

這其實關係著現在流行的 REST API架構

延伸閱讀:[不是工程師] 休息(REST)式架構? 寧靜式(RESTful)的Web API是現在的潮流?


在REST API架構下,如果我們要拿到上面的資料內容,我們應該是去跟【 https://[domain.com]/orders/2 】這個URL endpoint要。如果要到以後我們另外一個功能需要拿到關於這個shop的其他資訊,例如它旗下所有的 item 種類。我們勢必需要去跟URL【 https://[domain.com]/shops/:shop_id/items 】拿資料。這時候如果之前沒有把shop_id一起傳過來,我們就無法組出正確的URL 【 https://[domain.com]/shops/1/items 】。


這是比較應用面的考量,所以放在最後面供參考。
 

 

 

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


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


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

 

 

 

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


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

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

Small logo

進度條編輯群

進度條編輯團隊