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

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

　　從筆者的經曆看來，筆者更贊成在項目早期由開發者進行資料庫設計（後期調優需要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

空間資料

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_”字首。