以MySQL5.7为例,在一个项目中的数据库schema中建表
〇、建库
统一字符集和排序规则
规则
库的默认字符集选择utf8mb4,表、字段默认上级
库的排序规则选择utf8mb4_general_ci,表、字段默认上级
好处
统一排序规则,防止不必要的隐式转换,库级先指定,表级,字段级默认上级即可。
一、命名法
如果是大小写敏感的数据库【MySQL】就用蛇形命名法【小写+下划线】
如果是大小写不敏感的数据库【SQL Server】就用大驼峰式【大小写】
二、望文生义,自说明
百度百科中,望文生义:汉语成语,意思是指不了解某一词句的确切涵义或来源缘由,光从字面上去牵强附会,做出不确切的解释。
规则
在数据库设计中,表名字段名一定要用有意义的名词,即自说明,每一个名词都是由有意义的英文或者通用英文缩写组成。鉴别方式:使用百度搜索字段名中的单词,可以搜索到,则命名没问题。
例子
公司可以使用 corporation 或者缩写corp,不能用gongsi,下图为搜索缩写的结果
数量可以使用 quantity 或者缩写qty,不能用shuliang、numb(糟糕的选择,单词意义是麻木的; 失去知觉的; 迟钝的; 呆滞的)、num。下图为缩写搜索的结果
注意:缩写必须是约定俗成的,是行业通用的,否则宁可字段过长也不要缩写。不规范的缩写会导致表名易用性和可维护性变差。对开发人员和维护人员极其不友好。
好处
使不了解表结构的人,直接看表名、字段名就可以知道表、字段的意义。尽量不查备注,因为查看备注也需要花时间,对开发的编码流畅性干扰很大。
注意:
1、字段名要简短、易于理解、无歧义。
2、虽然已经望文生义、自说明,但是不意味着可以省略备注,每个表和字段还是有必要加上备注的,防止出现歧义。
三、字段统一
规则
意义相同的字段就算在不同表中也要保持字段名相同、保持类型相同。
所以备注要言简意赅,如果不同表出现相同字段,只要全库搜索备注就能找到相同意义的字段,这样就可以维持字段统一
好处
1、依旧是望文生义,当已经习惯于一个字段名,该字段在其他表中出现对开发识别字段意义有帮助
2、当两个表的数据相互传递时,可以使用相同属性名反射实现set、get方法,给开发提供便利
3、类型相同防止字段比较时出现隐式类型转换
四、模块分组
如果系统设计中划分了模块,各模块的表名中必须加了相同的模块简称前缀
例子
字典表模块中的
计量单位表dict_unit
公司表dict_corp
系统模块中的
用户表sys_user
角色表sys_role
好处
相同模块的表在工具中查看时是排列在一起的。方便查找相关表。
五、主键名
主键id不能统一命名为id,有要加上表信息,即用户表user主键user_id
规则
不要使用统一id当主键名,要有修饰词
例子
用户属于某公司,即用户表中需要存储公司表的主键作为外键【即使不建立外键】
用户表使用user,公司表使用corp
如果在公司表中主键id使用id,但在user表中主键id也是id,必然user表的外键公司id 应该是corp_id,这样会导致一个结果当user表和corp表联结时,
联结条件必须是user.corp_id = corp.id
如果联结的表过多时表名使用别名a,b,u,c时极其容易写错,还不容易排查错误
而如果user表主键定义为user_id,corp表主键定义为corp_id,user表中的公司id外键也定义为corp_id,
这样当表联结时,联结条件写为user.corp_id=corp.corp_id,联结条件一目了然。提高了SQL的可读性,节省了阅读SQL时表联结键的确认时间。
好处
节省开发时间,就算一次节省半秒,上千次之后也会节省十分钟,作为一个项目经理或dba,就算你做不到给开发减轻工作量,也不能拖后腿吧。
符合字段统一原则。
六、尽量not null
规则
在设计字段时尽量使用not null不可空。
数字类型默认0,字符串类型默认''零长度的字符串。
日期类型如果可以默认当前时间。
我知道作为开发人员嫌不可空麻烦,但是实际上可以在实体的getter方法中改写
数字可以return userId ==null?0:userID;
字符串可以return name ==null?"":name.trim();
日期可以return dt==null?new DateTime();
以上写法可以保证你的实体在插入时肯定不空,虽然开发写起来麻烦,但是好处多多。
好处
易于优化,虽然很多开发不以为然,但是你的项目真的需要高性能时你会后悔莫及,而很多项目开始时由于开发不考虑性能导致后期优化很费劲,为什么不提前把可以做好的做到最好。
节省空间,虽然可能只节省一个bit,但积少成多,好的性能都是一点一点积累出来的。
防止java出现空指针,好多空指针都是由于脏数据引起的。
NULL可能导致计算错误。例如concat(a,b),若a是NULL,结果为NULL。
如果时间字段无法默认时间,完全可以设置为null,不要在心里就反对null或者反对not null,我们是设计数据库,不要出现党*争。
七、注意varchar
当字段可以确定长度不超过一定数值时,建议使用char定长字符串类型,但如果整张表已经出现变长字段,那么都使用变长字段即可。
规则
如果可以都使用定长字符串,如果做不到就都使用变长字符串
好处
节省空间
易于优化
速度快,DBMS易于处理
八、范式
尽量符合三范式
规则
字段不可分割、表有主键、数据没有允余、表间关系明确
好处
范式目的是使结构更合理,消除存储异常,使数据冗余尽量小。便于插入、删除和更新。
范式是给关系型数据库创立的。对于增删改查四种操作总体来说性能和易用性最佳。如果你们的表只需要插入和查询,或者只需要插入和清空,CRUD四种操作不全需要时,完全可以违反范式。具体情况具体分析,没有必要在心里就反对范式或者严格遵守范式。我们是设计数据库,不是教条主义,不要出现党*争。
九、固定字段
删除标志、创建时间、修改修改、创建人id、修改人id五个字段为必须字段。
规则
删除标志默认为未删除的值,
创建时间设置为当前时间,
修改时间设置为数据修改时更新,
创建人设置id默认为0
创建人设置id默认为0
好处
大部分情况,这些字段都有必要,除非不需要保留已删除数据的不需要删除标识,而这种情况,基本在项目开始时分辨不出需要逻辑删除还是物理删除。
创建时间、修改时间、创建人、修改人没必要解释
十、状态值
规则
状态值,尽量不使用0,一般选择10,20,30,40等,
好处
防止需求突然加中间状态,原来定义的是0,1,2,3连续的状态,突然需要在2,3之间加个新状态,只能使用4,这样会对开发理解造成障碍,而如果初始就使用10,20,30,40作为状态,突然需要在20,30之间添加新的状态,完全可以使用25,即好理解又符合逻辑。
0对于前端开发不友好。
最后、上线前统一字符集和排序规则
项目上线之前执行以下SQL,会查询出指定库下的所有字段的排序规则和字符集,一定要统一后,再上线
其他老生常谈的问题可以自己查找,比如建议使用自增列做主键等
select table_name,column_name,character_set_name,collation_name
from information_schema.columns where table_schema = '库名' and data_type = 'varchar'
好处
防止字符串比较时出现隐式转换。
总结、好的设计会提高性能,提升便利
在保证性能的基础上,方便开发、易于运维、易于交接。
以上原则不是铁律,如果有的原则导致性能急剧下降,使用很不便利,完全可以无视原则,具体情况具体分析。世界上没有放之四海皆准的规则。