個(gè)人中心
退出
關(guān)系數(shù)據(jù)庫(kù)設(shè)計(jì)：談?wù)勔?guī)范化技術(shù)
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
為什么需要規(guī)范化？
第一范式
第二范式
第三范式
反規(guī)范化
關(guān)于外鍵
總結(jié)
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。今天我們來(lái)聊聊關(guān)系數(shù)據(jù)庫(kù)的規(guī)范化設(shè)計(jì)問(wèn)題。本文不涉及數(shù)據(jù)庫(kù)教材上晦澀難懂的各種公式，而是從實(shí)際應(yīng)用出發(fā)，通過(guò)簡(jiǎn)單直白的方式介紹規(guī)范化的設(shè)計(jì)過(guò)程和常見(jiàn)范式。
為什么需要規(guī)范化？
很多教材和文章都是直接從第一范式開始介紹如何進(jìn)行數(shù)據(jù)庫(kù)設(shè)計(jì)，完全忽略了對(duì)事物前因后果的分析；從而導(dǎo)致我們看完之后，只知道要關(guān)系數(shù)據(jù)庫(kù)要進(jìn)行規(guī)范設(shè)計(jì)，但卻不知道為什么要這么做。因此，我們首先來(lái)給大家介紹一下規(guī)范化之前發(fā)生了什么。
假設(shè)我們需要為某公司設(shè)計(jì)一個(gè)數(shù)據(jù)庫(kù)，用于管理員工、部門、職位等相關(guān)的信息。首先從直觀上考慮，可以將員工信息、所在部門以及職位信息存儲(chǔ)到一個(gè)表中，如下圖所示：
每一行數(shù)據(jù)對(duì)應(yīng)一個(gè)員工的信息，包括他/她所在的部門、職位等。如果真的這么設(shè)計(jì)，我們?cè)趯?shí)際應(yīng)用中很快就會(huì)發(fā)現(xiàn)以下各種問(wèn)題：
數(shù)據(jù)冗余，同一個(gè)部門的信息存儲(chǔ)了多份，這就需要占用更多的磁盤空間。另外，數(shù)據(jù)冗余有時(shí)候也可能是指在不同的表中存儲(chǔ)了重復(fù)的信息；
插入異常，假如現(xiàn)在需要成立一個(gè)新的部門，由于還沒(méi)有增加新的員工，因此無(wú)法錄入這個(gè)部門的信息；
更新異常，如果需要修改某個(gè)部門信息，需要更新多行數(shù)據(jù)，效率低下；不小心忽略了某些記錄的話，還會(huì)會(huì)導(dǎo)致數(shù)據(jù)不一致，尤其是當(dāng)一個(gè)信息存儲(chǔ)到多個(gè)表中時(shí)更容易出現(xiàn)這種情況。
刪除異常，如果某個(gè)部門的所有員工都被刪除，將會(huì)導(dǎo)致這個(gè)部門的信息也將不復(fù)存在；
關(guān)系數(shù)據(jù)庫(kù)之父 Edgar F. Codd 顯然意識(shí)到了這些問(wèn)題，并且為此引入了規(guī)范化（Normalization）的設(shè)計(jì)過(guò)程。規(guī)范化使用范式（normal form）來(lái)定義和衡量，范式就是數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)遵循的一種標(biāo)準(zhǔn)級(jí)別。Codd 最早提出了第一范式（1NF）、第二范式（2NF）以及第三范式（3NF），每個(gè)范式都基于前面的范式定義，例如第二范式需要先滿足第一范式。
??更高級(jí)別的范式包括 BC 范式（BCNF）、第四范式（4NF）、基本元組范式（ETNF）、第五范式（5NF）、DK 范式（DKNF）以及第六范式（6NF）；一般來(lái)說(shuō)，滿足第三范式的數(shù)據(jù)庫(kù)就可以避免數(shù)據(jù)冗余和操作異常問(wèn)題。
通過(guò)以上介紹，我們知道了規(guī)范化是數(shù)據(jù)庫(kù)設(shè)計(jì)過(guò)程中的一系列原理和技術(shù)，使用范式來(lái)定義和衡量，主要用于減少表中數(shù)據(jù)的冗余，消除異常，提高數(shù)據(jù)完整性和一致性。
下面我們基上面的非規(guī)范化數(shù)據(jù)庫(kù)結(jié)構(gòu)，逐步介紹第一范式到第三范式的實(shí)現(xiàn)過(guò)程。
第一范式
第一范式（First Normal Form）要求滿足以下條件：
表中的字段都是不可再分的單一屬性;
表需要定義主鍵（PRIMARY KEY）。
簡(jiǎn)單來(lái)說(shuō)，首先就是每個(gè)屬性要有單獨(dú)的字段。在上面的不規(guī)范設(shè)計(jì)中，員工的個(gè)人電話和工作電話存儲(chǔ)在一個(gè)字段中，破壞了原子性。另外，還需要為表定義一個(gè)主鍵，用于唯一識(shí)別表中的每一行數(shù)據(jù)；假設(shè)每個(gè)部門中的員工不會(huì)同名，可以使用部門名稱加員工姓名作為主鍵。
將上面的示例修改成以下結(jié)構(gòu)就可以滿足第一范式：
第一范式要求表中的字段具有不可分割的原子特性；不過(guò)我們知道，原子是化學(xué)反應(yīng)不可再分的基本微粒，但在物理狀態(tài)中可以分割，它是由原子核和繞核運(yùn)動(dòng)的電子組成。因此，我們同樣需要考慮字段不可分割到底是針對(duì)什么而言。
例如，上面的“姓名”字段，實(shí)際上也可以拆分成兩個(gè)字段：姓氏和名字。那么到達(dá)要不要拆分呢？顯然這個(gè)取決于應(yīng)用程序如何使用這些信息，一般我們將姓名作為一個(gè)字段存儲(chǔ)；有些應(yīng)用可能需要拆分，這樣在給客戶發(fā)送消息時(shí)可以方便地顯示為“尊敬的劉先生/女生”。
另一個(gè)類似的情況是地址信息，例如“XX省XX市XX區(qū)XX小區(qū)”，存儲(chǔ)到一個(gè)字段還是拆分成多個(gè)字段？大部分情況下，應(yīng)用程序可能需要統(tǒng)計(jì)不同地區(qū)的用戶情況，拆分成多個(gè)字段便于分析。不過(guò)這時(shí)候需要注意的是如何確保數(shù)據(jù)的標(biāo)準(zhǔn)化，因?yàn)椴煌挠脩綦m然住在相同的小區(qū)，但會(huì)輸入不一致的數(shù)據(jù)；所以最好提供一組標(biāo)準(zhǔn)的數(shù)據(jù)，提供下拉列表給用于進(jìn)行選擇。
除了基本的數(shù)字、字符、日期等數(shù)據(jù)類型之外，SQL 還提供了一些復(fù)雜的類型，例如數(shù)組、XML、JSON 以及自定義類型等。假如我們使用一個(gè) JSON 字段存儲(chǔ)電話號(hào)碼，數(shù)據(jù)如下所示：
{
“phoneNumbers”: [
{
“type”: “office”,
“number”: “61238888”
},
{
“type”: “mobile”,
“number”: “13612345678”
}
]
}
那么這種設(shè)計(jì)算不算違反第一范式？從定義來(lái)說(shuō)這顯然不屬于第一范式，因?yàn)檫@個(gè)字段中包含了多個(gè)可以分割的屬性。
但是，從 SQL 標(biāo)準(zhǔn)來(lái)說(shuō)這些類型都屬于原生類型，而且提供了對(duì)這種數(shù)據(jù)進(jìn)行處理和查詢的內(nèi)置函數(shù)和方法；如果從應(yīng)用程序的角度來(lái)看，例如電商平臺(tái)中的產(chǎn)品信息、博客文章中的評(píng)論信息，可以將它們看作一個(gè)原子數(shù)據(jù)存儲(chǔ)在 XML 或者 JSON 字段中，因?yàn)闆](méi)有進(jìn)行分割處理的需求。
??SQL 是關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言，但 SQL 遠(yuǎn)遠(yuǎn)不只能夠存儲(chǔ)和處理關(guān)系模型，XML 或者 JSON 文檔、多維數(shù)組、圖形存儲(chǔ)以及流數(shù)據(jù)處理已經(jīng)成為了 SQL 標(biāo)準(zhǔn)中的一部分，具體可以參考這篇文章。
以上表結(jié)構(gòu)滿足第一范式，但仍然存在數(shù)據(jù)冗余（例如部門信息），可能導(dǎo)致插入異常、刪除異常、修改異常等問(wèn)題；所以我們還需要進(jìn)一步規(guī)范化。
第二范式
第二范式（Second Normal Form）要求滿足以下條件：
滿足第一范式；
非主鍵字段必須完全依賴于主鍵或者候選鍵，不能只依賴于主鍵或者候選鍵的一部分。
上面表結(jié)構(gòu)中的“部門地址”取決于“部門名稱”，也就是主鍵的一部分；這種依賴關(guān)系稱為部分函數(shù)依賴（partial functional dependency）。顯然，此時(shí)表中的部門信息存在冗余，可能導(dǎo)致各種操作異常。
為此我們可以將部門信息單獨(dú)存儲(chǔ)到一張部門表中，并且在部門表和員工表之間維護(hù)一個(gè)一對(duì)多的關(guān)系。我們繼續(xù)將表的結(jié)構(gòu)修改如下：
我們將員工表拆成了 3 個(gè)表，員工表中的部門編號(hào)和職位編號(hào)是外鍵，分別引用了部門表的主鍵和職位表的主鍵。另外，我們?yōu)槊總€(gè)表增加了一個(gè) id 主鍵字段（工號(hào)、部門編號(hào)、職位編號(hào)）。因?yàn)椴块T名稱、職位名稱等信息并不適合作為主鍵；如果使用部門名稱作為主鍵，當(dāng)需要修改某個(gè)部門的名稱，員工表中可能需要相應(yīng)修改多條記錄。
如果考慮到同一個(gè)部門中可能存在同名的員工，直接在員工表中增加一個(gè) id 主鍵字段也可以滿足第二范式的要求。
以上表結(jié)構(gòu)滿足第二范式，但仍然存在數(shù)據(jù)冗余（例如部門信息），可能導(dǎo)致插入異常、刪除異常、修改異常等問(wèn)題；所以我們還需要進(jìn)一步規(guī)范化。
第三范式
第三范式要求滿足以下條件：
滿足第二范式；
屬性不依賴于其它的非主屬性，也就是非關(guān)鍵字段不依賴于其他非關(guān)鍵字段。
當(dāng)主鍵決定字段 A，字段 A 又決定字段 B 時(shí)，稱為傳遞函數(shù)依賴（transitive functional dependency）。例如員工編號(hào)決定了部門編號(hào)，部門編號(hào)決定了部門名稱；如果將部門信息和員工信息放在一張表中，就存在這種依賴。顯然，在上一節(jié)中將員工表拆分成三個(gè)表之后就不存在這種問(wèn)題，因此滿足第三范式。
最終，我們?cè)O(shè)計(jì)的公司數(shù)據(jù)庫(kù)結(jié)構(gòu)（ER 圖）如下：
其中，部門和員工的關(guān)系是一對(duì)多的關(guān)系；職位和員工的關(guān)系也是一對(duì)多的關(guān)系。
現(xiàn)在我們來(lái)回顧一下非規(guī)范化設(shè)計(jì)時(shí)的幾個(gè)問(wèn)題：
部門、員工以及職位信息分別存儲(chǔ)一份，通過(guò)外鍵保持它們之間的聯(lián)系。因此，不存在數(shù)據(jù)冗余的問(wèn)題；
如果想要成立一個(gè)新的部門，直接錄入部門信息即可，解決了插入異常的問(wèn)題；
如果某個(gè)部門的所有員工都被刪除，該部門的信息不會(huì)受到影響，不存在刪除異常；
如果需要修改部門信息，直接更新部門表即可，不會(huì)導(dǎo)致數(shù)據(jù)不一致。
對(duì)于前三個(gè)范式而言，只需要將不同的實(shí)體/對(duì)象單獨(dú)存儲(chǔ)到一張表中，并且通過(guò)外鍵建立它們之間的聯(lián)系即可滿足。這也是大多數(shù)在線交易系統(tǒng)數(shù)據(jù)庫(kù)理想的設(shè)計(jì)方法。
反規(guī)范化
簡(jiǎn)單來(lái)說(shuō)，規(guī)范化就是將大表拆分成多個(gè)小表，并且通過(guò)外鍵建立它們之間的聯(lián)系。但是，規(guī)范化可能導(dǎo)致連接查詢（JOIN）過(guò)多。例如，為了查看員工所在的部門名稱和職位名稱，我們需要關(guān)聯(lián)查詢 3 個(gè)表：
SELECT e.emp_name, e.hire_date, d.dept_name, j.job_title
FROM employee e
JOIN department d ON (d.dept_id = e.dept_id)
JOIN job j ON (j.job_id = e.job_id)
WHERE e.emp_name = ‘孫尚香’;
| emp_name | hire_date | dept_name | job_title |
|---|---|---|---|
| 孫尚香 | 2002-08-08 | 財(cái)務(wù)部 | 財(cái)務(wù)經(jīng)理 |
如果表中的數(shù)據(jù)量很大，過(guò)多的表連接查詢會(huì)增加數(shù)據(jù)庫(kù)的 IO 操作，從而降低數(shù)據(jù)庫(kù)的性能。因此，有時(shí)候?yàn)榱颂岣吣承┎樵兓蛘邞?yīng)用的性能而故意降低規(guī)范反的程度，也就是反規(guī)范化（denormalization）。一般來(lái)說(shuō)，數(shù)據(jù)倉(cāng)庫(kù)（Data Warehouse）和在線分析系統(tǒng)（OLAP）會(huì)使用到反規(guī)范化的技術(shù)，因?yàn)樗鼈円詮?fù)雜查詢和報(bào)表分析為主。
常用的反規(guī)范化方法包括增加冗余字段、增加計(jì)算列、將小表合成大表等。例如想要知道每個(gè)部門的員工數(shù)量的話，需要同時(shí)連接部門表和員工表；可以在部門表中增加一個(gè)字段（emp_numbers），查詢時(shí)就不需要再連接員工表，但是每次增加或者刪除員工時(shí)需要更新該字段。
需要注意的是，反規(guī)范化會(huì)增加更新和修改數(shù)據(jù)的開銷，導(dǎo)致數(shù)據(jù)存在冗余，可能帶來(lái)數(shù)據(jù)完整性和一致性的問(wèn)題；因此，通常我們應(yīng)該先進(jìn)行規(guī)范化設(shè)計(jì)，再根據(jù)實(shí)際情況考慮是否需要反規(guī)范化。
關(guān)于外鍵
在數(shù)據(jù)庫(kù)結(jié)構(gòu)設(shè)計(jì)時(shí)，還有一個(gè)經(jīng)常爭(zhēng)論的問(wèn)題就是需不需要使用外鍵（FOREIGN KEY）。外鍵是數(shù)據(jù)庫(kù)用于實(shí)現(xiàn)參照完整型的約束，利用數(shù)據(jù)庫(kù)的外鍵可以保證數(shù)據(jù)的完整性和一致性；外鍵的級(jí)聯(lián)操作可以方便數(shù)據(jù)的自動(dòng)處理，減少了程序出錯(cuò)的可能性。
例如，員工屬于部門，員工的部門字段上可以創(chuàng)建一個(gè)外鍵引用部門表的主鍵。此時(shí)，我們必須先創(chuàng)建部門，然后才能為該部門創(chuàng)建員工；不會(huì)出現(xiàn)員工屬于一個(gè)不存在的部門的情況，保證了數(shù)據(jù)的完整性。同時(shí)，如果要?jiǎng)h除一個(gè)部門的話，必須同時(shí)處理該部門下的員工；可以選擇級(jí)聯(lián)刪除員工或者將員工的部門修改為其他部門等操作。
既然外鍵擁有這么多好處，為什么我們還要討論是否需要使用外鍵呢？主要是性能問(wèn)題。因?yàn)槿魏问虑槎际怯写鷥r(jià)的，數(shù)據(jù)庫(kù)為了維護(hù)外鍵需要犧牲一定的性能，尤其是在大數(shù)據(jù)量高并發(fā)的情況下。因此出現(xiàn)了另一種解決方案，就是將完整性檢查放到應(yīng)用層去實(shí)現(xiàn)，而應(yīng)用程序相對(duì)比較容易擴(kuò)展。
不過(guò)，在應(yīng)用端實(shí)現(xiàn)約束也可能導(dǎo)致一些問(wèn)題。首先，無(wú)法百分之百保證不會(huì)出現(xiàn)問(wèn)題，尤其是多個(gè)應(yīng)用同時(shí)共享一個(gè)數(shù)據(jù)庫(kù)時(shí)。缺失外鍵可能導(dǎo)致數(shù)據(jù)庫(kù)的結(jié)構(gòu)不明確，需要依賴相應(yīng)的文檔進(jìn)行說(shuō)明。
總之，在系統(tǒng)的設(shè)計(jì)之初應(yīng)該盡量使用外鍵確保完整性。如果隨著業(yè)務(wù)增長(zhǎng)出現(xiàn)性能問(wèn)題，可以考慮在應(yīng)用中實(shí)現(xiàn)約束。
總結(jié)
本文從非規(guī)范化數(shù)據(jù)庫(kù)結(jié)構(gòu)可能導(dǎo)致的問(wèn)題出發(fā)，介紹了關(guān)系數(shù)據(jù)庫(kù)為什么應(yīng)該進(jìn)行規(guī)范化設(shè)計(jì)以及常用的各種范式。同時(shí)，我們還討論了特殊應(yīng)用場(chǎng)景下的反規(guī)范化問(wèn)題和外鍵的取舍。
分類導(dǎo)航