数据库设计规范

最新推荐文章于 2024-07-01 23:02:25 发布

liang墨竹

最新推荐文章于 2024-07-01 23:02:25 发布

阅读量511

点赞数

文章标签：数据库设计规范 java

原文链接：https://zhuanlan.zhihu.com/p/67125499

版权

文章出处：主数据中心数据库设计规范 - 知乎用于收藏学习

一、数据库设计原则

1、表设计原则

（1）规范化与反规范化

规范化的优点是减少了数据冗余，节约了存储空间，减少相应逻辑和物理的I/O次数，同时加快了增、删、改的速度。出于性能和方便管理的考虑，原则上表设计应满足第三范式，有时为了提高某些查询或应用的性能而可以破坏规范规则，即反规范化。

（2）数据表分类

数据表按照不同业务逻辑，分为不同的类别。主要有以下类别：

基本数据表：描述业务实体的基本信息。例如，组织机构表、人员信息表、承运商表等。

标准编码表：描述属性的列表值。例如，职称、民族、状态等。

业务数据表：记录业务发生的过程和结果。例如，订单表、订单轨迹表等。

系统信息表：存放与系统操作、业务控制有关的参数。例如，用户信息、权限、用户配置信息等。

统计数据表：存放业务数据统计值。例如，配送员薪资表、站点运力表等。

临时处理表：存放业务处理过程中的中间结果。

其他类型表：存放应用层的日志、消息记录等。

（3）统一选择表字符集为utf8，表名必须有详细描述（COMMENT='详细表情况说明'）。

（4）一般情况可以选择MyISAM存储引擎，如果需要事务支持必须使用InnoDB存储引擎。

2、字段设计原则

（1）使用能正确存储和表示数据的最小类型，不确定需要什么数据类型时，则选择不会超出范围的最小类型。

（2）选择更简单的数据类型。例如，比较整数的代价小于比较字符，因为字符集和排序规则使字符比较更复杂。

（3）尽可能把字段定义为 NOT NULL。对于字段能否NULL，应该在SQL建表脚本中明确指明，不应使用缺省。

（4）一个表中的字段不要太多，理论上不要超过80个。

（5）数据库中所有布尔型中数值0表示为假；数值1表示为真。

（6）当字段定义为字符串类型时使用VARCHAR而不用CHAR,尤其变长字段。手机号必须使用VARCHAR(20)来存储，其中长度数字代表字符个数不是字节数。

（7）字段尽可能有默认值，字符型的默认值为一个空字符值串，数字型的默认值为数值0。

（8）字段要有描述（COMMENT说明清楚字段含义）。

举例：

3、键设计原则

（1）为关联字段创建外键。

（2）所有的键都必须唯一。

（3）尽可能避免使用复合键。

（4）外键总是关联唯一的键字段。

（5）尽可能使用系统生成的主键。

（6）可选键有时可做主键。

（7）一个表中组合主键的字段个数尽可能少。

4、索引设计原则

（1）如果列出现在表达式或函数中，不会使用该列上的索引。

（2）要索引外键。

（3）对于索引选择性高的列使用B-Tree索引。

（4）HASH索引只适用于相等比较。

（5）不要索引大型字段（有很多字符的字段）。

（6）不要索引常用的小型表。

（7）单表索引建议控制在5个以内，字段数控制在5个以内。

5、大类型字段设计原则

如无特别需要，避免使用大字段。对于二进制多媒体数据，流水队列数据(如日志)，超大文本数据不要放在数据库字段中。大文件和照片存储在文件系统，数据库里存URI。

二、完整性设计

采用数据库系统实现数据的完整性。这不但包括通过标准化实现的完整性而且还包括数据的功能性。

1、主键约束

每个表都要求有主键，主键字段或组合字段必须满足非空属性和唯一性要求。

2、外键约束

（1）对于关联两个表的字段，一般应该分别建立主键、外键。实际是否建立外键，根据对数据完整性的要求决定。

（2）根据需要适当设置父表数据修改时对子表的影响：

父表中删除数据：级联删除；受限删除；置空值。

父表中插入数据：受限插入；递归插入。

父表中更新数据：级联更新；受限更新；置空值。

3．NULL值

由于NULL值在参加任何运算时，结果均为NULL，所以必须利用IFNULL()函数把可能为NULL值得字段或变量转换为非NULL的默认值。

4．CHECK条件

对于字段有检查性约束，要求指定CHECK规则。

5．触发器

触发器是一种特殊的存储过程，通过对表的DML操作而触发执行，是为确保数据的完整性和一致性不被破坏而创建，实现数据的完整约束。选择触发器的BEFORE或AFTER事务属性的时候，对表操作的事务属性必须与应用程序事务属性保持一致，以避免死锁发生。在大量修改数据时，尽量避免使用触发器。

6．视图

为了在数据库和应用程序之间提供另一层抽象，可以为应用程序建立专门的视图而不必非要应用程序直接访问表。这样做还为处理数据库变更时提供了更多的自由。视图是虚拟的数据库表，在使用时要遵循以下原则：

（1）为简化查询，将复杂的检索或子查询通过视图实现。

（2）提高数据的安全性，只将需要查看的数据信息显示给权限有限的人员。

（3）视图中如果嵌套使用视图，级数不要超过3级。

（4）由于视图中只能固定条件或没有条件，所以对于数据量较大或随时间的推移逐渐增多的表，不宜使用视图，可以采用实体化视图代替。

（5）除特殊需要，避免类似SELECT * FROM [TableName] 而没有检索条件的视图。

（6）视图中尽量避免出现数据排序的SQL语句。

三、命名规范

1、总则

（1）所有命名采用26个英文大小写字母和0－9这十个自然数，加上下划线“_”组成。不能出现其他字符（注释除外）。

（2）长度不超过30个字符。

（3）实际名字尽量描述实体的内容，由英文单词、单词组合或单词缩写组成，不以数字和“_”开头。

（4）命名中禁止使用SQL关键字。

（5）对象名尽量短。

2、数据库命名规范

（1）采用26个英文字母(区分大小写，原则全小写，并且使用有实体含义的英文单词，禁止拼音英文混用)和0-9的自然数(经常不需要)加上下划线“_”组成。

（2）命名简洁明确(长度不能超过30个字符)。例如：user, stat, log, 也可以cod_user, cod_stat, cod_log给数据库加个前缀。

（3）除非是备份数据库可以加0-9的自然数：biz_logistics_1707。

（4）数据库和表名应尽可能和所服务的业务模块名一致。

3、数据库表名命名规范

（1）采用26个英文字母(区分大小写, 原则全小写，并且使用有实体含义的英文单词，禁止拼音英文混用)和0-9的自然数(经常不需要)加上下划线“_”组成。

（2）命名简洁明确,多个单词用下划线“_”分隔。例如：user_login, user_profile, user_detail, user_role, user_role_relation,user_role_right, user_role_right_relation表前缀'user_'可以有效的把相同关系的表显示在一起。

（3）各类型表前缀说明。

表以单数形式名词或名词短语命名。如果表名仅有一个单词，那么建议不使用缩写，而是用完整的单词。

数据表 biz_<系统标识>_<表标识>

编码表 cod_<系统标识>_<表标识>

系统表 sys_<系统标识>_<表标识>

统计表 sta_<系统标识>_<表标识>

临时表 tmp_<系统标识>_<表标识>

日志表 log_<系统标识>_<表标识>

4、数据库表字段名命名规范

（1）采用26个英文字母(区分大小写, 原则全小写，并且使用有实体含义的英文单词，禁止拼音英文混用)和0-9的自然数(经常不需要)加上下划线“_”组成，命名简洁明确,多个单词用下划线“_”分隔。例如：user_login表字段 user_id, user_name, pass_word, email, status, mobile, add_time。

（2）每个表中必须有自增主键,update_time(默认系统时间)。

（3）表与表之间的相关联字段名称要求尽可能的相同。

（4）字段名不能与表名相同。