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