数据库设计规范-通用版

　

数据库设计规范

一、命名规范

1、总命名规范

1、不得使用数据库保留关键字，以及php/java等常用语言的保留关键字，或者可能成为关键字的单词作为完整命名。（对于一些疑似关键字的单词，可以在后面加一个下划线来避免，例如“key_”）。【附：mysql保留关键字列表：HTTPs://dev.mysql.com/doc/refman/5.7/en/keywords.HTML】

2、如无特殊说明，名称必须用英文字母开头，采用有特征含义的单词或缩写，单词中间用“_”分割，且只能由英文字母、数字和下划线组成，不能用双引号包含。

3、除数据库名称长度为1至8个字符，其余（包括表、字段、索引等）不超过30个字符，Database link名称也不要超过30个字符。（30并不是凭空想象出来的，而是参考了oracle的限制）

2、表名

（建议以2-3字项目名称为前缀开头），紧跟2-5个字符（英文字母或数字，但不得全是数字）的模块名（必须），最后跟上当前表的含义的单词（1-3个单词，用下划线连接），例如：SQ_SYS_CAR，SQ是项目名称的缩写，SYS是模块名称的缩写，CAR表示当前表的具体含义。 

特别强调：项目名称和模块名用简写（建议长度为2-5个字符），而表含义的名称，可简写、也可以不简写，但是都不能超过3个单词，例如下面两个反面例子：

1. ABF_SUPERVISION_USER，问题：模块名称似乎比较长，建议控制在2-5个字符，缩写为 ABF_SUPV_USER; 

2. ABF_SYS_USER_MANAGE_ORG_ROLE，问题：除去前缀ABF_SYS_，表含义（USER_MANAGE_ORG_ROLE）超过了3个单词。

3、字段名

a) 表的字段数不超过50个。 

b) 类型：各表之间相同含义的字段，类型定义要完全相同（包括精度、默认值等）； 

c) 命名：

1. 字段名无单词数的限制，但是名字的字符长度应该符合上面的“总命名规范”。

2. 字段命名及其注释，要做到清楚、无歧义。 

举两个实际的例子，

1）有些数据可能会存在多种完全不同类型的状态，例如，例如汽车数据，有启停状态，参保状态，维修状态，年审状态……总之，在有些数据表中，有许多的状态字段。如果没写清楚，例如有个字段 “STATUS tinyint NULL; -- 状态”，这是让人很疑惑的，状态？到底是什么状态？状态的取值有哪些？——如果改成“DELETE_STATUS tinyint default 0; -- 删除状态（1：已删除，默认为0：未删除）”，这样的命名和注释，让人一目了然。 

2）再比如“belong_dept -- 所属部门”，这也有歧义，因为部门除了数据唯一ID之外，还有一个部门编码CODE也是唯一的。那到底是存 部门ID，还是 部门编码 CODE？实际情况是，有的人认为存ID，有的却认为存编码。所以，在命名上就应该做到无歧义，如果要存ID，就应该命名为“belong_dept_id -- 所属部门ID”，如果要存部门编码，就应该为“belong_dept_code -- 所属部门编码”。

3. 同一个字段名在一个数据库中只能代表一个意思。比如phone在一个表中代表“座机号码”的意思，在另外一个表中就不能代表其他意思（比如手机名称、品牌等，否则在A表中phone存的是座机号码，在B表中存的是手机品牌，那就混乱了）

4. 反之，代表同一个意思的字段，在各个表中都用相同单词表示，例如电话号码字段，在A表中叫telephone，在B表中叫phone，在C表中叫mobile，这样就很混乱。

特殊情况：如果有多个字段时，可以加前缀或后缀区分，代表复数含义时，单词后可以加s，例如user_ids。比如“电话号码”，在A表字段中名称为tel，在B表中也只能叫做tel（但是如果B表中有多种电话号码，可以加后缀，例如 保卫部 tel_bw，科技部 tel_kj，综合部 tel_zh）。

5. 对于多个表关联的外键字段，例如 create_user_id，关联的是 user表里面 id 字段，建议的命名规则是 “关联表名（无需前缀）+"_"+关联字段名”，也就是说，单词是根据表和字段名而来的，不是凭空随便想出来的。例如这个 create_user_id，create_是前缀，user_代表 abf_sys_user表，id代表abf_sys_user表的id字段。再比如create_user_dept_code，user_是abf_sys_user表的后缀，dept_是abf_sys_dept表的后缀，code是abf_sys_dept表的code_字段。

综合第4、5点，再举一例：有一个部门表abf_sys_dept，里面有一个部门编码字段code_，如果有一个表需要保存 "责任部门编号" 和 "创建人所属部门编号"，按照规范，这两个字段可以命名为：resp_dept_code 和 create_user_dept_code。

4、主键名

前缀为PK_。以PK_+表名+主键字段名构成。如果复合主键的构成字段较多，则只包含第一个字段。表名可以去掉前缀。例如PK_SYS_CAR_ID。

5、外键名

前缀为FK_。以FK_+ 外键表名 + 主键表名 + 外键字段名构成。表名可以去掉前缀。例如FK_SYS_USR_SYS_CAR_ID。

6、普通索引

前缀为IDX_。以IDX_+表名+索引字段名构成。如果复合索引的构成字段较多，则只包含第一个字段，并添加序号。表名可以去掉前缀。例如IDX_SYS_CAR_DIN。

7、主键索引

前缀为IDX_PK_。以IDX_PK_+表名+索引字段名构成。表名可以去掉前缀。例如IDX_PK_SYS_CAR_ID。

8、唯一索引

前缀为IDX_UK_。以IDX_UK_+表名+索引字段名构成。表名可以去掉前缀。例如IDX_UK_SYS_CAR_DIN。

9、外键索引

前缀为IDX_FK_。以IDX_FK_+表名+外键字段名构成。表名可以去掉前缀。例如IDX_FK_SYS_CAR_ID。

10、Oracle序列

前缀为SEQ_。以SEQ_+“序列业务名称”构成。如果“序列业务名称”就是某个表名，则使用表的全名，不可去掉前缀。例如SEQ_SQ_SYS_CAR。

二、表设计规范

1. 采用UTF8字符集。

2. 对于数据量可能很大的表（超过2000万），采用分库/分表/分区表，横向拆分控制单表容量。

3. 必须为表、字段等添加注释。

4. 遵守数据的设计规范3NF 规定。

1. 表内的每一个记录都只能被表达一次。

2. 表内的每一个记录都应该被唯一的标识（有唯一键）。

3. 表内不应该存储依赖于其他键的非键信息。

1. 反范式化冗余字段使用规范 考虑具体使用场景，当SQL关连查询比较频繁，或涉及到4张以上表时可考虑采用冗余字段。

2. 必须设置唯一主键，尽量使用自增id作为主键。

3. 建议主键为数字类型，且为递增顺序，主键不表示任何业务含义，严禁数据量大的表使用UUID/MD5作为主键。

4. 不使用数据库外键，由程序保证。

5. MySQL：

1. 使用InnoDB存储引擎。

2. 数据库和表字符集类型统一（utf8mb4 -- UTF-8 unicode），排序规则统一（utf8mb4_unicode_ci）；建表语句中强制指定字符集；

3. 自增字段类型必须是整型，使用 BIGINT类型。并且自增字段必须是主键或者是主键的一部分。

三、字段设计规范

1、凡是可能被索引的字段，必须定义为NOT NULL，可以设置default值；

2、非负值的数字统一使用unsigned（无符号）类型存储 ？？

2、大对象字段

1. 通常情况下，禁止使用LOB类型保存大文本、图片和文件，建议使用其他方式存储（例如文件系统，数据库只保存其地址信息）。

2. MySQL：尽量不要使用TEXT数据类型，mysql的varchar类型支持65535字节，满足大多数场景，仅当字符数特别大时，才考虑text类型；

附——大对象字段处理方法：

1. 将大对象字段从主表中拆分出来单独存放，与原表主键单独存储在另外一个表里；

2. 如果是Oracle 12g之前的版本，VARCHAR2最多支持4000，如果文本内容只是偶尔可能超过4000，但是不会超过8000，那么可以用两个VARCHAR2字段来存储，使用的时候将这两个字段拼接起来就行了。

3. 如果有方便的文件系统，可以将大文本或附件，保存在文件系统中，数据库中只保存其位置和路径信息即可。

3、禁止使用enum，对于boolean类型或者表示简单状态的字段，MySQL用tinyint，Oracle用NUMBER(1)

1. 建议字段not null，根据业务要求来设置默认值（例如默认为0）。

2. 对于boolean类型，以1代表是（true）， 0 代表否（false）。

3. 对于状态类型，注释中应该注明每一种状态的含义，例如“0：编辑中，1：审核中，2：已完成”。

4、数字、小数类型

1. 对于数字、小数类型，不得使用VACHAR等字符串类型来保存，应该使用相应精度的数字、小数类型。

2. 尽量确保数值型列都有默认值

3. 对于Oracle，确定好Number的精度。

4. 对于MySQL，选好数字类型：TINYINT>SMALLINT>mediumint>INT>BIGINT>DECIMAL(存储空间逐渐变大,而性能却逐渐变小)，超过tinyint（256）但不超过65536的使用smallint；当该字段超过42亿时，才使用bigint；

5. 使用DECIMAL 代替 FLOAT 和 DOUBLE 存储精确浮点数？？why？

5、时间类型标准

对于Oracle，有两种时间类型：DATE和TIMESTAMP，DATE的精度只保存到秒，例如“2013-11-02 11:16:36”，而TIMESTAMP精度更高可以保存小数秒，例如“2013-11-03 11:16:36.000000” 。有时候，DATE只保存到秒，不足够区别出两个事件哪个先发生，这时建议使用TIMESTAMP类型。

MySQL：存储年使用year类型，存储日期使用date类型，使用精确时间戳（精确到秒）尽量使用timestamp类型，因为timestamp使用4字节，datetime使用8字节，它们的区别：TIMESTAMP值不能早于1970或晚于2037（'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC）。

5、必须使用int unsigned存储ipV4；

6、一些常见字段的命名统一

为了规范命名，并结合一般命名习惯，指定如下几个字段定义（以Oracle为例）：

ID 编号 NUMBER(22)（integer）

Create_By 创建人 NUMBER(22)（Integer）

Create_Time 创建时间 TIMESTAMP --默认为系统当前时间

Update_By 修改人 NUMBER(22)（Integer）

Update_Time 修改时间 TIMESTAMP --默认为系统当前时间

其他参考命名：

Code_ 编码 VARCHAR2(30)

Level_ 层级 NUMBER(1或2)

Delete_Status 删除标志 NUMBER(1) --1：表示已经删除，默认为0：表示未删除

Description_ 描述或备注 VARCHAR2（200）

四、索引规范

复合索引的字段数不能超过5个。

单表的索引数量尽量控制在5个以内。

联合索引的字段排列顺序以去重后字段的数值的个数大小排序先后顺序。比如表mk_task有id,name，id有50000个独立值，name有5000个独立值，那么，顺序是id在name前面，建立的索引是idx_id_name。

order by、distinct、group by后的字段尽量建立索引。

update、delete的where尽量使用有索引的字段或主键。

超过20字节的varchar字段建议用前缀索引，禁止对字符串长度超过50个字符的列创建索引。

不建议在低基数列上创建索引，例如“性别”列；

合理创建联合索引（避免冗余），(a,b,c) 相当于（a）、（a、b）、（a、b、c）。

长文本类型字段（例如Text）不能使用索引。

五、其他

1、主键ID

建议使用分布式全局唯一递增ID，比如类snowflake算法，很多大公司都在用，有许多成熟的案例，而且百度、腾讯、美团都把自己的ID生成工具开源了。

禁止使用存储过程、视图、事件、触发器、数据库自带的分区表。

临时库、表名必须以”tmp_日期”为后缀，如当日创建多个，则在日期后增加数字后缀；

备份库、表必须以”bak_日期”为后缀，如当日创建多个，则在日期后增加数字后缀；

相关阅读

数据库设计之概念结构设计---------E-R图详解(流程和

0、试述采用E-R方法进行数据库概念设计的过程。 答：采用E-R方法进行数据库概念设计，可以分成3步进行：首先设计局部E-R模式，然后把各局

Oracle数据库(八)——游标

游标——数据的缓存区 什么是游标 游标的使用可以让用户想操作数组一样操作查询出来的数据集，实际上，它提供了一种从集合性质的结果

MySQL数据库审计系统

数据库审计 数据库审计（简称DBAudit）能够实时记录网络上的数据库活动，对数据库操作进行细粒度审计的合规性管理，对数据库遭受到的风

[置顶] 大数据的验证和插入数据库

最近在做项目的时候涉及到大数据的校验和插入。问题描述：通过Excel导入客户信息表至数据库中指定表。刚看到该问题，自然而然就是想

常用数据库建模工具

收藏：http://www.oschina.net/project/tag/83/db-modelIntellij下mybatis插件 MyBatisCodeHelper [国产]MyBatisCodeHelper 是 I