淺談資料庫正規化

把前段時間沒搞懂的第二正規化跟,第三正規化搞懂了，順便筆記一下

 

資料庫的發明，是為了快速讀取或寫入當下所需的資料

像是進入部落格的首頁，伺服器端就會向資料庫query出每篇文章的摘要，再送回用戶的瀏覽器

想像如果等10秒才載入完畢，那我的部落格豈還有人會等著載入完看嗎

因此，將所有資料塞在同個table顯然不一定是好的選擇

而將資料分門別類獨立出不同table儲存的動作即稱為資料庫正規化

適當的資料庫正規化有助於提升query效能
但是當過度得正規化則會讓邊際效益呈負的

 

正規化種類

上面已經提到，將資料分類歸到不同table的動作叫資料庫正規化

而這個資料庫正規化，也是有分程度的：

• 第一正規化
• 第二正規化
• 第三正規化

這是日常普遍資料庫常見的三個正規化，再下去就有點像為了正規化而正規化，效能不增反減

當然，正規化只是種強迫症分類概念

我也有接觸過好幾個快30萬條row的table刻意只做第一正規化的

現在就來講講怎麼做第一二三正規化

 

第一正規化

原則

• 資料表要有主鍵
• 每個欄位只存放一個值
• 沒有多個代表相同意義的欄位

說明

拿最近學校叫我們填的教師期中意見評量當靈感

假設有意見評量回覆的metadata，直接原封不動塞進table內

課碼	課堂名稱	教師代碼	教師名稱	學生學號	學生名稱	滿意度	意見回饋
0001	微積分	T1003	周週粥	105210420 104209232	好勃村 安東尼	4 5	教得好 句句重點
0002	資料庫	T2210	陳誠呈	103116198 104130224	王小明 金小美	4 4	還不錯 生動活潑
0003	服務學習	T1165	木林森	104209232 104209005	安東尼 湯馬士	1 1	垃圾課欸 幹，拔草?!

每一個row就是一堂課所有的feedback匯集

表面上看起來沒什麼問題，但如果今天想要SELECT某學生(e.g. 安東尼)對所有老師的回饋呢

由於一堂課只有一個row，最多只能過濾到安東尼修過哪些課，沒辦法直接SELECT他單獨的意見回饋

現在，我們將table進行第一正規化

把每個欄位多餘的值新增成不同的row，然後決定table的主鍵

在這邊補充一下

主鍵不一定要是唯一的索引
當整個table只有一個主鍵時，它是唯一的索引沒錯；
如果整個table含有兩個以上的主鍵時，「它們」的聯集才是唯一的索引。
「它們」也就是所謂的複合主鍵。

這邊我們用課碼跟學生學號來當作複合主鍵

就不用再新增一個無用的column加上流水號來做以往認知上的單一主鍵

*課碼	課堂名稱	教師代碼	教師名稱	*學生學號	學生名稱	滿意度	意見回饋
0001	微積分	T1003	周週粥	105210420	好勃村	4	教得好
0001	微積分	T1003	周週粥	104209232	安東尼	5	句句重點
0002	資料庫	T2210	陳誠呈	103116198	王小明	4	還不錯
0002	資料庫	T2210	陳誠呈	104130224	金小美	4	生動活潑
0003	服務學習	T1165	木林森	104209232	安東尼	1	垃圾課欸
0003	服務學習	T1165	木林森	104209005	湯馬士	1	幹，拔草?!

這樣就完成第一正規化了，是不是如果要SELECT安東尼修過哪些課的意見回饋就容易得多了呢

 

第二正規化

原則

• 符合第一正規化
• 消除任何欄位與主鍵部分相依

說明

第二正規化要做的其實就是去除重複的資料

我們可以看到，剛剛的table在做完第一正規化後出現了許多重複的值

*課碼	課堂名稱	教師代碼	教師名稱	*學生學號	學生名稱	滿意度	意見回饋
0001	微積分	T1003	周週粥	105210420	好勃村	4	教得好
0001	微積分	T1003	周週粥	104209232	安東尼	5	句句重點
0002	資料庫	T2210	陳誠呈	103116198	王小明	4	還不錯
0002	資料庫	T2210	陳誠呈	104130224	金小美	4	生動活潑
0003	服務學習	T1165	木林森	104209232	安東尼	1	垃圾課欸
0003	服務學習	T1165	木林森	104209005	湯馬士	1	幹，拔草?!

而且還出現了「部分相依」

部分相依
某些欄位「只」與複合主鍵中的「部份」欄位相依，
「沒有」與複合主鍵中的「全部」欄位相依。

我們把欄位與主鍵的相依關係做成表格，以方便判別

	與課碼相依	與學生學號相依
*課堂名稱	O	X
*教師代碼	O	X
*教師名稱	O	X
*學生名稱	X	O
滿意度	O	O
意見回饋	O	O

並且在部分相依的欄位打星號

為了達成第二正規化，我們先將「部分相依」的欄位獨立成為新的table

courses:

*課碼	課堂名稱	教師代碼	教師名稱
0001	微積分	T1003	周週粥
0002	資料庫	T2210	陳誠呈
0003	服務學習	T1165	木林森

相依於課碼的歸一個table

students:

*學生學號	學生名稱
105210420	好勃村
104209232	安東尼
103116198	王小明
104130224	金小美
104209005	湯馬士

相依於學生學號的歸一個table

頓時，我們原本的table就瞬間乾淨了許多

feedbacks:

*課碼	*學生學號	滿意度	意見回饋
0001	105210420	4	教得好
0001	104209232	5	句句重點
0002	103116198	4	還不錯
0002	104130224	4	生動活潑
0003	104209232	1	垃圾課欸
0003	104209005	1	幹，拔草?!

到此，這就是所謂的第二正規化了

 

第三正規化

原則

• 符合第二正規化
• 消除主鍵以外的欄位與主鍵間接相依

說明

經過上面第二正規化後，students跟feedbacks的table其實也已經符合第三正規化了

唯一剩下courses這個table，它不符合第三正規化的原因在它存在主鍵以外的欄位與主鍵間接相依

間接相依
兩個欄位並不是直接相依的關係，而是有另一個欄位夾在中間。
譬如說A相依於B，B又相依於C，則A就間接相依於C。

course:

*課碼	課堂名稱	教師代碼	教師名稱
0001	微積分	T1003	周週粥
0002	資料庫	T2210	陳誠呈
0003	服務學習	T1165	木林森

我們從左往右掃一遍，可以看到

• 課堂名稱相依於課碼
• 教師代碼相依於課碼
• 教師名稱相依於教師代碼

換言之，教師名稱「間接相依」於課碼

為了達成第三正規化，我們要將教師名稱與課碼的間接相依關係消除

來吧，大膽將間接相依的欄位分割出去

course:

*課碼	課堂名稱	教師代碼
0001	微積分	T1003
0002	資料庫	T2210
0003	服務學習	T1165

teachers:

*教師代碼	教師名稱
T1003	周週粥
T2210	陳誠呈
T1165	木林森

這樣最終就完成了第三正規化

 

現在，我們來回顧做完第三正規化的四個table

feedbacks:

*課碼	*學生學號	滿意度	意見回饋
0001	105210420	4	教得好
0001	104209232	5	句句重點
0002	103116198	4	還不錯
0002	104130224	4	生動活潑
0003	104209232	1	垃圾課欸
0003	104209005	1	幹，拔草?!

course:

*課碼	課堂名稱	教師代碼
0001	微積分	T1003
0002	資料庫	T2210
0003	服務學習	T1165

teachers:

*教師代碼	教師名稱
T1003	周週粥
T2210	陳誠呈
T1165	木林森

students:

*學生學號	學生名稱
105210420	好勃村
104209232	安東尼
103116198	王小明
104130224	金小美
104209005	湯馬士

是不是更 難閱讀 整齊乾淨了呢^^

 

額外補充

 

判斷相依性

有些人可能會有疑問說剛剛這張表滿意度及意見回饋與課碼跟學生學號的相依性怎麼判斷

	與課碼相依	與學生學號相依
*課堂名稱	O	X
*教師代碼	O	X
*教師名稱	O	X
*學生名稱	X	O
滿意度	O	O
意見回饋	O	O

這邊引用一個簡單的判斷方法

假設在關聯表 R(X,Y,Z)中，包含一組功能相依(X,Y)->Z

如果我們從關聯表 R中移除任一屬性 X 或 Y 時，
使得這個功能相依(X,Y)->Z 不存在，
此時我們稱 Z 為「完全功能相依」於(X,Y)。

反之，若(X,Y)->Z 存在，我們稱 Z 為「部份功能相依」於(X,Y) 。

套用上面的公式(課碼,學生學號)->滿意度

如果拿掉課碼或學生學號任意一個，滿意度就沒有任何意義了，因此，滿意度為完全相依

依樣畫葫蘆

(課碼,學生學號)->意見回饋

拿掉課碼或學生學號任意一個，意見回饋也沒有任何意義

所以大可斷定意見回饋也是完全相依

 

正規化快速判別技巧整理

如果對於上面敘述太長沒有看完，或不是很懂上面的敘述，這邊再提供另一種簡單的方式

第一正規化

• 找出或規劃欄位為主鍵
• 解決一個欄位有多個值的問題
• 解決多個欄位代表相同意義的問題

第二正規化

• 將一直重複的欄位獨立出來

第三正規化

• 將與主鍵無關的欄位獨立出來

 

有關淺談資料庫的正規化就到這邊(打這麼多好像也不淺了…)，謝謝大家這麼有耐心地看完。