寫給開發者看的關係型資料庫設計

　　資料庫設計，一個軟體專案成功的基石。很多從業人員都認為，資料庫設計其實不那麼重要。現實中的情景也相當雷同，開發人員的數量是資料庫設計人員的數倍。多數人使用資料庫中的一部分，所以也會把資料庫設計想的如此簡單。其實不然，資料庫設計也是門學問。

　　從筆者的經歷看來，筆者更贊成在專案早期由開發者進行資料庫設計（後期調優需要DBA）。根據筆者的專案經驗，一個精通OOP和ORM的開發者，設計的資料庫往往更為合理，更能適應需求的變化，如果追其原因，筆者個人猜測是因為資料庫的規範化，與OO的部分思想雷同（如內聚）。而DBA，設計的資料庫的優勢是能將DBMS的能力發揮到極致，能夠使用SQL和DBMS實現很多程式實現的邏輯，與開發者相比，DBA優化過的資料庫更為高效和穩定。如標題所示，本文旨在分享一名開發者的資料庫設計經驗，並不涉及複雜的SQL語句或DBMS使用，因此也不會侷限到某種DBMS產品上。真切地希望這篇文章對開發者能有所幫助，也希望讀者能幫助筆者查漏補缺。

　　一 Codd的RDBMS12法則——RDBMS的起源

　　Edgar Frank Codd（埃德加·弗蘭克·科德）被譽為“關聯式資料庫之父”，並因為在資料庫管理系統的理論和實踐方面的傑出貢獻於1981年獲圖靈獎。在1985年，Codd 博士釋出了12條規則，這些規則簡明的定義出一個關係型資料庫的理念，它們被作為所有關聯式資料庫系統的設計指導性方針。

1. 資訊法則 關聯式資料庫中的所有資訊都用唯一的一種方式表示——表中的值。
2. 保證訪問法則 依靠表名、主鍵值和列名的組合，保證能訪問每個資料項。
3. 空值的系統化處理 支援空值（NULL），以系統化的方式處理空值，空值不依賴於資料型別。
4. 基於關係模型的動態聯機目錄 資料庫的描述應該是自描述的，在邏輯級別上和普通資料採用同樣的表示方式，即資料庫必須含有描述該資料庫結構的系統表或者資料庫描述資訊應該包含在使用者可以訪問的表中。
5. 統一的資料子語言法則 一個關聯式資料庫系統可以支援幾種語言和多種終端使用方式，但必須至少有一種語言，它的語句能夠一某種定義良好的語法表示為字串，並能全面地支援以下所有規則：資料定義、檢視定義、資料操作、約束、授權以及事務。（這種語言就是SQL)
6. 檢視更新法則 所有理論上可以更新的檢視也可以由系統更新。
7. 高階的插入、更新和刪除操作 把一個基礎關係或派生關係作為單個操作物件處理的能力不僅適應於資料的檢索，還適用於資料的插入、修改個刪除，即在插入、修改和刪除操作中資料行被視作集合。
8. 資料的物理獨立性 不管資料庫的資料在儲存表示或訪問方式上怎麼變化，應用程式和終端活動都保持著邏輯上的不變性。
9. 資料的邏輯獨立性 當對錶做了理論上不會損害資訊的改變時，應用程式和終端活動都會保持邏輯上的不變性。
10. 資料完整性的獨立性 專用於某個關係型資料庫的完整性約束必須可以用關聯式資料庫子語言定義，而且可以儲存在資料目錄中，而非程式中。
11. 分佈獨立性 不管資料在物理是否分散式儲存，或者任何時候改變分佈策略，RDBMS的資料操縱子語言必須能使應用程式和終端活動保持邏輯上的不變性。
12. 非破壞性法則 如果一個關聯式資料庫系統支援某種低階（一次處理單個記錄）語言，那麼這個低階語言不能違反或繞過更高階語言（一次處理多個記錄）規定的完整性法則或約束，即使用者不能以任何方式違反資料庫的約束。

　　二 關係型資料庫設計階段

　　（一）規劃階段

　　規劃階段的主要工作是對資料庫的必要性和可行性進行分析。確定是否需要使用資料庫，使用哪種型別的資料庫，使用哪個資料庫產品。

　　（二）概念階段

　　概念階段的主要工作是收集並分析需求。識別需求，主要是識別資料實體和業務規則。對於一個系統來說，資料庫的主要包括業務資料和非業務資料，而業務資料的定義，則依賴於在此階段對使用者需求的分析。需要儘量識別業務實體和業務規則，對系統的整體有初步的認識，並理解資料的流動過程。理論上，該階段將參考或產出多種文件，比如“用例圖”，“資料流圖”以及其他一些專案文件。如果能夠在該階段產出這些成果，無疑將會對後期進行莫大的幫助。當然，很多文件已超出資料庫設計者的考慮範圍。而且，如果你並不精通該領域以及使用者的業務，那麼請放棄自己獨立完成使用者需求分析的想法。使用者並不是技術專家，而當你自身不能扮演“業務顧問”的角色時，請你選擇與專案組的相關人員合作，或者將其視為風險呈報給PM。再次強調，大多數情況，使用者只是行業從業者，而非職業技術人員，我們僅僅從使用者那裡收集需求，而非依賴於使用者的知識。

　　記錄使用者需求時，可以使用一些技巧，當然這部分內容有些可能會超出資料庫設計人員的職責：

• 努力維護一系列包含了系統設計和規格說明資訊的文件，如會議記錄、訪談記錄、關鍵使用者期望、功能規格、技術規格、測試規格等。
• 頻繁與干係人溝通並收集反饋。
• 標記出你自己新增的，不屬於客戶要求的，未決內容。
• 與所有關鍵干係人儘快確認專案範圍，併力求凍結需求。

　　此外，必須嚴謹處理業務規則，並詳細記錄。在之後的階段，將會根據這些業務規則進行設計。

　　當該階段結束時，你應該能夠回答以下問題：

• 需要哪些資料？
• 資料該被怎樣使用？
• 哪些規則控制著資料的使用？
• 誰會使用何種資料？
• 客戶想在核心功能介面或者報表上看到哪些內容？
• 資料現在在哪裡？
• 資料是否與其他系統有互動、整合或同步？
• 主題資料有哪些？
• 核心資料價值幾何，對可靠性的要求程度？

　　並且得到如下資訊：

• 實體和關係
• 屬性和域
• 可以在資料庫中強制執行的業務規則
• 需要使用資料庫的業務過程

　　（三）邏輯階段

　　邏輯階段的主要工作是繪製E-R圖，或者說是建模。建模工具很多，有不同的圖形表示方法和軟體。這些工具和軟體的使用並非關鍵，筆者也不建議讀者花大量時間在建模方法的選擇上。對於大多數應用來說，E-R圖足以描述實體間的關係。建模關鍵是思想而不是工具，軟體只是起到輔助作用，識別實體關係才是本階段的重點。

　　除了實體關係，我們還應該考慮屬性的域（值型別、範圍、約束）

　　（四）實現階段

　　實現階段主要針對選擇的RDBMS定義E-R圖對應的表，考慮屬性型別和範圍以及約束。

　　（五）物理階段

　　物理階段是一個驗證並調優的階段，是在實際物理裝置上部署資料庫，並進行測試和調優。

　　三 設計原則

　　（一）降低對資料庫功能的依賴

　　功能應該由程式實現，而非DB實現。原因在於，如果功能由DB實現時，一旦更換的DBMS不如之前的系統強大，不能實現某些功能，這時我們將不得不去修改程式碼。所以，為了杜絕此類情況的發生，功能應該有程式實現，資料庫僅僅負責資料的儲存，以達到最低的耦合。

　　（二）定義實體關係的原則

　　當定義一個實體與其他實體之間的關係時，需要考量如下：

• 牽涉到的實體 識別出關系所涉及的所有實體。
• 所有權 考慮一個實體“擁有”另一個實體的情況。
• 基數 考量一個實體的例項和另一個實體例項關聯的數量。

　　關係與表數量

• 描述1:1關係最少需要1張表。
• 描述1:n關係最少需要2張表。
• 描述n:n關係最少需要3張表。

　　（三）列意味著唯一的值

　　如果表示座標（0,0），應該使用兩列表示，而不是將“0,0”放在1個列中。

　　（四）列的順序

　　列的順序對於表來說無關緊要，但是從習慣上來說，採用“主鍵+外來鍵+實體資料+非實體資料”這樣的順序對列進行排序顯然能得到比較好的可讀性。

　　（五）定義主鍵和外來鍵

　　資料表必須定義主鍵和外來鍵（如果有外來鍵）。定義主鍵和外來鍵不僅是RDBMS的要求，同時也是開發的要求。幾乎所有的程式碼生成器都需要這些資訊來生成常用方法的程式碼（包括SQL文和引用），所以，定義主鍵和外來鍵在開發階段是必須的。之所以說在開發階段是必須的是因為，有不少團隊出於效能考慮會在進行大量測試後，在保證參照完整性不會出現大的缺陷後，會刪除掉DB的所有外來鍵，以達到最優效能。筆者認為，在效能沒有出現問題時應該保留外來鍵，而即便效能真的出現問題，也應該對SQL文進行優化，而非放棄外來鍵約束。

　　（六）選擇鍵

　　1 人工鍵與自然鍵

　　人工健——實體的非自然屬性，根據需要由人強加的，如GUID，其對實體毫無意義；自然健——實體的自然屬性，如身份證編號。

　　人工鍵的好處：

• 鍵值永遠不變
• 永遠是單列儲存

　　人工鍵的缺點：

• 因為人工鍵是沒有實際意義的唯一值，所以不能通過人工鍵來避免重複行。

　　筆者建議全部使用人工鍵。原因如下：

• 在設計階段我們無法預測到程式碼真正需要的值，所以乾脆放棄猜測鍵，而使用人工鍵。
• 人工鍵複雜處理實體關係，而不負責任何屬性描述，這樣的設計使得實體關係與實體內容得到高度解耦，這樣做的設計思路更加清晰。

　　筆者的另一個建議是——每張表都需要有一個對使用者而言有意義的自然鍵，在特殊情況下也許找不到這樣一個項，此時可以使用複合鍵。這個鍵我在程式中並不會使用其作為唯一標識，但是卻可以在對資料庫直接進行查詢時使用。

　　使用人工鍵的另一根弊端，主要源自對查詢效能的考量，因此選擇人工鍵的形式（列的型別）很重要：

• 自增值型別 由於型別輕巧查詢效率更好，但取值有限。
• GUID 查詢效率不如值型別，但是取值無限，且對開發人員更加親切。

　　2 智慧健與非智慧鍵

　　智慧鍵——鍵值包含額外資訊，其根據某種約定好的編碼規範進行編碼，從鍵值本身可以獲取某些資訊；非智慧鍵，單純的無意義鍵值，如自增的數字或GUID。

　　智慧鍵是一把雙刃劍，開發人員偏愛這種包含資訊的鍵值，程式盼望著其中潛在的資料；資料庫管理員或者設計者則討厭這種智慧鍵，原因也是很顯然的，智慧鍵對資料庫是潛在的風險。前面提到，資料庫設計的原則之一是不要把具有獨立意義的值的組合實現到一個單一的列中，應該使用多個獨立的列。資料庫設計者，更希望開發人員通過拼接多個列來得到智慧鍵，即以複合主鍵的形式給開發人員使用，而不是將一個列的值分解後使用。開發人員應該接受這種資料庫設計，但是很多開發者卻想不明白兩者的優略。筆者認為，使用單一列實現智慧鍵存在這樣一個風險，就是我們可能在設計階段無法預期到編碼規則可能會在後期發生變化。比如，構成智慧鍵的區域性鍵的值用完而引起規則變化或者長度變化，這種編碼規則的變化對於程式的有效性驗證與智慧鍵解析是破壞性的，這是系統運維人員最不希望看到的。所以筆者建議如果需要智慧鍵，請在業務邏輯層封裝（使用只讀屬性），不要再持久化層實現，以避免上述問題。

　　（七）是否允許NULL

　　關於NULL我們需要了解它的幾個特性：

• 任何值和NULL拼接後都為NULL。
• 所有與NULL進行的數學操作都返回NULL。
• 引入NULL後，邏輯不易處理。

　　那麼我們是否應該允許列為空呢？筆者認為這個問題的答案受到我們的開發語言的影響。以C#為例，因為引入了可空型別來處理資料庫值型別為NULL的情形，所以是否允許為空對開發者來說意義並不大。但有一點必須注意，就是驗證非空必須要在程式集進行處理，而不該依賴於DBMS的非空約束，必須確保完整資料（所有必須的屬性均被賦值）到達DB（所謂的“安全區”，我們必須定義在多層系統中那些區域得到的資料是安全而純淨的）。

　　（八）屬性切割

　　一種錯誤想法是，屬性與列是1：1的關係。對於開發者，我們公開屬性而非欄位。舉個例子來說，對於實體“員工”有“名字”這一屬性，“名字”可以再被分解為“姓”和“名”，對於開發人員來說，顯然第二種資料結構更受青睞（“姓”和“名”作為兩個欄位）。所以，在設計時我們也應該根據需要考慮是否切割屬性。

　　（九）規範化——正規化

　　當筆者還在大學時，正規化是學習關係型資料庫時最頭疼的問題。我想也許會有讀者仍然不理解正規化的價值，簡單來說——正規化將幫助我們來保證資料的有效性和完整性。規範化的目的如下：

• 消滅重複資料。
• 避免編寫不必要的，用來使重複資料同步的程式碼。
• 保持表的瘦身，以及減從一張表中讀取資料時需要進行的讀運算元量。
• 最大化聚集索引的使用，從而可以進行更優化的資料訪問和聯結。
• 減少每張表使用的索引數量，因為維護索引的成本很高。

　　規範化旨在——挑出複雜的實體，從中抽取出簡單的實體。這個過程一直持續下去，直到資料庫中每個表都只代表一件事物，並且表中每個描述的都是這件事物為止。

　　1 規範化實體和屬性（去除冗餘）

　　1NF：每個屬性都只應表示一個單一的值，而非多個值。

　　需要考慮幾點：

• 屬性是原子性的 需要考慮熟悉是否分解的足夠徹底，使得每個屬性都表示一個單一的值。（和“（三）列意味著唯一的值”描述的原則相同。）分解原則為——當你需要分開處理每個部分時才分解值，並且分解到足夠用就行。（即使當前不需要徹底分解屬性，也應該考慮未來可能的需求變更。）
• 屬性的所有例項必須包含相同數量的值 實體有固定數量的屬性（表有固定數量的列）。設計實體時，要讓每個屬性只有固定數量的值與其相關聯。
• 實體中出現的所有實體型別都必須不同

　　當前設計不符合1NF的“臭味”：

• 包含分隔符類字元的字串資料。
• 名字尾端有數字的屬性。
• 沒有定義鍵或鍵定義不好的表。

　　2 屬性間的關係（去除冗餘）

　　2NF-實體必須符合1NF，每個屬性描述的東西都必須針對整個鍵（可以理解為oop中型別屬性的內聚性）。

　　當前設計不符合2NF的“臭味”：

• 重複的鍵屬性名字字首（設計之外的資料冗餘） 表明這些值可能描述了某些額外的實體。
• 有重複的資料組（設計之外的資料冗餘） 這標誌著屬性間有函式依賴型。
• 沒有外來鍵的複合主鍵 這標誌著鍵中的鍵值可能標識了多種事物，而不是一種事物。

　　3NF-實體必須符合2NF，非鍵屬性不能描述其他非鍵屬性。（與2NF不同，3NF處理的是非鍵屬性和非鍵屬性之間的關係，而不是和鍵屬性之間的關係。

　　當前設計不符合3NF的“臭味”：

• 多個屬性有同樣的字首。
• 重複的資料組。
• 彙總的資料，所引用的資料在一個完全不同的實體中。（有些人傾向於使用檢視，我更傾向於使用物件集合，即由程式來完成。）

　　BCNF-實體滿足第一正規化，所有屬性完全依賴於某個鍵，如果所有的判定都是一個鍵，則實體滿足BCNF。（BCNF簡單地擴充套件了以前的正規化，它說的是：一個實體可能有若干個鍵，所有屬性都必須依賴於這些鍵中的一個，也可以理解為“每個鍵必須唯一標識實體，每個非鍵熟悉必須描述實體。”

　　3 去除實體組合鍵中的冗餘

　　4NF-實體必須滿足BCNF，在一個屬性與實體的鍵之間，多值依賴（一條記錄在整個表的唯一性由多個值組合起來決定的）不能超過一個。

　　當前設計不符合4NF的“臭味”：

• 三元關係（實體:實體:實體）。
• 潛伏的多值屬性。（如多個手機號。）
• 臨時資料或歷史值。（需要將歷史資料的主體提出，否則將存在大量冗餘。）

　　4 儘量將所有關係分解為二元關係　

　　5NF-實體必須滿足4NF，當分解的資訊無損的時候，確保所有關係都被分解為二元關係。

　　5NF保證在第四正規化中存在的任何可以分解為實體的三元關係都被分解。有的三元關係可以在不丟失資訊的前提下被分解為二元關係，當分解為兩個二元關係的過程要丟失資訊時，關係被宣稱為處於第四正規化中。所以，第五正規化建議是，最好把現有的三元關係都分解為3個二元關係。

　　需要注意的是，規範化的結果可能是更多的表，更復雜的查詢。因此，處理到何種程度，取決於效能和資料架構的多方考量。建議規範化到第四正規化，原因是5NF的判斷太過隱晦。例如：表X（老師，學生，課程）是一個三元關係，可以分解為表A（老師，學生），表B（學生，課程），表C（老師，課程）。表X表示某個老師是上某個學生的某個課程的老師；表A表示老師教學生；表B表示學生上課；表C表示老師教課。單獨看是無法發現問題的，但是從資料出發，"表X=表A+表B+表C"並不一定成立，即不能通過連線構建分解前的資料。因為可能有多種組合，喪失了表X反饋出的業務規則。這種現象，容易在設計階段被忽略，但好在在開放階段會被顯現，而且並不經常發生。

　　推薦做法：

• 儘可能地遵守上述規範化原則。
• 所有屬性描述的都應該是體現被建模實體的本質的內容。
• 至少必須有一個鍵，它唯一地標識和描述了所建實體的本質。
• 主鍵要謹慎選擇。
• 在邏輯階段能做多少規範化就做多少（效能不是邏輯階段考慮的範疇）。

　　（十）選擇資料型別（MS SQL 2008）

　　MS SQL的常用型別：

 

精確數字	不會發生精度損失	bit tinyint smallint int bigint decimal
近似數字	對於極值可能發生精度損失	float(N) real
日期和時間		date time smalldatetime datetime datetime2 datetimeoffset
二進位制資料		bingary(N) varbinary(N) varbinary(max)
字元（串）資料		char(N) varchar(N) varchar(max) nchar(N) nvarchar(N) nvarchar(max)
儲存任意資料		sql_variant
時間戳		timestamp
GUID		uniqueidentifier
XML	不要試圖使用該型別規避1NF	xml
空間資料		geometry geography
層次資料		heirarchyid

　　MS SQL中不在支援的或糟糕的型別選擇

• image：被varbinary(max)取代。
• text和ntext：被varchar(max)和nvarchar(max)取代。
• money和smallmoney：開發過程中不好用，建議使用decimal。

　　常用型別選擇：

　　型別選擇的最基本規則是選擇滿足需要的最輕的型別，因為這樣查詢更快。

 

bool	建議使用bit而非char(1)，因為開發語言對其支援覺好，可以直接對映為bool或bool?。
大值資料	使用所有備選型別中最小的那種，型別越大，查詢越慢，當位元組大於8000時，應使用max。
主鍵	自增主鍵根據預期範圍選擇int或bigint，GUID使用uniqueidentifier而非varchar(N)。

　　（十一）優化並行

　　設計DB時就應該考慮到對並行進行優化，比如，MS SQL中的timestamp型別就是極好的選擇。

　　四 命名規則

• 表——“模組名_表名”。表名最好不要用複數，原因是在使用ORM框架開發時，程式碼生成器根據DB生成類定義，表生成了某個例項的型別定義，而不是例項集合。表名不要太長。原因之一，某些軟體對錶名最大長度有限制；原因之二，使用程式碼生成器往往會根據表名生產型別名稱，之後懶人會直接使用這一名稱，如果將太長的名稱跨網路邊界顯然不是明智之舉。
• 欄位——bool型別用“Is”、“Can”、“Has”等表示；日期型別命名必須包含“Date”；時間型別必須包含“Time”。
• 儲存過程——使用“proc_”字首。
• 檢視——使用“view_”字首。
• 觸發器——使用“trig_”字首。