以下20個(gè)條款是我從一個(gè)超過1000個(gè)數(shù)據(jù)庫表的大型ERP系統(tǒng)中提煉出來的設(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í)候，我們可以直接這樣寫：

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)寫入值。

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

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

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

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

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

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

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

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

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

銷售送貨Shipment會(huì)引用到是送哪張銷售單據(jù)的，可以添加如下引用鍵SourceRefNo,SourceEntryNo，表示送貨單引用的銷售單的參考編號(hào)和行號(hào)。Source開頭的字段一般用于單據(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寫入到數(shù)據(jù)庫中，讀取枚舉的DisplayText顯示在界面中。

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

8 數(shù)值類型字段長度設(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è)字符長度，不夠用的情況下增加到30個(gè)字符，再不夠用增加到60個(gè)字符。這樣可以保證每張單據(jù)的第一個(gè)參考編號(hào)輸入控件看起來都是一樣長度。

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

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í)記錄下本位幣的值。

銷售單銷售金額 SalesAmount或SalesAmt,本位幣字段定義為SalesAmountLocal或SalesAmtLocal

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

17 文件類表增加MD5 Hash字段

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

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

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

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

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

19 使用約定俗成的簡寫

模塊Module 簡寫

名稱Name 簡寫

20  庫存單據(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ù)方法。