数据库设计规范-通用版

一、命名规范

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_日期”为后缀，如当日创建多个，则在日期后增加数字后缀；