数据库设计规范

1 数据库设计规范说明
本数据库设计规范目标是统一数据库命名规则，以及在使用数据库过程中的规范。
1.1通用命名规范
通用命名规范
1、命名：所有命名只能使用大写英文字母，数字和下划线（多个单词用下划线连接），且不以数字、下划线开头，2个下划线中间不能出现数字，长度为1－30个字符，如：
正例：COMPANY, APPLICATION2_FUNCTION
反例：APPLICATION_2_FUNCTION，_APPLICATION

2、缩写：一个单词可缩写为1－4个字符,全部大写。
例如：application = APP (3)
多个单词组合的缩写取每个单词的首字母或头两个字母组成，同样不超过4个字符；
例如：
application_function = AF (1:1)
application_function = APFU (2:2)
application_function_role = APFR (2:1:1)
application_function_role_banana = AFRB (1:1:1:1)

3、连接中禁止连续出现两个及以上下划线
例如：APPLICATION__CODE

1.2数据库命名规范
数据库命名规范
1、【强制】参见通用命名规范；
2、【推荐】库名与应用名称尽量一致。

1.3表使用规范
表使用规范
1、【强制】表名不使用复数名词；
说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量;
2、【强制】表名、字段名必须使用大写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字，参见通用命名规范；
3、【强制】禁止使用数据库保留关键字；
说明：不同类型的数据库关键字不一样（例如ORACLE的ACTION,PRIVATE,IS_DELETE,PROCESSS_TATUS；MySQL的DESC、RANGE、MATCH、DELAYED等），请参考对应数据库的官方保留字；
4、【强制】逻辑删除表必须包含如下特有的字段；
isdeleted
5、【强制】启动工作流的表必须包含如下特有的字段；
工作流字段
6、【推荐】”前缀”（2位）+”“+”单词”（能够表达当前表的意义）；
附：表名定义前缀参考示例：
表定义前缀
7、【推荐】关系表命名：
1)两张实体表之间的关系表：
“实体主表+”“+”实体子表(无表前缀)
示例：
MF_CARGO；海运货物表
MF_CONTAINER；海运箱表
关系表：MF_CONTAINER_CARGO
8、【推荐】单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。 说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

1.4表别名命名规范
表别名命名规范
1、【强制】参见通用命名规范；
2、【推荐】表别名使用4位,表前缀+2位表单词缩写，如果是一个单词取前两位，如果是多个单词取前两个单词首位+”_”+”单词”（能够表达当前字段的意义）
对于系统涉及到增删改查的执行命令语句，需统一对表设置别名，根据别名编写语句
例子：
SELECT FMBK.CREATOR,
FMBC.CREATOR
FROM   FM_BOOKING FMBK, FM_BOOKING_CARGO  FMBC  
1.5字段使用规范
字段使用规范
1、【强制】字段名必须使用大写英文字母、数字或下划线（多个单词用下划线连接）组成，且不以数字、下划线开头，参见通用命名规范；
2、【强制】主键字段命名为“表名+ID”，只支持单字段主键，云平台生成的代码不支持联合字段的主键；
4、【强制】字段名不要出现单个字母或单个数字的单词；
反例：V_STATUS，CONTRACT_2_NAME。
5、【强制】禁止使用数据库保留关键字，请参考对应数据库的官方保留字；
6、【强制】每个字段必须有字段注释；
说明：建议字段注释不超过6个中文字符，字段注释默认是界面输入框的标签。
带备注数据库表结构中的comment，要求不能存在敏感字符、特殊字符，禁止把枚举值放在comment中
反例：比如订单类型，1-海运、2-路运
7、【强制】小数类型为decimal，禁止使用float和double；
说明:float和double在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小数分开存储。
8、【强制】表达是与否概念的字段，必须使用IS_XXX的方式命名，数据类型是CHAR(1)；
正例：表达逻辑删除的字段名IS_DELETED，默认X表示删除，空表示未删除。如果有特殊需要，支持配置其他值。
9、【强制】业务功能不要使用云平台默认的10个系统字段；
云平台默带10个字段说明：
平台默认字段

10、 【推荐】如果项目组使用数据库已有的表结构，创建人、创建时间等固定字段名与云平台上述10个字段名不一致，可通过公共字段配置实现；
11、 【推荐】如果某个字段在多个表中出现并且代表相同的意思，则要求字段的实际名字一致；
例如：多个表中出现的字段，比如顾客编号CUSTOMER_ID，可以出现在顾客表、顾客购物表 中，在这两个表中，顾客编号均为CUSTOMER_ID。杜绝一个叫ID一个叫CUSTOMER_ID的情况。
12、【推荐】varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为大文本字段（Oracle是clob，MySQL是text），独立出来一张表，用主键来对应，避免影响其它字段索引效率。
13、 【推荐】字段的命名最好两个单词组成“范围限定_字段含义”；
正例：ORDER_STATUS；反例：STATUS。
14、【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。
冗余字段应遵循：
1）不是频繁修改的字段。
2）不是varchar超长字段，更不能是大文本字段。 正例：商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。

1.6索引使用规范
索引使用规范
1、【强制】参见通用命名规范；
2、【强制】多个字段复合索引包括“IDX”+”“+”字段名称”（能够表达当前索引的意义）
多个字段组合索引：“IDX”+”“+”字段关键单词”+””+”字段关键单词”+（能够表达当前索引的意义）；
3、【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
唯一索引定义：“UNI”+”“+”字段名称” ；
4、【推荐】超过三个表禁止join。需要join的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引；
说明：即使双表join也要注意表索引、SQL性能。
5、【推荐】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可；
6、【推荐】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决；
7、【推荐】如果有order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能；
正例：where a=? and b=? order by c; 索引：a_b_c
反例：索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引a_b无法排序。
8、【推荐】利用覆盖索引来进行查询操作，避免表扫描；
说明：如果一本书需要知道第11章是什么标题，会翻开第11章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。 正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用explain的结果，extra列会出现：using index。
9、【推荐】建组合索引的时候，区分度最高的在最左边；
正例：如果where a=? and b=? ，如果a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。
说明：存在非等号和等号混合时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么即使c的区分度更高，也必须把d放在索引的最前列，即索引idx_d_c。
10、【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。

1.7约束命名规范
约束命名规范
1、【强制】主键：PK_字段名；
2、【强制】外键：FK_字段名；
3、【强制】唯一（unique）约束：UNI_字段名。

1.8视图命名规范（不推荐使用）
视图命名规范（不推荐使用）
1、【强制】视图名称包括”前缀”（VW）+”_”+”单词”（能够表达当前视图的意义）。
例子：VW_STANDING

1.9自定义SQL命名规范
自定义SQL命名规范
1、【强制】自定义SQL名称包括 “前缀”（VW）+”_”+”单词”（能够表达当前自定义SQL查询的意义）。
例子：提货单信息：VW_SDELIVERORDER
1.10SQL语句规范
SQL语句规范
1、【推荐】不要使用count(列名)或count(常量)来替代count(*)，count(*)是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。
说明：count(*)会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行。
2、【推荐】count(distinct col) 计算该列除NULL之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为NULL，那么即使另一列有不同的值，也返回为0。
3、【推荐】当某一列的值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结果为NULL，因此使用sum()时需注意NPE问题。
正例：可以使用如下方式来避免sum的NPE问题：SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;
4、【推荐】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
5、【推荐】数据订正（特别是删除、修改记录操作）时，要先select，避免出现误删除，确认无误才能执行更新语句。
6、【推荐】in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内。
7、【推荐】如果有国际化需要，所有的字符存储与表示，均以utf-8编码，注意字符统计函数的区别。
说明： SELECT LENGTH(“轻松工作”)； 返回为12 SELECT CHARACTER_LENGTH(“轻松工作”)； 返回为4 如果需要存储表情，那么选择utf8mb4来进行存储，注意它与utf-8编码的区别。

1.11控件相关
控件相关
1、联想控件类型的字段，必须保存code和name，否则无法进行联想，或者未来会有性能问题。因此设计字段为联想控件的时候，请保留2字段。除非该字段不需要进行翻译，才能使用一个字段。
2、数据字典类型的存储，不能使用字典ID（数字类型）保存，只能使用字典值的code（文本）存储，否则在转换或显示时，会出现转换失败（ID和数据字典表的字典值字段类型不同）。且下拉控件只需要一个字段存储即可。
3、日期控件或者日期时间控件，即格式为‘yyyy:MM:dd HH:mm:ss’或者是‘yyyy:MM:dd’数据库字段设计可为date，varchar，timestamp类型。如果是时间控件即格式‘HH:mm:ss’则类型为varchar文本类型。
4、复选框是文本类型，长度为1即可，默认X是true，空是false。如果要使用Y/N复选框，可以直接配置Y/N复选框，Y是true，N是false。如有其他特殊的需要，例如T/F,1/0,复选框配合数据字典一起配置。
5、地区控件，varchar类型，一个字段即可。Ec地区控件存储的是6位编码，省市区各占2位。没有支持到国家或者是到第四级街道地址。