大型.NET ERP系統的20條資料庫設計規範

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

以下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 設計單據狀態欄位

欄位	含義
Posted	過帳，已確認
Closed	已完成
Cancelled	已取消
Approved	已批核
Issued	已發料
Finished	已完成
Suspended	已取消

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 各種日期欄位的設計

欄位名稱	含義
TranDate	日期帳日期 Tran是Transaction的簡寫
PostedDate	過帳日期
ClosedDate	完成日期
InvoiceDate	開發票日期
DueDate	截止日期
ScheduleDate	計劃日期，這個欄位用在不同的單據含義不同。比如銷售單是指送貨日期，採購單是指收貨日期。
OrderDate	訂單日期
PayDate	付款日期
CreatedDate	建立日期
RevisedDate	修改日期
SettleDate	付款日期
IssueDate	發出日期
ReceiptDate	收貨日期
ExpireDate	過期時間

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 簡寫

簡寫	全名
SL	Sales 銷售
PU	Purchasing 採購
IC	Inventory 倉庫
AR	Account Receivable 應收
AP	Account Payable 應付
GL	General Ledger 總帳
PR	Production 生產

名稱Name 簡寫

簡寫	全名
Uom	Unit of Measure 單位
Ccy	Currency 貨幣
Amt	Amount 金額
Qty	Quantity 數量
Qty Per	Quantity Per 用量
Std Output	Standard Output 標準產量
ETA	Estimated Time of Arrival 預定到達時間
ETD	Estimated Time of Departure 預定出發時間
COD	Cash On Delivery 貨到付款
SO	Sales Order 銷售單
PO	Purchase Order 採購單

20 庫存單據數量狀態

Qty On Hand 在手量

Qty Available 可用量

Qty On Inspect 在驗數量

Qty On Commited 提交數量

Qty Reserved 預留數量

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