大型.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 预留数量

以上每个字段都有标准和行业约定的含义,不可随意修改取数方法。

原文发布于微信公众号 - 我为Net狂(dotNetCrazy)

原文发表时间:2015-10-09

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏用户画像

mysql查询实例

select Sno as 学号,Sname as 姓名 from student;

2662
来自专栏andychai

MySQL模糊查询性能优化

根据模糊查找的业务场景,比对一下上面列出的6种条件,如果你的场景是全都要支持,并且是 大用户量, 接口qps高,海量的数据检索量,那就不要在数据库上做任何挣扎了...

5.3K6
来自专栏架构师之路

或许你不知道的10条SQL技巧

这几天在写索引,想到一些有意思的TIPS,希望大家有收获。 一、一些常见的SQL实践 (1)负向条件查询不能使用索引 select * from order w...

39012
来自专栏峰会SaaS大佬云集

关于经典停车场问题指针输出乱码问题?

#include<stdio.h> #include<stdlib.h> #include<string.h> #include<math.h> struct ...

411
来自专栏数据和云

12C 新特性 | 标量子查询自动转换

优化器是 Oracle 数据库最引人入胜的部件之一,因为它对每一个 SQL 语句的处理都必不可少。优化器为每个 SQL 语句确定最有效的执行计划,这是基于给定的...

3597
来自专栏高爽的专栏

如何实现按距离排序、范围查找

简介 现在几乎所有的O2O应用中都会存在“按范围搜素、离我最近、显示距离”等等基于位置的交互,那这样的功能是怎么实现的呢?本文提供的实现方式,适用于所有数据库。...

3941
来自专栏james大数据架构

你真的会玩SQL吗?三范式、数据完整性

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

2147
来自专栏文渊之博

SQLServer图数据库一些优点

上一篇简要介绍了图数据库的一些基本内容(初识SQL Server2017 图数据库(一)),本篇通过对比关系型一些语法来体现图数据库模式的一些优点,比如查询方...

2156
来自专栏CSDN技术头条

MySQL查询优化之道

查询优化器的任务是发现执行 SQL 查询的最佳方案。大多数查询优化器,要么基于规则、要么基于成本。

1454
来自专栏数据库

关于女神SQLite的疑惑(2)

还是女神SQLite的话题,继续讨论有关她的种种常见疑惑。 1.问:女神SQLite是线程安全的吗? 1.答:SQLite是线程安全的,这点确凿无疑。但我要补充...

2098

扫码关注云+社区

领取腾讯云代金券