數(shù)據(jù)庫設(shè)計（Database Design)是指對于一個給定的應(yīng)用環(huán)境，構(gòu)造最優(yōu)的數(shù)據(jù)庫模式，建立數(shù)據(jù)庫及其應(yīng)用系統(tǒng)，使之能夠有效地存儲數(shù)據(jù)，滿足各種用戶的應(yīng)用需求（信息要求和處理要求）。

一、數(shù)據(jù)庫設(shè)計的原則

1.      表設(shè)計原則

（1）規(guī)范化與反規(guī)范化

規(guī)范化的優(yōu)點是減少了數(shù)據(jù)冗余，節(jié)約了存儲空間，相應(yīng)邏輯和物理的I/O次數(shù)減少，同時加快了增、刪、改的速度。但是一個完全規(guī)范化的設(shè)計并不總能生成最優(yōu)的性能，因為對數(shù)據(jù)庫查詢通常需要更多的連接操作，從而影響到查詢的速度，而且范式越高性能就會越差。出于性能和方便管理的考慮，原則上表設(shè)計應(yīng)滿足第三范式。有時為了提高某些查詢或應(yīng)用的性能而可以破壞規(guī)范規(guī)則，即反規(guī)范化。

數(shù)據(jù)應(yīng)當(dāng)按兩種類別進(jìn)行組織：頻繁訪問的數(shù)據(jù)和頻繁修改的數(shù)據(jù)。對于頻繁訪問但是不頻繁修改的數(shù)據(jù)，內(nèi)部設(shè)計應(yīng)當(dāng)物理不規(guī)范化。對于頻繁修改但并不頻繁訪問的數(shù)據(jù)，內(nèi)部設(shè)計應(yīng)當(dāng)物理規(guī)范化。比較復(fù)雜的方法是將規(guī)范化的表作為邏輯數(shù)據(jù)庫設(shè)計的基礎(chǔ)，然后再根據(jù)整個應(yīng)用系統(tǒng)的需要，物理地非規(guī)范化數(shù)據(jù)。

（2）數(shù)據(jù)表分類說明

    根據(jù)應(yīng)用的實際需要和特點，可以將數(shù)據(jù)表進(jìn)行如下分類：

l        基本數(shù)據(jù)表：描述業(yè)務(wù)實體的基本信息。例如，人員基本信息、單位基本信息等。

l        標(biāo)準(zhǔn)編碼表：描述屬性的列表值。例如，職稱、民族、狀態(tài)等。

l        業(yè)務(wù)數(shù)據(jù)表：記錄業(yè)務(wù)發(fā)生的過程和結(jié)果。例如，人員調(diào)動登記、變更通知單等。

l        系統(tǒng)信息表：存放與系統(tǒng)操作、業(yè)務(wù)控制有關(guān)的參數(shù)。例如，用戶信息、權(quán)限、用戶配置信息等。

l        統(tǒng)計數(shù)據(jù)表：存放業(yè)務(wù)數(shù)據(jù)統(tǒng)計值。例如，通知單統(tǒng)計、人員類別統(tǒng)計等。

l        臨時處理表：存放業(yè)務(wù)處理過程中的中間結(jié)果。

l        其他類型表：存放應(yīng)用層的日志、消息記錄等。

2.      字段設(shè)計原則

（1）一般來說，應(yīng)該使用能正確存儲和表示數(shù)據(jù)的最小類型。如果不確定需要什么數(shù)據(jù)類型，則選擇不會超出范圍的最小類型。

（2）選擇更簡單的數(shù)據(jù)類型。例如，比較整數(shù)的代價小于比較字符，因為字符集和排序規(guī)則使字符比較更復(fù)雜。

（3）盡可能把字段定義為 NOT NULL。對于字段能否NULL，應(yīng)該在SQL建表腳本中明確指明，不應(yīng)使用缺省。

（4）一個表中的字段不要太多，理論上不要超過80個。

（5）數(shù)據(jù)庫中所有布爾型中數(shù)值0表示為假；數(shù)值1表示為真

（6）當(dāng)字段定義為字符串類型時使用VARCHAR2而不用NVARCHAR

（7）字段盡可能有默認(rèn)值，字符型的默認(rèn)值為一個空字符值串，數(shù)字型的默認(rèn)值為數(shù)值0。

3.      鍵設(shè)計原則

（1）為關(guān)聯(lián)字段創(chuàng)建外鍵。

（2）所有的鍵都必須唯一。

（3）盡可能避免使用復(fù)合鍵。

（4）外鍵總是關(guān)聯(lián)唯一的鍵字段。

（5）盡可能使用系統(tǒng)生成（如序列SEQUENCE產(chǎn)生）的主鍵。

（6）可選鍵有時可做主鍵。

（7）一個表中組合主鍵的字段個數(shù)盡可能少。

4.      索引設(shè)計原則

（1）如果一列出現(xiàn)在表達(dá)式或函數(shù)中，不會使用該列上的索引

（2）要索引外鍵

（3）對于索引選擇性高的列使用B-Tree索引

（4）對于索引選擇性低的列使用位圖索引

（5）HASH索引只適用于相等比較

（6）不要索引大型字段（有很多字符的字段）

（7）不要索引常用的小型表

5.      LOB設(shè)計原則

如無特別需要，避免使用大字段（BLOB、CLOB、LONG等）。如使用時必須使用BLOB或CLOB類型。

二、完整性設(shè)計

采用數(shù)據(jù)庫系統(tǒng)實現(xiàn)數(shù)據(jù)的完整性。這不但包括通過標(biāo)準(zhǔn)化實現(xiàn)的完整性而且還包括數(shù)據(jù)的功能性。在寫數(shù)據(jù)的時候還可以增加觸發(fā)器來保證數(shù)據(jù)的正確性。不要依賴于應(yīng)用程序保證數(shù)據(jù)完整性，它不能保證表之間（外鍵）的完整性。

1．主鍵約束

每個表要求有主健，主健字段或組合字段必須滿足非空屬性和唯一性要求。

2．外鍵約束

（1）對于關(guān)聯(lián)兩個表的字段，一般應(yīng)該分別建立主鍵、外鍵。實際是否建立外鍵，根據(jù)對數(shù)據(jù)完整性的要求決定。

（2）根據(jù)需要適當(dāng)設(shè)置父表數(shù)據(jù)修改時對子表的影響：

l        父表中刪除數(shù)據(jù)：級聯(lián)刪除；受限刪除；置空值。

l        父表中插入數(shù)據(jù)：受限插入；遞歸插入。

l        父表中更新數(shù)據(jù)：級聯(lián)更新；受限更新；置空值。

3．NULL值

由于NULL值在參加任何運算時，結(jié)果均為NULL，所以必須利用NVL()函數(shù)把可能為NULL值得字段或變量轉(zhuǎn)換為非NULL的默認(rèn)值。

4．CHECK條件

對于字段有檢查性約束，要求指定CHECK規(guī)則。

5．觸發(fā)器

觸發(fā)器是一種特殊的存儲過程，通過對表的DML操作而觸發(fā)執(zhí)行，是為確保數(shù)據(jù)的完整性和一致性不被破壞而創(chuàng)建，實現(xiàn)數(shù)據(jù)的完整約束。選擇觸發(fā)器的BEFORE或AFTER事務(wù)屬性的時候，對表操作的事務(wù)屬性必須與應(yīng)用程序事務(wù)屬性保持一致，以避免死鎖發(fā)生。在大量修改數(shù)據(jù)時，盡量避免使用觸發(fā)器。

6．視圖

為了在數(shù)據(jù)庫和應(yīng)用程序之間提供另一層抽象，可以為應(yīng)用程序建立專門的視圖而不必非要應(yīng)用程序直接訪問表。這樣做還在處理數(shù)據(jù)庫變更時提供了更多的自由。視圖是虛擬的數(shù)據(jù)庫表，在使用時要遵循以下原則：

l        為簡化查詢，將復(fù)雜的檢索或子查詢通過視圖實現(xiàn)。

l        提高數(shù)據(jù)的安全性，只將需要查看的數(shù)據(jù)信息顯示給權(quán)限有限的人員。

l        視圖中如果嵌套使用視圖，級數(shù)不要超過3級。

l        由于視圖中只能固定條件或沒有條件，所以對于數(shù)據(jù)量較大或隨時間的推移逐漸增多的表，不宜使用視圖，可以采用實體化視圖代替。

l        除特殊需要，避免類似SELECT * FROM [TableName] 而沒有檢索條件的視圖。

l        視圖中盡量避免出現(xiàn)數(shù)據(jù)排序的SQL語句。

三、命名規(guī)范

1．總則

（1）所有命名采用26個英文大小寫字母和0－9這十個自然數(shù)，加上下劃線_組成。不能出現(xiàn)其他字符（注釋除外）。

（2）長度不超過30個字符。

（3）實際名字盡量描述實體的內(nèi)容，由英文單詞、單詞組合或單詞縮寫組成，不以數(shù)字和_開頭。

（4）命名中禁止使用SQL關(guān)鍵字。

（5）對象名盡量短。

2．表

表以單數(shù)形式名詞或名詞短語命名。如果表名僅有一個單詞，那么建議不使用縮寫，而是用完整的單詞。

數(shù)據(jù)表     t_inf_<系統(tǒng)標(biāo)識>_<表標(biāo)識>

編碼表     t_cod_<系統(tǒng)標(biāo)識>_<表標(biāo)識>

系統(tǒng)表    t_sys_<系統(tǒng)標(biāo)識>_<表標(biāo)識>

統(tǒng)計表     t_sta_<系統(tǒng)標(biāo)識>_<表標(biāo)識>

臨時表     t_tmp_<系統(tǒng)標(biāo)識>_<表標(biāo)識>

日志表     t_log_<系統(tǒng)標(biāo)識>_<表標(biāo)識>

3．字段

l        采用有意義的字段名，應(yīng)該是易于理解，能表達(dá)字段功能的英文單詞或單詞縮寫，一般不超過三個英文單詞。

l        系統(tǒng)中所有屬于內(nèi)碼的字段（僅用于表示唯一性和程序內(nèi)部用到的標(biāo)識性字段），名稱取為：ID。

l        系統(tǒng)中屬于是業(yè)務(wù)范圍內(nèi)的編號的字段，其代表一定的業(yè)務(wù)信息，這樣的字段建議命名為CODE，其數(shù)據(jù)類型為VARCHAR，該字段需加唯一索引。

l        字段名不要與表名重復(fù)

l        不要在列的名稱中包含數(shù)據(jù)類型。

4．主鍵

PK_<表名>

5．外鍵

FK_<表名>_<主表名>_<外鍵字段名>

6．索引

IDX_<表名>_<構(gòu)成索引的字段名>

如果復(fù)合索引的構(gòu)成字段較多，則只包含第一個字段，并添加序號。

7．視圖

V_<系統(tǒng)標(biāo)識>_<視圖標(biāo)識>

8．存儲過程

SP_<系統(tǒng)標(biāo)識>_<存儲過程標(biāo)識>

9．函數(shù)

F_<系統(tǒng)標(biāo)識>_<函數(shù)標(biāo)識>

10．            觸發(fā)器

TR_<表名>_<i、u、d的任意組合>

11．            用戶定義數(shù)據(jù)類型

UD_<自定義數(shù)據(jù)類型標(biāo)識>

12．            序列

SEQ_<序列標(biāo)識>

13．            局部變量

L_<變量標(biāo)識>

14．            全局變量

G_<變量標(biāo)識>

15．            游標(biāo)變量

L_CUR_<變量標(biāo)識>或G_CUR_<變量標(biāo)識>

16．            存儲過程或函數(shù)定義中的參數(shù)

IN型參數(shù)：P_<參數(shù)標(biāo)識>

OUT型參數(shù)：R_<參數(shù)標(biāo)識>

函數(shù)返回值：R_<變量標(biāo)識>

四、安全性設(shè)計

1．管理默認(rèn)用戶

在生產(chǎn)環(huán)境中，必須嚴(yán)格管理SYS和SYSTEM用戶，必須修改其默認(rèn)密碼，禁止用該用戶建立應(yīng)用數(shù)據(jù)庫對象。刪除或鎖定SCOTT等默認(rèn)安裝但不使用的用戶。

2．?dāng)?shù)據(jù)庫級用戶權(quán)限設(shè)計

必須按照應(yīng)用需求，設(shè)計不同的用戶訪問權(quán)限。包括應(yīng)用系統(tǒng)管理用戶，普通用戶等，按照業(yè)務(wù)需求建立不同的應(yīng)用角色。用戶訪問另外的用戶對象時，應(yīng)該通過創(chuàng)建同義詞對象SYNONYM進(jìn)行訪問。

3．角色與權(quán)限

確定每個角色對數(shù)據(jù)庫表的操作權(quán)限，如創(chuàng)建、檢索、更新、刪除等。每個角色擁有剛好能夠完成任務(wù)的權(quán)限，不多也不少。在應(yīng)用時再為用戶分配角色，則每個用戶的權(quán)限等于他所兼角色的權(quán)限之和。

4．應(yīng)用級用戶設(shè)計

應(yīng)用級的用戶賬號密碼不能與數(shù)據(jù)庫相同，防止用戶直接操作數(shù)據(jù)庫。用戶只能用賬號登錄到應(yīng)用軟件，通過應(yīng)用軟件訪問數(shù)據(jù)庫，而沒有其它途徑操作數(shù)據(jù)庫。

5．用戶密碼管理

用戶賬號的密碼必須進(jìn)行加密處理，確保在任何地方查詢都不會出現(xiàn)密碼的明文。

五、SQL語句編寫

1．字符類型數(shù)據(jù)

SQL中的字符類型數(shù)據(jù)應(yīng)該統(tǒng)一使用單引號。特別對純數(shù)字的字符串，必須用單引號，否則會導(dǎo)致內(nèi)部轉(zhuǎn)換而引起性能問題或索引失效問題。利用TRIM(),LOWER()等函數(shù)格式化匹配條件。

2．復(fù)雜SQL

對于非常復(fù)雜的SQL（特別是有多層嵌套，帶子句或相關(guān)子查詢的），應(yīng)該先考慮是否設(shè)計不當(dāng)引起的。對于一些復(fù)雜SQL可以考慮使用程序?qū)崿F(xiàn)。

3．避免IN子句

使用 IN 或 NOT IN 子句時，特別是當(dāng)子句中有多個值且表數(shù)據(jù)較多時，速度會明顯下降?？梢圆捎眠B接查詢或外連接查詢來提高性能。

4．避免使用SELECT * 語句

如果不必要取出所有數(shù)據(jù)，不要用 * 來代替，應(yīng)給出字段列表。

5．避免不必要的排序

不必要的數(shù)據(jù)排序大大的降低系統(tǒng)性能。

6．INSERT語句

使用INSERT語句一定要給出插入值的字段列表，這樣即使表加了字段也不會影響現(xiàn)有系統(tǒng)的運行。

7．多表連接

做多表操作時，應(yīng)該給每個表取一個別名，每個表字段都應(yīng)該標(biāo)明其所屬哪個表。

 8．參數(shù)的傳遞

SQL語句的編寫，變量盡量使用“?”綁定變量。

9．存儲過程、函數(shù)中的注釋，示例如下：

/*

目的：

作者：

創(chuàng)建日期：

*/

/*

修改順序號：

修改者：

修改日期：

修改原因：（具體原因詳細(xì)描述）

*/

六、建模管理方法

1．建模工具

統(tǒng)一使用PowerDesigner軟件建模。推薦版本PowerDesigner 15中文版。

2．建模步驟

（1）邏輯建模

根據(jù)數(shù)據(jù)庫設(shè)計和命名規(guī)范先在PowerDesigner中建立邏輯模型(LDM)文件。要求每個表和字段都要有注釋說明；Check Model不能出現(xiàn)錯誤。

（2）根據(jù)邏輯模型文件創(chuàng)建對應(yīng)數(shù)據(jù)庫的物理模型文件。

（3）生成數(shù)據(jù)庫結(jié)構(gòu)及其相應(yīng)的SQL腳本。

3．模型維護

（1）所有關(guān)于數(shù)據(jù)庫的表、字段及關(guān)系、說明等均以物理模型文件為準(zhǔn)。

（2）由開發(fā)人員將變更需求提交項目負(fù)責(zé)人審批。

（3）項目負(fù)責(zé)人同意變更后由相應(yīng)開發(fā)人員負(fù)責(zé)編寫變更腳本提交DBA。

（4）DBA更新數(shù)據(jù)庫及其相關(guān)文檔，并維護所有部分的一致性。

七、其他設(shè)計技巧

1．避免使用觸發(fā)器

觸發(fā)器的功能通常可以用其他方式實現(xiàn)。在調(diào)試程序時觸發(fā)器可能成為干擾。假如確實需要采用觸發(fā)器，最好集中對它文檔化。

2．保存常用信息

讓一個表專門存放一般數(shù)據(jù)庫信息非常有用。在這個表里存放數(shù)據(jù)庫當(dāng)前版本、最近檢查/修復(fù)、關(guān)聯(lián)設(shè)計文檔的名稱、客戶等信息。這樣可以實現(xiàn)一種簡單機制跟蹤數(shù)據(jù)庫。

3．包含版本機制

在數(shù)據(jù)庫中引入版本控制機制來確定使用中的數(shù)據(jù)庫的版本。時間一長，用戶的需求總是會改變的。最終可能會要求修改數(shù)據(jù)庫結(jié)構(gòu)。把版本信息直接存放到數(shù)據(jù)庫中更為方便。

4．編制文檔

對所有的命名規(guī)范、限制、數(shù)據(jù)字典、存儲過程、函數(shù)都要編制文檔。采用給表、列、觸發(fā)器等加注釋的數(shù)據(jù)庫工具。對開發(fā)、支持和跟蹤修改非常有用。對數(shù)據(jù)庫文檔化也會大大減少犯錯的機會。

5．測試、測試、反復(fù)測試

建立或者修訂數(shù)據(jù)庫之后，必須用用戶新輸入的數(shù)據(jù)測試修改的字段。最重要的是，讓用戶進(jìn)行測試并且同用戶一起保證選擇的數(shù)據(jù)類型滿足要求。測試需要在把新數(shù)據(jù)庫投入實際服務(wù)之前完成。

6．檢查設(shè)計

在開發(fā)期間檢查數(shù)據(jù)庫設(shè)計的常用技術(shù)是通過其所支持的應(yīng)用程序原型檢查數(shù)據(jù)庫。換句話說，針對每一種最終表達(dá)數(shù)據(jù)的原型應(yīng)用，保證檢查了數(shù)據(jù)模型并且查看如何取出數(shù)據(jù)。