數(shù)據(jù)庫(kù)設(shè)計(jì)規(guī)范是個(gè)技術(shù)含量相對(duì)低的話題，只需要對(duì)標(biāo)準(zhǔn)和規(guī)范的堅(jiān)持即可做到。當(dāng)系統(tǒng)越來(lái)越龐大，嚴(yán)格控制數(shù)據(jù)庫(kù)的設(shè)計(jì)人員，并且有一份規(guī)范書(shū)供執(zhí)行參考。在程序框架中，也有一份強(qiáng)制性的約定，當(dāng)不遵守規(guī)范時(shí)報(bào)錯(cuò)誤。

[[143687]]

以下20個(gè)條款是我從一個(gè)超過(guò)1000個(gè)數(shù)據(jù)庫(kù)表的大型ERP系統(tǒng)中提煉出來(lái)的設(shè)計(jì)約定，供參考。

1 所有的表的第一個(gè)字段是記錄編號(hào)Recnum，用于數(shù)據(jù)維護(hù)

[Recnum] [decimal] (8, 0) NOT NULL IDENTITY(1, 1)

在進(jìn)行數(shù)據(jù)維護(hù)的時(shí)候，我們可以直接這樣寫(xiě)：

UPDATE Company SET Code='FLEX' WHERE Recnum=23

2 每個(gè)表增加4個(gè)必備字段，用于記錄該筆數(shù)據(jù)的創(chuàng)建時(shí)間，創(chuàng)建人，最后修改人，最后修改時(shí)間

[CreatedDate] [datetime] NULL,
[CreatedBy] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RevisedDate] [datetime] NULL,
[RevisedBy] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

框架程序中會(huì)強(qiáng)制讀取這幾個(gè)字段，默認(rèn)寫(xiě)入值。

3 主從表的主外鍵設(shè)計(jì)

主表用參考編號(hào)RefNo作為主鍵，從表用RefNo,EntryNo作為主鍵。RefNo是字符串類(lèi)型，可用于單據(jù)編碼功能中自動(dòng)填寫(xiě)單據(jù)流水號(hào)，從表的EntryNo是行號(hào)，LineNo是SQL Server 的關(guān)鍵字，所以用EntryNo作為行號(hào)。

如果是三層表，則第三層表的主鍵依次是RefNo,EntryNo,DetailEntryNo，第三個(gè)主鍵用于自動(dòng)增長(zhǎng)行號(hào)。

4 設(shè)計(jì)單據(jù)狀態(tài)字段

5 字段含義相近，把相同的單詞調(diào)成前綴。

比如工作單中的成本核算，人工成本，機(jī)器成本，能源成本，用英文表示為L(zhǎng)aborCost,MachineCost,EnergyCost

但是為了方便規(guī)組，我們把Cost調(diào)到字段的前面，于是上面三個(gè)字段命名為CostLabor,CostMachine,CostEnergy。

可讀性后者要比前者好一點(diǎn)，Visual Studio或SQL Prompt智能感知也可幫助提高字段輸入的準(zhǔn)確率。

6 單據(jù)引用鍵命名 SourceRefNo SourceEntryNo

銷(xiāo)售送貨Shipment會(huì)引用到是送哪張銷(xiāo)售單據(jù)的，可以添加如下引用鍵SourceRefNo,SourceEntryNo，表示送貨單引用的銷(xiāo)售單的參考編號(hào)和行號(hào)。Source開(kāi)頭的字段一般用于單據(jù)引用關(guān)聯(lián)。

7 數(shù)據(jù)字典鍵設(shè)計(jì)

比如員工主檔界面的員工性別Gender，我的方法是在源代碼中用枚舉定義。性別枚舉定義如下：

public enum Gender
{
[StringValue("M")]
[DisplayText("Male")]
Male,

[StringValue("F")]
[DisplayText("Female")]
Female
}

在代碼中調(diào)用枚舉的通用方法，讀取枚舉的StringValue寫(xiě)入到數(shù)據(jù)庫(kù)中，讀取枚舉的DisplayText顯示在界面中。

經(jīng)過(guò)這一層設(shè)計(jì)，數(shù)據(jù)庫(kù)中有關(guān)字典方面的設(shè)計(jì)就規(guī)范起來(lái)了，避免了數(shù)據(jù)字典的項(xiàng)的增減給系統(tǒng)帶來(lái)的問(wèn)題。

8 數(shù)值類(lèi)型字段長(zhǎng)度設(shè)計(jì)

Price/Qty 數(shù)量/單價(jià) 6個(gè)小數(shù)位 nnnnnnnnnn.nnnnnn 格式 （10.6）

Amount 金額 2個(gè)小數(shù)位 nnnnnnnnnnnn.nn 格式（12.2）

Total Amt 總金額 2個(gè)小數(shù)位 nnnnnnnnnnnnnn.nn 格式（14.2）

參考編號(hào)默認(rèn)16個(gè)字符長(zhǎng)度，不夠用的情況下增加到30個(gè)字符，再不夠用增加到60個(gè)字符。這樣可以保證每張單據(jù)的第一個(gè)參考編號(hào)輸入控件看起來(lái)都是一樣長(zhǎng)度。

除非特別需求，一般而言，界面中控件的長(zhǎng)度取自映射的數(shù)據(jù)庫(kù)中字段的定義長(zhǎng)度。

9 每個(gè)單據(jù)表頭和明細(xì)各增加10個(gè)自定義字段，基礎(chǔ)資料表增加20個(gè)自定義字段

參考供應(yīng)商主檔的自定義字段，自定義字段的名稱統(tǒng)一用UserDefinedField。

ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_1] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_2] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_3] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_4] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_5] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_6] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_7] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_8] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_9] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_10] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_11] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_12] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_13] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_14] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_15] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_16] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_17] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_18] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_19] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE Vendor ADD COLUMN [USER_DEFINED_FIELD_20] nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

10 多貨幣(本位幣)轉(zhuǎn)換字段的設(shè)計(jì)

金額或單價(jià)默認(rèn)是以日記帳中的貨幣為記錄，當(dāng)默認(rèn)貨幣與本位幣不同時(shí)需要同時(shí)記錄下本位幣的值。

銷(xiāo)售單銷(xiāo)售金額 SalesAmount或SalesAmt,本位幣字段定義為SalesAmountLocal或SalesAmtLocal

通常是在原來(lái)的字段后面加Local表示本位幣的值。

11 各種日期字段的設(shè)計(jì)

12 財(cái)務(wù)有關(guān)的單據(jù)包含三個(gè)標(biāo)準(zhǔn)字段

FiscalYear 財(cái)年，PeriodNo 會(huì)計(jì)期間，Period 前面二個(gè)的組合。以國(guó)外的財(cái)年為例子，F(xiàn)iscalYear是2015，PeriodNo是4，Period是2015/04。

歐美會(huì)計(jì)期間是從每年的4月份開(kāi)始，需要注意的是會(huì)計(jì)期間與時(shí)間沒(méi)有必然的聯(lián)系，看到會(huì)計(jì)期間是2015/04，不一定是表示2015的4月份，它只是說(shuō)這是2015財(cái)年的第四期，具體在哪個(gè)時(shí)間段需要看會(huì)計(jì)期間定義。

13 單據(jù)自動(dòng)生成 DirectEntry

有些單據(jù)是由其它單據(jù)生成過(guò)來(lái)的，邏輯上應(yīng)該不支持編輯。比如銷(xiāo)售送貨Shipment單會(huì)產(chǎn)生出倉(cāng)單，出倉(cāng)單應(yīng)該不支持編輯，只能做過(guò)帳扣減庫(kù)存 操作。這時(shí)需要DirectEntry標(biāo)準(zhǔn)字段來(lái)表示。當(dāng)手工創(chuàng)建一張出倉(cāng)單時(shí)，將DirectEntry設(shè)為true,表示可編輯單據(jù)中的字段值，當(dāng)由 其它單據(jù)傳遞產(chǎn)生過(guò)來(lái)產(chǎn)生的出倉(cāng)單，將DirectEntry設(shè)為false，表示不能編輯此單據(jù)。這種情況還發(fā)生在業(yè)務(wù)單據(jù)產(chǎn)生記帳憑證 (Voucher)的功能中，如果可以修改由原始單據(jù)傳遞過(guò)來(lái)的數(shù)量金額等字段，則會(huì)導(dǎo)致與源單不匹配，給系統(tǒng)對(duì)帳產(chǎn)生困擾。

14 百分比值字段的設(shè)計(jì)

Percentage百分比值，用于折扣率，損耗率等相關(guān)比率設(shè)定的地方。推薦用數(shù)值類(lèi)型表示，用腳本表示是

[ScrapRate] [decimal] (5, 2) NULL

預(yù)留兩位小數(shù)，整數(shù)部分支持1-999三位數(shù)。常常是整數(shù)部分2位就可以，用3位也是為了支持一些特殊行業(yè)(物料損耗率超過(guò)100）的要求。

15 日志表記錄編號(hào)LogNo字段設(shè)計(jì)

LogNo字段的設(shè)計(jì)有些巧妙，以出倉(cāng)單為例子，一張出倉(cāng)單有5行物料明細(xì)，每一行物料出倉(cāng)都會(huì)扣減庫(kù)存，再寫(xiě)物料進(jìn)出日記帳，因?yàn)檫@五行物料出倉(cāng) 來(lái)自同一個(gè)出倉(cāng)單，于是將這五行物料的日記帳中的LogNo都設(shè)為同一個(gè)值。于在查詢數(shù)據(jù)時(shí)，以這個(gè)字段分組即可看到哪些物料是在同一個(gè)時(shí)間點(diǎn)上出倉(cāng)的， 對(duì)快速查詢有很重要的作用。

16 基礎(chǔ)資料表增加名稱，名稱長(zhǎng)寫(xiě)，代用名稱三個(gè)字段

比如供應(yīng)商Vendor表，給它加以下三個(gè)字段：

Description 供應(yīng)商名稱，比如微軟公司。

ExtDescription 供應(yīng)商名稱長(zhǎng)寫(xiě)，比如電氣行業(yè)的南網(wǎng)的全名是南方國(guó)家電網(wǎng)有限公司。

AltDescription 供應(yīng)商名稱替代名稱，用在報(bào)表或是其它單據(jù)引用中。比如采購(gòu)單中的供應(yīng)商是用微軟，還是用代用名稱Microsoft，由參數(shù)(是否用代用名稱)控制。

17 文件類(lèi)表增加MD5 Hash字段

比如產(chǎn)品數(shù)據(jù)管理系統(tǒng)要讀取圖紙，單據(jù)功能中增加的附件文件，這類(lèi)涉及文件讀寫(xiě)引用的地方，考慮存放文件的MD5哈希值。文件的MD5相當(dāng)于文件的 唯一識(shí)別身份，在網(wǎng)上下載文件時(shí)，網(wǎng)站常常會(huì)放出文件的MD5值，以方便對(duì)比核對(duì)。當(dāng)下載到本機(jī)的文件的MD5值與網(wǎng)站上給出的值不一致時(shí)，有可能這個(gè)文 件被第三方程序修改過(guò)，不可信任。

18 數(shù)據(jù)表的主鍵用字符串而不是數(shù)字

比如銷(xiāo)售單中的貨幣字段，是存放貨幣表的貨幣字符串值RMB/HKD/USD，還是存放貨幣表的數(shù)字鍵，1/2/3。

存放前者對(duì)于報(bào)表制作相對(duì)容易，但是修改起來(lái)相對(duì)麻煩。存放后者對(duì)修改數(shù)據(jù)容易，但對(duì)報(bào)表類(lèi)或查詢類(lèi)操作都需要增加一個(gè)左右連接來(lái)看數(shù)字代表的貨幣。金蝶使用的是后者，它的BOS系統(tǒng)也不允許數(shù)據(jù)表之間有直接的關(guān)聯(lián)，而是間接通過(guò)Id值來(lái)關(guān)聯(lián)表。

在我看到的系統(tǒng)中，只有一個(gè)會(huì)計(jì)期間功能(財(cái)年Fiscal Year)用到數(shù)字值作主鍵，其余的單據(jù)全部是字符串做主鍵。

19 使用約定俗成的簡(jiǎn)寫(xiě)

模塊Module 簡(jiǎn)寫(xiě)

名稱Name 簡(jiǎn)寫(xiě)

20 庫(kù)存單據(jù)數(shù)量狀態(tài)

Qty On Hand 在手量

Qty Available 可用量

Qty On Inspect 在驗(yàn)數(shù)量

Qty On Commited 提交數(shù)量

Qty Reserved 預(yù)留數(shù)量

以上每個(gè)字段都有標(biāo)準(zhǔn)和行業(yè)約定的含義，不可隨意修改取數(shù)方法。