建站
咨詢
售后
建站咨詢
新聞中心
數(shù)據(jù)庫設(shè)計規(guī)范是個技術(shù)含量相對低的話題，只需要對標準和規(guī)范的堅持即可做到。當系統(tǒng)越來越龐大，嚴格控制數(shù)據(jù)庫的設(shè)計人員，并且有一份規(guī)范書供執(zhí)行參考。在程序框架中，也有一份強制性的約定，當不遵守規(guī)范時報錯誤。
[[142376]]
以下20個條款是我從一個超過1000個數(shù)據(jù)庫表的大型ERP系統(tǒng)中提煉出來的設(shè)計約定，供參考。
1 所有的表的第一個字段是記錄編號Recnum，用于數(shù)據(jù)維護
[Recnum] [decimal] (8, 0) NOT NULL IDENTITY(1, 1)
在進行數(shù)據(jù)維護的時候，我們可以直接這樣寫：
UPDATE Company SET Code='FLEX' WHERE Recnum=23
2 每個表增加4個必備字段，用于記錄該筆數(shù)據(jù)的創(chuàng)建時間，創(chuàng)建人，最后修改人，最后修改時間
[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 主從表的主外鍵設(shè)計
主表用參考編號RefNo作為主鍵，從表用RefNo,EntryNo作為主鍵。RefNo是字符串類型，可用于單據(jù)編碼功能中自動填寫單據(jù)流水號，從表的EntryNo是行號，LineNo是SQL Server 的關(guān)鍵字，所以用EntryNo作為行號。
如果是三層表，則第三層表的主鍵依次是RefNo,EntryNo,DetailEntryNo，第三個主鍵用于自動增長行號。
4 設(shè)計單據(jù)狀態(tài)字段
|
字段 |
含義 |
|
Posted |
過帳，已確認 |
|
Closed |
已完成 |
|
Cancelled |
已取消 |
|
Approved |
已批核 |
|
Issued |
已發(fā)料 |
|
Finished |
已完成 |
|
Suspended |
已取消 |
5 字段含義相近，把相同的單詞調(diào)成前綴。
比如工作單中的成本核算，人工成本，機器成本，能源成本，用英文表示為LaborCost,MachineCost,EnergyCost
但是為了方便規(guī)組，我們把Cost調(diào)到字段的前面，于是上面三個字段命名為CostLabor,CostMachine,CostEnergy。
可讀性后者要比前者好一點，Visual Studio或SQL Prompt智能感知也可幫助提高字段輸入的準確率。
6 單據(jù)引用鍵命名 SourceRefNo SourceEntryNo
銷售送貨Shipment會引用到是送哪張銷售單據(jù)的，可以添加如下引用鍵SourceRefNo,SourceEntryNo，表示送貨單引用的銷售單的參考編號和行號。Source開頭的字段一般用于單據(jù)引用關(guān)聯(lián)。
7 數(shù)據(jù)字典鍵設(shè)計
比如員工主檔界面的員工性別Gender，我的方法是在源代碼中用枚舉定義。性別枚舉定義如下：
public enum Gender
{
[StringValue("M")] [DisplayText("Male")] Male, [StringValue("F")] [DisplayText("Female")]
Female
}
在代碼中調(diào)用枚舉的通用方法，讀取枚舉的StringValue寫入到數(shù)據(jù)庫中，讀取枚舉的DisplayText顯示在界面中。
經(jīng)過這一層設(shè)計，數(shù)據(jù)庫中有關(guān)字典方面的設(shè)計就規(guī)范起來了，避免了數(shù)據(jù)字典的項的增減給系統(tǒng)帶來的問題。
8 數(shù)值類型字段長度設(shè)計
Price/Qty 數(shù)量/單價 6個小數(shù)位 nnnnnnnnnn.nnnnnn 格式 （10.6）
Amount 金額 2個小數(shù)位 nnnnnnnnnnnn.nn 格式（12.2）
Total Amt 總金額 2個小數(shù)位 nnnnnnnnnnnnnn.nn 格式（14.2）
參考編號默認16個字符長度，不夠用的情況下增加到30個字符，再不夠用增加到60個字符。這樣可以保證每張單據(jù)的第一個參考編號輸入控件看起來都是一樣長度。
除非特別需求，一般而言，界面中控件的長度取自映射的數(shù)據(jù)庫中字段的定義長度。
9 每個單據(jù)表頭和明細各增加10個自定義字段，基礎(chǔ)資料表增加20個自定義字段
參考供應(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è)計
金額或單價默認是以日記帳中的貨幣為記錄，當默認貨幣與本位幣不同時需要同時記錄下本位幣的值。
銷售單銷售金額 SalesAmount或SalesAmt,本位幣字段定義為SalesAmountLocal或SalesAmtLocal
通常是在原來的字段后面加Local表示本位幣的值。
11 各種日期字段的設(shè)計
|
字段名稱 |
含義 |
|
TranDate |
日期帳日期 Tran是Transaction的簡寫 |
|
PostedDate |
過帳日期 |
|
ClosedDate |
完成日期 |
|
InvoiceDate |
開發(fā)票日期 |
|
DueDate |
截止日期 |
|
ScheduleDate |
計劃日期，這個字段用在不同的單據(jù)含義不同。比如銷售單是指送貨日期，采購單是指收貨日期。 |
|
OrderDate |
訂單日期 |
|
PayDate |
付款日期 |
|
CreatedDate |
創(chuàng)建日期 |
|
RevisedDate |
修改日期 |
|
SettleDate |
付款日期 |
|
IssueDate |
發(fā)出日期 |
|
ReceiptDate |
收貨日期 |
|
ExpireDate |
過期時間 |
12 財務(wù)有關(guān)的單據(jù)包含三個標準字段
FiscalYear 財年，PeriodNo 會計期間，Period 前面二個的組合。以國外的財年為例子，F(xiàn)iscalYear是2015，PeriodNo是4，Period是2015/04。
歐美會計期間是從每年的4月份開始，需要注意的是會計期間與時間沒有必然的聯(lián)系，看到會計期間是2015/04，不一定是表示2015的4月份，它只是說這是2015財年的第四期，具體在哪個時間段需要看會計期間定義。
13 單據(jù)自動生成 DirectEntry
有些單據(jù)是由其它單據(jù)生成過來的，邏輯上應(yīng)該不支持編輯。比如銷售送貨Shipment單會產(chǎn)生出倉單，出倉單應(yīng)該不支持編輯，只能做過帳扣減庫存 操作。這時需要DirectEntry標準字段來表示。當手工創(chuàng)建一張出倉單時，將DirectEntry設(shè)為true,表示可編輯單據(jù)中的字段值，當由 其它單據(jù)傳遞產(chǎn)生過來產(chǎn)生的出倉單，將DirectEntry設(shè)為false，表示不能編輯此單據(jù)。這種情況還發(fā)生在業(yè)務(wù)單據(jù)產(chǎn)生記帳憑證 (Voucher)的功能中，如果可以修改由原始單據(jù)傳遞過來的數(shù)量金額等字段，則會導致與源單不匹配，給系統(tǒng)對帳產(chǎn)生困擾。
14 百分比值字段的設(shè)計
Percentage百分比值，用于折扣率，損耗率等相關(guān)比率設(shè)定的地方。推薦用數(shù)值類型表示，用腳本表示是
[ScrapRate] [decimal] (5, 2) NULL
預(yù)留兩位小數(shù)，整數(shù)部分支持1-999三位數(shù)。常常是整數(shù)部分2位就可以，用3位也是為了支持一些特殊行業(yè)(物料損耗率超過100）的要求。
15 日志表記錄編號LogNo字段設(shè)計
LogNo字段的設(shè)計有些巧妙，以出倉單為例子，一張出倉單有5行物料明細，每一行物料出倉都會扣減庫存，再寫物料進出日記帳，因為這五行物料出倉 來自同一個出倉單，于是將這五行物料的日記帳中的LogNo都設(shè)為同一個值。于在查詢數(shù)據(jù)時，以這個字段分組即可看到哪些物料是在同一個時間點上出倉的， 對快速查詢有很重要的作用。
16 基礎(chǔ)資料表增加名稱，名稱長寫，代用名稱三個字段
比如供應(yīng)商Vendor表，給它加以下三個字段：
Description 供應(yīng)商名稱，比如微軟公司。
ExtDescription 供應(yīng)商名稱長寫，比如電氣行業(yè)的南網(wǎng)的全名是南方國家電網(wǎng)有限公司。
AltDescription 供應(yīng)商名稱替代名稱，用在報表或是其它單據(jù)引用中。比如采購單中的供應(yīng)商是用微軟，還是用代用名稱Microsoft，由參數(shù)(是否用代用名稱)控制。
17 文件類表增加MD5 Hash字段
比如產(chǎn)品數(shù)據(jù)管理系統(tǒng)要讀取圖紙，單據(jù)功能中增加的附件文件，這類涉及文件讀寫引用的地方，考慮存放文件的MD5哈希值。文件的MD5相當于文件的 唯一識別身份，在網(wǎng)上下載文件時，網(wǎng)站常常會放出文件的MD5值，以方便對比核對。當下載到本機的文件的MD5值與網(wǎng)站上給出的值不一致時，有可能這個文 件被第三方程序修改過，不可信任。
18 數(shù)據(jù)表的主鍵用字符串而不是數(shù)字
比如銷售單中的貨幣字段，是存放貨幣表的貨幣字符串值RMB/HKD/USD，還是存放貨幣表的數(shù)字鍵，1/2/3。
存放前者對于報表制作相對容易，但是修改起來相對麻煩。存放后者對修改數(shù)據(jù)容易，但對報表類或查詢類操作都需要增加一個左右連接來看數(shù)字代表的貨幣。金蝶使用的是后者，它的BOS系統(tǒng)也不允許數(shù)據(jù)表之間有直接的關(guān)聯(lián)，而是間接通過Id值來關(guān)聯(lián)表。
在我看到的系統(tǒng)中，只有一個會計期間功能(財年Fiscal Year)用到數(shù)字值作主鍵，其余的單據(jù)全部是字符串做主鍵。
19 使用約定俗成的簡寫
模塊Module 簡寫
|
簡寫 |
全名 |
|
SL |
Sales 銷售 |
|
PU |
Purchasing 采購 |
|
IC |
Inventory 倉庫 |
|
AR |
Account Receivable 應(yīng)收 |
|
AP |
Account Payable 應(yīng)付 |
|
GL |
General Ledger 總帳 |
|
PR |
Production 生產(chǎn) |
名稱Name 簡寫
|
簡寫 |
全名 |
|
Uom |
Unit of Measure 單位 |
|
Ccy |
Currency 貨幣 |
|
Amt |
Amount 金額 |
|
Qty |
Quantity 數(shù)量 |
|
Qty Per |
Quantity Per 用量 |
|
Std Output |
Standard Output 標準產(chǎn)量 |
|
ETA |
Estimated Time of Arrival 預(yù)定到達時間 |
|
ETD |
Estimated Time of Departure 預(yù)定出發(fā)時間 |
|
COD |
Cash On Delivery 貨到付款 |
|
SO |
Sales Order 銷售單 |
|
PO |
Purchase Order 采購單 |
20 庫存單據(jù)數(shù)量狀態(tài)
Qty On Hand 在手量
Qty Available 可用量
Qty On Inspect 在驗數(shù)量
Qty On Commited 提交數(shù)量
Qty Reserved 預(yù)留數(shù)量
以上每個字段都有標準和行業(yè)約定的含義，不可隨意修改取數(shù)方法。
文章標題：解析大型.NET ERP系統(tǒng) 20條數(shù)據(jù)庫設(shè)計規(guī)范
路徑分享：http://www.dlmjj.cn/article/djjpdpe.html
