数据库设计规范-通用版
2017年09月27日


导读:本文按 “命名规范” “表设计” “字段设计” “索引设计” “其它”  五个部分展开。


1 命名规范

1.1 总命名规范

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

    正例:id,ip,admin,config,level,key_,let_,desc_,string_,public_,switch_

    反例:table,time,then,to,const,val,create,desc 等等

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

    正例:abf_admin,abf_config,abf_level2,key_

    反例:3rd_admin,abf_level_2

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

4、英文名字,要么全小写,要么全大写。(建议全部使用小写的名字)。标准SQL语句中的关键字使用大写,例如下面的SQL格式:

-- 用户与角色对应关系
CREATE TABLE `fast_user_role` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint COMMENT '用户ID',
  `role_id` bigint COMMENT '角色ID',
  PRIMARY KEY (`id`)
) ENGINE=`InnoDB` DEFAULT CHARACTER SET utf8mb4 COMMENT='用户与角色对应关系';

上面的SQL,名字用的是全小写,SQL关键字用的是全大写,符合本规范。

1.2 表名

    以2-4字项目名称为前缀开头(建议,不强制),紧跟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个单词。

1.3 字段名

1.3.1 字段数量和类型

1、表的字段数不超过50个。 

2、类型:各表之间相同含义的字段,类型定义要完全相同(包括精度、默认值等); 

1.3.2 字段命名

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。

1.4 索引及其他

1.4.1 索引命名规范

    索引一般的命名格式为:索引前缀+索引字段名

  1. 如果复合索引的构成字段较多,则可以省略过多的字段名,采用添加序号或者缩写等策略

  2. 普通索引:前缀为IDX_

  3. 唯一索引:前缀为UK_

  4. 自定义的主键索引名:前缀为PK_

    1)针对MySQL,使用默认主键就行了,一般不用特殊设置。

    2)针对Oracle,建主键的时候,建议 指定主键索引名(约束名),

        Oracle未指定主键约束(索引)名时,结果如下:

SQL> create table jack (id int primary key not null,name varchar2(20));
Table created
 
SQL> select table_name,index_name from user_indexes where table_name='JACK';

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
JACK                           SYS_C0011100

       可见,它会默认命名为 “SYS_xxxx”,建议指定约束名,效果如下:

SQL> create table jack (id int ,name varchar2(20),constraint pk_id primary key(id));
Table created

SQL> select table_name,index_name from user_indexes where table_name='JACK';

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
JACK                           PK_ID

1.4.2 Oracle序列

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


2 表设计规范

2.1 采用UTF8字符集。

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

2.3 主键和外键

  1. 必须设置唯一主键。

  2. 建议主键为数字类型,且为递增顺序。

  3. 主键不表示任何业务含义。

  4. 严禁数据量大的表使用UUID/MD5作为主键。

  5. 不使用数据库外键,由程序保证。

2.4 对于数据量可能很大的表(超过2000万),采用分库/分表/分区表,横向拆分控制单表容量。

2.5 通常应遵守数据的设计规范的3NF规定(例外见第6条):

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

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

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

2.6 反范式化冗余字段使用规范:

    考虑具体使用场景,当SQL关连查询比较频繁,或涉及到4张以上表时可考虑采用冗余字段。

2.7 MySQL:

  1. 使用InnoDB存储引擎。

  2. 建表语句中强制指定字符集:数据库和表字符集类型统一(utf8mb4 -- UTF-8 Unicode),排序规则统一(utf8mb4_unicode_ci);

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


3 字段设计规范

3.1 大对象字段

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

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

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

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

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

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

3.2 建议尽量将字段设置为not null,根据业务要求来设置默认值(例如默认为0)

    一个经验:设计表时,可以先假设所有字段为not null,然后再来看,哪些字段可以设置为null,以及哪些字段需要设置默认值。

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

    1、对于boolean类型,以1代表是(true), 0 代表否(false)。

    2、一个字段只表示一个类型的状态(即:不同类型的状态,应分多个字段来表示),注释中应该注明每一种状态的含义,例如“文章审核状态”:“0:未提交审核,1:提交审核中,2:审核通过,3:审核不通过”。

3.3 数字、小数类型

    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?

3.4 时间类型标准

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

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

3.5 凡是可能被索引的字段,必须定义为NOT NULL,可以设置default值

3.6 针对MySQL:

  1. 非负值的数字统一使用unsigned(无符号)类型存储(建议??)

3.7 统一常见字段的命名

    为了规范命名,并结合一般命名习惯,指定如下几个字段定义(以Oracle为例):

  • ID 编号 NUMBER(22)(Integer)

  • Create_User_Id 创建者ID NUMBER(22)(Integer)

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

  • Modify_User_Id 修改者ID NUMBER(22)(Integer)

  • Modify_Time 修改时间 TIMESTAMP --默认为0

其他参考命名:

  • Code_ 编码 VARCHAR2(30)

  • Level_ 层级 NUMBER(1或2)

  • Delete_Status 删除标志 NUMBER(1) --1:表示已经删除,默认为0:表示未删除

  • Description_ 描述或备注 VARCHAR2(200)

3.8 使用int unsigned存储IPV4(将ip转换成整数存储)

    如果用CHAR(15)来存储,则需要15个字节(VARCHAR则需要16个字节)。将IP用无符号整数存储,只需要4个字节,能节省空间。写一个简单的程序就可以将IP和整数互换(参见这个程序),例如PHP中的ip2long函数:

    ip2long('10.52.1.129');  // 171180417

    MySQL甚至有专门的函数INET_ATON (将 IP 地址的字符串形式转换成数字形式),方便直接查询IP段,例如:

SELECT user_id FROM user_ip WHERE ip > INET_ATON('192.168.0.0')
 AND ip < INET_ATON('192.168.255.255')

3.9 建议字段

1、如果某表的相同类型的数据,可以被多个不同用户创建,则建议记录下创建用户的用户名或ID,以及创建时间。

以MySQL为例,字段不能为空,如下:

`create_user_id` bigint NOT NULL COMMENT '创建者ID'
`create_user_name` varchar(比如50) NOT NULL COMMENT '创建者名称'
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'

2、如果某表的同一条数据,可能被多个不同用户更改,则建议记录下更改的用户名或ID,以及更改时间。

以MySQL为例,字段可以为空,如下:

`modify_user_id` bigint COMMENT '修改者ID'
`modify_user_name` varchar(比如50) COMMENT '修改者名称'
`modify_time` datetime NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'


4 索引规范

4.1禁止条例

  1. 不建议在低基数列上创建索引,例如“性别”列;

  2. 长文本类型字段(例如Text)不能使用索引;

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

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

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

4.4 联合索引的字段排列顺序以去重后字段值的个数的大小排序

    比如表mk_task有id,name,id有50000个独立值,name有2000个独立值,那么,顺序是id在name前面,建立的索引是idx_id_name。

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

4.6 优先考虑建立 唯一索引,如果达不到唯一索引要求,才考虑建立普通索引

4.7 建议条例

  • Order by、distinct、group by后的字段尽量建立索引

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



5 其他

5.1 主键ID

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

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

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

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