数据表表设计规范-心得总结

前言

数据库表的设计规范是很重要的，因为如果后期改表的话，会牵涉一大推代码的变动，重构起来十分麻烦，良好的设计习惯，可以使工作事半功倍，且避免很多的坑。

 

1. 表名称不应该取得太长（一般不超过三个英文单词，不推荐使用中文拼音，总的长度不要超过30个字符）。

2. 不使用tab或tb作为表前缀（本来就是一个表，为什么还要说明）。

3. 一些作为多对多连接的表，可以使用两个表的前缀作为表名：如：用户登录表user_login，用户分组表user_group_Info，这两个表建立多对多关系的表名为：user_group_relation（关系统一用relation）。

4. 当系统中有一些少量的，重复出现的值时，使用字典表来节约存储空间和优化查询。如地区、系统中用户类型的代号等。这类值不会在程序的运行期变化，但是需要存储在数据库中。一般数据库中，都有一个数据字典表，用来保存系统所用到的基础数据，大型的字段表如省份城市区域的字典表。(sys_data_dictionary，sys_city_dictionary)

5. 在命名表时，用单数形式表示名称。例如，使用 employee，而不是 employees。

6. 数据库中应建立这样一个表，就是数据库本身的字段信息，表的说明，也就是数据库设计文档的一个表，方便查询使用，有什么不明的可以直接从数据库查询，数据库文档丢失，注释丢失，都可以重新起作用。

7. 每个表都应该有一个主键，这个主键最好是数字，有很多表的主键用32位字符编码，这样做的目的更多的是从安全考虑的。因为字符多时索引时效率低。

8. 操作日志表，登录日志表，这是数据库中必备的两个表，这个记录也需要做进一步的保存。这个有两种情形，一是具体到单个字段的操作日志，二是整个表的操作日志。常见的几个表具体说明：操作日志表sys_operate_log、登录日志表sys_login_log.

9. 【强制】表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint（ 1表示是，0表示否）。 
说明：任何字段如果为非负数，必须是unsigned。 
正例：表达逻辑删除的字段名is_deleted，1表示删除，0表示未删除。

10. 【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。 
说明：MySQL在Windows下不区分大小写，但在Linux下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。 
正例：aliyun_admin，rdc_config，level3_name 
反例：AliyunAdmin，rdcConfig，level_3_name

11. 【强制】主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名。 
说明：pk_ 即primary key；uk_ 即 unique key；idx_ 即index的简称。

12. 【强制】小数类型为decimal，禁止使用float和double。 
说明：float和double在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小数分开存储。

13. 【强制】如果存储的字符串长度几乎相等，使用char定长字符串类型。

14. 【强制】varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

15. 【强制】表必备三字段：id, gmt_create, gmt_modified。 
说明：其中id必为主键，类型为unsigned bigint、单表时自增、步长为1。gmt_create, gmt_modified的类型均为datetime类型，前者现在时表示主动创建，后者过去分词表示被动更新。

16. 【推荐】表的命名最好是加上“业务名称_表的作用”。 
正例：alipay_task / force_project / trade_config

17. 【推荐】库名与应用名称尽量一致。

18. 【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。

19. 【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：
1）不是频繁修改的字段。
2）不是varchar超长字段，更不能是text字段。 
正例：商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。

20. 【推荐】单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。 
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

21. 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。 
正例：如下表，其中无符号值可以避免误存负数，且扩大了表示范围。
对象    年龄区间    类型    字节
人    150岁之内    unsigned tinyint    1
龟    数百岁    unsigned smallint    2
恐龙化石    数千万岁    unsigned int    4
太阳    约50亿年    unsigned bigint    8

 

 

参考：阿里云建表规约