解析大型.NET ERP系統 20條數據庫設計規范

原文出處： James Li  

數據庫設計規范是個技術含量相對低的話題，只需要對標準和規范的堅持即可做到。當系統越來越龐大，嚴格控制數據庫的設計人員，并且有一份規范書供執行參考。在程序框架中，也有一份強制性的約定，當不遵守規范時報錯誤。

以下20個條款是我從一個超過1000個數據庫表的大型ERP系統中提煉出來的設計約定，供參考。

 
1 所有的表的第一個字段是記錄編號Recnum，用于數據維護

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

在進行數據維護的時候，我們可以直接這樣寫：

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

 
2 每個表增加4個必備字段，用于記錄該筆數據的創建時間，創建人，最后修改人，最后修改時間

[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

框架程序中會強制讀取這幾個字段，默認寫入值。

 
3 主從表的主外鍵設計

主表用參考編號RefNo作為主鍵，從表用RefNo,EntryNo作為主鍵。RefNo是字符串類型，可用于單據編碼功能中自動填寫單據流水號，從表的EntryNo是行號，LineNo是SQL Server 的關鍵字，所以用EntryNo作為行號。

如果是三層表，則第三層表的主鍵依次是RefNo,EntryNo,DetailEntryNo，第三個主鍵用于自動增長行號。

 
4 設計單據狀態字段

 
5 字段含義相近，把相同的單詞調成前綴。

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

但是為了方便規組，我們把Cost調到字段的前面，于是上面三個字段命名為CostLabor,CostMachine,CostEnergy。

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

 
6 單據引用鍵命名 SourceRefNo SourceEntryNo

銷售送貨Shipment會引用到是送哪張銷售單據的，可以添加如下引用鍵SourceRefNo,SourceEntryNo，表示送貨單引用的銷售單的參考編號和行號。Source開頭的字段一般用于單據引用關聯。

 
7 數據字典鍵設計

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

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

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

在代碼中調用枚舉的通用方法，讀取枚舉的StringValue寫入到數據庫中，讀取枚舉的DisplayText顯示在界面中。

經過這一層設計，數據庫中有關字典方面的設計就規范起來了，避免了數據字典的項的增減給系統帶來的問題。

 
8 數值類型字段長度設計

Price/Qty 數量/單價 6個小數位 nnnnnnnnnn.nnnnnn 格式 （10.6）
Amount 金額 2個小數位 nnnnnnnnnnnn.nn 格式（12.2）
Total Amt 總金額 2個小數位 nnnnnnnnnnnnnn.nn 格式（14.2）

參考編號默認16個字符長度，不夠用的情況下增加到30個字符，再不夠用增加到60個字符。這樣可以保證每張單據的第一個參考編號輸入控件看起來都是一樣長度。

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

 
9 每個單據表頭和明細各增加10個自定義字段，基礎資料表增加20個自定義字段

參考供應商主檔的自定義字段，自定義字段的名稱統一用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 多貨幣(本位幣)轉換字段的設計

金額或單價默認是以日記帳中的貨幣為記錄，當默認貨幣與本位幣不同時需要同時記錄下本位幣的值。

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

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

 
11 各種日期字段的設計

 
12 財務有關的單據包含三個標準字段

FiscalYear 財年，PeriodNo 會計期間，Period 前面二個的組合。以國外的財年為例子，FiscalYear是2015，PeriodNo是4，Period是2015/04。

歐美會計期間是從每年的4月份開始，需要注意的是會計期間與時間沒有必然的聯系，看到會計期間是2015/04，不一定是表示2015的4月份，它只是說這是2015財年的第四期，具體在哪個時間段需要看會計期間定義。

 
13 單據自動生成 DirectEntry

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

 
14 百分比值字段的設計

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

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

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

 
15 日志表記錄編號LogNo字段設計

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

 
16 基礎資料表增加名稱，名稱長寫，代用名稱三個字段

比如供應商Vendor表，給它加以下三個字段：

Description 供應商名稱，比如微軟公司。

ExtDescription 供應商名稱長寫，比如電氣行業的南網的全名是南方國家電網有限公司。

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

 
17 文件類表增加MD5 Hash字段

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

 
18 數據表的主鍵用字符串而不是數字

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

存放前者對于報表制作相對容易，但是修改起來相對麻煩。存放后者對修改數據容易，但對報表類或查詢類操作都需要增加一個左右連接來看數字代表的貨幣。金蝶使用的是后者，它的BOS系統也不允許數據表之間有直接的關聯，而是間接通過Id值來關聯表。

在我看到的系統中，只有一個會計期間功能(財年Fiscal Year)用到數字值作主鍵，其余的單據全部是字符串做主鍵。

 
19 使用約定俗成的簡寫

模塊Module 簡寫

名稱Name 簡寫

 
20 庫存單據數量狀態

Qty On Hand 在手量

Qty Available 可用量

Qty On Inspect 在驗數量

Qty On Commited 提交數量

Qty Reserved 預留數量

以上每個字段都有標準和行業約定的含義，不可隨意修改取數方法。