数据库设计规范

标题：数据库设计规范

版本：V2.0

修订文档历史记录：
---日期--|---版本--|--说明----
2003.6.5   V1.0      文档初搞
2003.6.17  V2.0      对1.0 的内容重新整理

                            数据库设计规范
1  目的

   规范数据库设计。

2  概述

   从数据库的设计原则  设计文档几方面论述数据库设计的规范思想及命名规则。

3  数据库应用结构
   根据对一般业务系统的分析，将数据库和程序系统统一进行整体描述，展示数据库的表之间以及与程序模块间的关系。

3.1 数据表和程序模块的分类
   根据“处理特点”，将数据表和程序模块进行分类如下：
   数据表分类：业务数据表、基本编码表、辅助编码表、系统信息表、累计数据表、结算数据表、决策数据表。
   程序模块分类：初始化、业务处理、完整性检测与修正、结算处理、统计处理。

3.1.1 数据表分类说明   
   业务数据表：记录业务发生的过程和结果。如，合同、出仓单、申请单、凭证。
   基本编码表：描述业务实体的基本信息和编码。如，产品、客户、供应商、雇员。
   辅助编码表：描述属性的列表值。如，合同类型、职称、民族、付款方式。
   系统信息表：存放与系统操作、业务控制有关的参数。如，用户信息、权限、用户配置信息、成本核算方式。
   累计数据表：存放业务的当前值和累计值。如，当前库存、当前存款、累计销售、累计支出、应收账款。
   结算数据表：存放各个时期末的结存数。如，月末库存、月末银行存款、应收账款月结。
   决策数据表：存放各个时期内发生的统计值。如，月销售统计、月回款统计、出入库统计。

3.1.2 程序模块分类说明
   初始化：系统运行前对系统进行数据的初始化。如，库存初始化。
   业务处理：业务过程的控制和结果记录。如，合同录入、费用审批、出入库。
   完整性检测与修正：对累计数据表进行检查并自动修正。如对当前库存、当前存款、累计销售的检查和重新计算。
   结算处理：计算并记录各个时期末的结存数。库存月结、应收账款月结。
   统计处理：计算并记录各个时期内发生的统计数。如，统计月销售、统计月回款、统计出入库。

 3.2 数据表间的关系
   业务数据表<-->基本编码表 主-外键关系。如，合同表<-->客户编码表;
   业务数据表<-->辅助编码表 主-外键关系。如，合同表<-->付款方式;
   业务数据表、累计数据表、结算数据表：累计数据表=结算数据表(上期末) + 业务数据表(本期内发生)。如当前库存=上月末库存数+(本月入库数-本月出库数);
   决策数据表<-->业务数据表 决策数据表的数据是由业务数据表中数据导出(统计)的；

 3.3 数据表与程序模块间的关系
  由一个例子(仓库管理)来说明数据表与程序模块之间的关系：
   . 系统使用前，由初始化模块对库存数(累计数据表)和上月末库存数(结存数据表)进行初始化；
   . 当有入库业务发生时，由入库模块(业务处理)将入库单录入并保存到入库单明细帐(业务数据表)中，同时将入库数累加到库存数(累计数据表)中；
   . 定期或不定期，库存数核算模块(检查完整性检测与修正)根据上月末的库存数(结存数据表)、本月已发生数(业务数据表)检查当前的库存数(累计数据表)是否符合，不符合则给出提示，可手工或自动进行更正(当前库存数=上月末库存数+本月入库数-本月出库数)；
   . 每月初，进行上月的月结处理。月结模块(结算处理)根据上月初的库存数(结存数据表)、上月发生数(业务数据表)计算出上月末的库存数(累计数据表)。公式为：上月末库存数=上月初库存数+上月入库数-上月出库数；
   . 每个月月结后，库存业务月统计模块(统计处理)统计上月的各种库存商品的入库和出库数，便于查询和生成报表，也作为决策支持的数据基础。

  3.4 数据表命名时对数据表分类的考虑
 . 业务数据表：t_d_<系统标识>_<表标识>。如销售系统的合同表 t_d_SH_Contract或 t_d_SH_合同；
 . 基本编码表：t_b_[<系统标识>]_<表标识>。如客户编码表t_b_Customer 或 t_b_客户；
 . 辅助编码表：t_a_[<系统标识>]_<表标识>。如合同类别t_a_ContType 或 t_a_合同类别；
 . 系统信息表：t_s_[<系统标识>]_<表标识>。如用户表t_s_User 或 t_s_用户；
 . 累计数据表：t_t_<系统标识>_<表标识>。如当前库存表t_t_SO_Stock 或 t_t_SO_库存；
 . 结算数据表：t_c_<系统标识>_<表标识>。如库存月结表t_c_SO_StockMonth 或t_c_SO_库存月结；
 . 决策数据表：t_w_<系统标识>_<表标识>。如月销售统计表t_w_SH_SellMonth 或t_w_SH_月销售统计；

 注：[]内的内容表示可选。如“t_s_[<系统标识>]_<表标识>”表示t_s_SH_User 和t_s_User 都是符合规则的。

4  数据库结构原则 

   规定除数据库设计所遵循的范式外的一些适用原则，在遵循数据库设计范式的基础上，合理地划分表，添加状态和控制字段等。
   4.1 辅助编码表
   为了使辅助编码表能起到预期的效能，又不因过多的辅助编码表难以管理，故对辅助编码表的使用作如下规定：
   1. 当某辅助编码表的编码允许用户添加时，应设计成“独立”的数据表；否则，将不允许用户添加编码的各辅助编码表合并成一个“通用”的辅助编码表。
   2. “独立”的辅助编码表与主表的列采用主-外约束保证列数据完整性。
   3. “通用”的辅助编码表与各主表间没有约束关系，主表列的数据完整性由列说明的“域”来保证。
   4. “通用”的辅助编码表除编码和名称列外，还有一个标识列，用来标识合并前的各码表，该标识列+编码列作为该表的主键。
   5. 对于“独立”的辅助编码表，用户只可添加新的编码和改变名称，并且不能改变一个编码所代表的意义；对于“通用”的辅助编码表，原则上不允许用户修改，或只有限地允许修改名称。（Re:对于相对固定不变的辅助编码表，或者说不允许用户修改的辅助编码表，这种表相对很小，为了减少表的个数，将这些编码表合并到一起，就称为“通用”，为了区分包含的各编码表，就需要一个标识列来区分，为了保证每个子表编码的相对独立，其主键应为标识列+编码列。）
   
 4.2 基本编码表
 1. 基本编码表可以有如下的标识列：内编码、外编码、助记码、简称、全称。内编码（唯一编码）作为主键有程序自动生成，用户不可见；外编码（唯一编码）由用户按某种规则自行定义，用户可见；助记码为拼音缩，方便录入，不唯一，重码时由列表选择；简称用于列表显示和报表，以便缩短行宽。以上的列在实现时可视情况和习惯加以删减。

 2. 当码表的列较多且也行较多时，可将上述的标识列和常用的信息存于一个表，将其它的信息另表存储。

4.3 业务数据表

 1. 设有‘录入人’和‘录入日期’列，由系统自动记录。
 2. 记录单据的表中设置“自动单据号”，由两个字符开始以区分单据类型，后跟一数字序列表示序号。‘自动单据号’由系统自动生成，作为主表的主键，不允许用户修改。当有对应的纸质单据时，设置“单据号”用于记录纸质单据的单据号。
 3. 明细表中设有行序号，自动记录行的录入顺序。
 4. 设置“存档标记”列，用于抽取数据到决策数据库时的更新标记。插入新行或修改已有行时设置该标记；数据抽取后清除该标记。
 5. 对于用于查询过滤条件的列，不可为空，以免行“丢失”。
 6. 对于数值列，不可为空，“0”作为默认值。
 7. 对于必要的“冗余”列，如客户名称，应有相应的程序保持各“冗余”列的同一性，以免出现异议。
 8. 设置“过程状态”列和“记录状态”列。过程状态列用于记录如创建、审核、记账、冲红等状态；记录状态用于记录如有效、删除等状态。（Re: 此中的删除代表逻辑删除，使我们通常所说的“作废”，这与编程习惯有关。设置两个状态列是为了记录被作废或删除后，可以保留“过程状态”，用以识别记录被处理的过程。）
   
5  数据库命名原则
   5.1 表名
   . 业务数据表：t_d_<系统标识>_<表标识>。
   . 基本编码表：t_b_[<系统标识>]_<表标识>。
   . 辅助编码表：t_a_[<系统标识>]_<表标识>。
   . 系统信息表：t_s_[<系统标识>]_<表标识>。
   . 累计数据表：t_t_<系统标识>_<表标识>。
   . 结算数据表：t_c_<系统标识>_<表标识>。
   . 决策数据表：t_w_<系统标识>_<表标识>。

   5.2 视图
   v_<视图类型>_[<系统标识>]_<视图标识>。视图类型参见《表的分类》。

   5.3 存储过程
   p_[<系统标识>]_<存储过程标识>

   5.4 函数
   f_[<系统标识>]_<函数标识>

   5.5 触发器
   tr_<表名>_<i,u,d的任意组合>  (after)
   ti_<表名>_<i,u,d的任意组合>  (instead)

   5.6 自定义数据类型
   ud_<自定义数据类型标识>_<数据类型>

   5.7 Default
   df_<Default标识>

   5.8 Rule
   ru_<Rule标识>

   5.9 主键
   pk_<表名>_<主键标识>

   5.10 外键
   fk_<表名>_<主表名>_<外键标识>

===========================================================================================

搂主这么热心，我也贡献一点。


一、 概述
为了提高编码的效率和标准化程度，增强代码的可读性，特制定本规范作为公司软件开发人员设计数据库时的开发规范。本规范规定数据库中各种对象的命名规则及代码书写规则。
除非特别说明，本文中的数据库都是指目前的主流关系型商业数据库系统，如Informix，DB2，Oracle，Access等。
二、 命名规范
1、 命名规则说明
对象命名的格式如下：
type_[desc]_name…
其中：
l 下划线(_)： 为名称的一部分，用于提高命名的可读性；
l 类型(type)： 用于标识对象的类别，用正常字体表示；
l 修饰符(desc)： 用于描述对象的某种属性，可选；
l 名称(name)： 此部分由用户用具体的词代替；
l 中括号([])： 表示此部分为可选项目。
l 小数点(.)： 表明此部分长度可变。
2、 数据库设计中涉及的对象及命名规范
以下给出在数据库设计中经常使用到的对象及其命名规范：
2.1、 数据库设备(Database Device)
l 名称
dev_xxx
l 说明
dev：表明这是一个数据库设备名称。
xxx： 表明该设备的用途，如：
       log：  用于事务日志
       dat： 用于数据和索引
l 举例
dev_dat_1
dev_dat_2
dev_log_1
如果该设备是镜像设备，应采用如下命名方式，在设备名称后加“_mir”：
 dev_dat_1_mir      dev_dat_2_mir   dev_log_1_mir 
2.2、 数据库(Database)
l 名称
db_dddd_vvvv
l 说明
db：表明这是一个数据库名称。
dddd： 是一个有意义的描述
vvvv…： 版本或环境描述。如：
dev(开发)
    test(测试)
    prod(产品)
    train(培训)
l 举例
db_travel_dev
db_travel_test
db_operations_prod


2.3、 表(Table)
l 名称
[t][tab]_[desc]_tttt…
l 说明
t或者tab：表明这是一个表名称。
desc： 对表的某种属性的描述，比如对于有多个子系统的开发项目，应在相应的子系统所用的表(table)前加上相应的子系统缩写作为描述符， 在公用表前加描述符pub_；对于小型系统，可省去此项；
tttt： 表的意义描述。
l 举例
t_pub_user
t_book_publisher
tab_pub_user
tab_bank_service
2.4、 字段(Field or Column)
l 名称
f_[type]_tttt…
l 说明
f：表明这是一个字段名称。
type：可选，表明字段类型，字符型为c，整型为i，逻辑型为b，货币类型为m,浮点型为f，日期型为d，时间型为t，二进制为bl；由于各种数据库系统都有自己定义的数据类型，因此此项为可选，不做强制要求；具体要求在具体项目的开发中进行规定。
tttt： 对字段属性的有意义的描述，可以用英语单词、单词缩写、汉语拼音、字段实际含义的拼音缩写等；
l 举例
f_name(姓名)
f_c_name
f_xm(姓名)
f_grp_id(组标识)
2.5、 索引(Index)
l 名称
idx_tttt…
l 说明
idx：表明这是一个索引名称。
tttt： 对索引属性的有意义的描述；
l 举例
idx_usersid
2.6、 视图(View)
l 名称
v_tttt…
l 说明
v：表明这是一个视图名称。
tttt： 对视图属性的有意义的描述；
l 举例
v_special_user
v_cancled_order
2.7、 触发器(Trigger)
l 名称
trg_[d][i[[u]_tttt…
l 说明
trg： 表明是一个触发器
d，i，u：  表明触发器类型(Delete，Insert，Update)定义；注意书写顺序为d、i、u，而不能有任意逆转书写
tttt…： 是一个表(Table)的名称，表明触发器所在的表。
l 举例
trg_iu_air_segment
trg_d_group_client
l 创建触发器的脚本文件命名
trgname.sql
其中trgname为触发器名称，如：
trg_iu_air_segment.sql
2.8、 约束(Constraint)
l 名称
cns_tttt…
l 说明
cns： 表明是个约束
tttt…： 描述；
l 举例
cns_contid
2.9、 存储过程(Stored Procedure)
l 名称
sp_tttt…
l 说明
sp： 表明是个存储过程
tttt…： 有意义的描述；
l 举例
sp_AddUser
l 创建存储过程的脚本文件命名
spname.sql
其中spname为存储过程名称，如：
sp_AddUser.sql


三、 数据库说明书格式
数据库说明书用来说明数据库中各个对象的详细信息，包括数据库表、字段、索引、视图、触发器、约束等。说明书示例如下：
1、 tab_PhysicalCheck(健康评估－体征和检查表)
l 功能描述：存放用户的体检数据。
l 字段说明：
字段名称 字段类型 说明 备注
f_id Integer 唯一标识
f_detid Integer 评估总表标识 连接健康评估总表中的f_hp_id
f_tw integer 体温
f_mb integer 脉搏
f_hx integer 呼吸频率
f_shg integer 身高
f_tzh integer 体重
f_zhfhl single 脂肪含量
f_shsy integer 收缩压
f_shzhy integer 舒张压
f_chtxb char(100) 查体印象－胸部
f_chtfb char(100) 查体印象－腹部


l 索引
索引名称 包含字段 索引类型
idx_hpcid f_id 主键
idx_other f_tw+f_mb 唯一索引


l 视图
l 触发器
l 约束

=============================================================================================

我可以上载文件吗？大家可以看看我们公司的数据库设计规范，相对来说，比楼上的通用一点点。顺便给楼上的规范提几点意见，不要见怪：


1）与业务的东东耦合度太高，有的东西不需要在这里面体现，数据库设计规范就仅是数据库设计规范，与别的东西没有关系，要体现的话在别的文档体现，如数据表与程序模块间的关系等内容；


2）没有版本的概念，也就是数据库内容的设计不是一天定下来的，可能要随着业务和代码的开发，数据库内容也在变，如何体现出来变化，没有涉及？


3）没有团队的概念，有很多东西是需要多人参与的，不同的人利用这份文档的角度不一样，如果有多人参与的话，我不知道象这份涵盖这么多具体的业务概念的东西在哪个阶段、什么样的人能做得出来，如果业务变化的话，根据这份文档设计的系统不知道要改多少地方；在有些内容还定不下来的的时候，其它人能不能利用这份文档开始搭框架和原型，搭了以后又有多少可以利用？数据库设计是很多东西的基础，如果这份东西做的不好话，如果您是项目负责人，改来改去，大家都会跟着您累死。


4）提一些建议：1.现在都是强调专门化和团队协作，考虑什么东西都要象个做系统的思维，自己好用是一方面，如何做到把一个庞大的系统工作细划，每做完一块，它就是一个相对完整的工作，即使它改了以后，也要尽可能影响小，而不是动不动就是整个系统与之相关的东西都要改动一次；2.合作是必要的。也就是说不要让某些文档承载太多的东西，否则很多人都要等米下锅，某些人累得半死，某些人闲死。


关于审计的内容，有几点感触：


（1）是不是大家对管理逻辑与业务逻辑没有区分的概念？审计的东西我理解就是属于管理的逻辑，业务逻辑就是与业务的逻辑，它们之间是相辅相成的，这时候一定有朋友会问，这样进行数据库设计，加上审计表的思路有问题吗？我的回答是：有，而且相当有问题。第一，您能想到审计表，就说明要不是您接触过许多项目用户都提出这个需求，要不就是您比用户想到更多，也就是说至少有一点您是认可的：这部分内容是必要的，除非这个系统是非常小的或者用户需求不变化。如果这样的前提下，也就需要有专门一个的模块（系统或平台）来做这部分工作，把很多工作交给这个专门的部分来完成，因为加审计表是数据库级别的，也可能是某个人或某几个人的做法，变化了以后谁来维护？又去修改审计表？审计表不能满足用户业务需求后，直接改审计表结构？这么下去的话，您做了100个项目或者系统后，天天做维护都做不完，何谈接新项目？应该有一个专门的平台去做管理，而且是有界面的，所有的管理逻辑都通过界面，从建模型开始，把管理的内容模型化，变成可管理的内容，这样才基本上从理论上来说（我们是已经实现了，而且在大量的项目中实用）可以做到管理逻辑与业务逻辑分离，再加上一些如数据与界面分离等思想，系统才有点意思，如果只是一堆代码堆在那里，不考虑维护和升级的成本，以及隐藏在其中的由于没有规划和协作、标准规范的原因而带来的只能由原始开发人员（甚至是高级的一个月拿1万2万的人员）进行维护和修改，一旦他们烦透这种工作和生活方式，一走了之的高额人员成本，您觉得自己做的事情有意义吗？我不是说您做的东西档次低，只是想表达作为一个程序员，我自己做不到，至少我也应该知道哪种境界是我们追求的境界。


（2）是不是大家在开发的时候，都没有一个处理管理逻辑的平台在做支撑？我的意思说如果涉及到角色、资源和权限的时候，如果您需要直接在代码里写上这部分的控制代码或者您即使有一些通用的模块，但是不成体系，也就是说您开发的这个系统与那个系统，这通用的模块也是不通用的，里面有很多代码变化了，只要是这样，就说明管理逻辑在您的心目中地位还不重要，难道您没有感觉您的大量的维护工作中或者您感觉到用户的用户漫延得让您感到项目结束遥遥无期时，其实很多都是管理的东西在不停做变化，并不是您实现不了某业务功能。