本人自从转开发以来，一共设计过6个系统总计200+数据库表，主要基于MySQL数据库，涉及系统包括一个WMS系统，一个CMS系统，三个CRM系统，和一个OMS系统。同时得益于职业初期的工作，我对于其他主流数据库（SQL Server, Oracle, DB2, Teradata, PGSQL, Greenplum, Vertica, RedBrick, Netezza, Sybase, etc.）也有一定了解。本篇主要总结一下这些年来我的一些数据库设计的经验。 对于数据库表的设计，网上一搜一大堆，这里就挑一些我认为比较重要或者比较容易被忽视的点着重讲一讲。
1. 发现实体
数据库表的设计，是整个系统中最重要的工作之一。毛主席说的好，基础不牢，地动山摇。如果数据模型在开发阶段甚至上线后出现了变动，那对整个软件开发流程的影响是牵一发而动全身的，所以我们应该在初期多花一些时间，将需求理解透彻，搞清楚系统涉及的实体及实体间的关系后，画出ER图，再谨慎地建表。严格上来讲表的设计是要遵循领域模型->逻辑模型->物理模型的顺序来的，在这里推荐一个老牌但比较好用的设计工具－Sybase公司的Power Designer。在Power Designer里，用户可以完全遵守上述的三个步骤，一步一步地进行数据库的，同时在这三个模型的转换在PD中是非常顺滑的，并且在设计完逻辑模型后，用户可以根据不同的数据库自动生成不同的物理模型和DDL，十分强大。
除了工具之外，这边我还想简单讲讲表设计的方法论，更精确地讲应该是需求中实体识别的方法。众所周知，数据库的一张表实际上对应的就是现实世界中的一个实体，所以把需求中实体及其他们的关系识别出来，那么基本就可以把该系统的表设计的八九不离十了。举个简单的例子来说，现在有这么一个需求：
做一个体育主题的论坛，有不同体育板块。用户可以发帖，回帖，点赞，上传图片等。
当我们拿到需求准备开始设计表时，可以先把需求里的功能点一条一条的整理出来，并使用主谓宾结构列出来，并且注意量词修饰主语和宾语：
论坛包含多个板块
一个帖子属于一个板块
一个板块包含多个帖子
一个帖子包含多条信息
一个用户可以发布多个帖子
一条信息只属于一个用户
一条信息可以对应多条回复信息
一个用户可以上传多张图片
一条信息可以包含多张图片
一张图片可以用于多个消息
...
然后我们便可把上面的句子中的主语和宾语拿出来作为实体：
板块，帖子，用户，信息，图片..
句子中的量词即可作为实体间的关系设计的基础：
板块 1:n 帖子
帖子 1:n 留言
用户 1:n 消息
消息 1:n (回复)消息
用户 1:n 图片
消息 n:n 图片
...
对于1的那一端，我们通常作为n的实体的中的一个关联键存在，比如消息表中使用用户id关联用户。回复消息其实是消息的一种类型，所以并不需要单独作为一个实体设计，而是在消息表中增加消息类型字段区分，同时增加被回复消息id进行自关联（这里其实有两种设计，取决于需求，一种是每条回复都会作为帖子的一条新的信息展示，附带上被回复的信息；另一种设计是每条信息的回复全都在该信息下显示。这里采取前一种的设计方式）。对于多对多的关系，如消息和图片的关系，则需要增加一张关系表来记录两个实体间的关系，当关系表中出现了附加属性的时候，关系表本身也变成了一个实体，比如同一张图片在不同消息中展示的分辨率不同，那这个字段就必须存在关系表中。
切勿将不同的实体放在同一张表里。一张表里的每一条数据其实就是该实体的一个实例化对象，如果把多个实体放在同一个表里，就会出现一些字段只有某几行才有，而另一些字段是另外几行数据才有的尴尬局面，甚至还会出现同一个字段在不同的数据行中含义不同的吊诡现象。
在表的设计过程中，要充分依照三范式的设计思想，但同时也要进行必要的反范式设计，将一些必要的数据进行冗余，用空间换时间，减少关联。对于冗余的数据，一般来说是不会轻易更新的，比如用户名等，如果有更新的场景，则需要保证两个表数据的一致性。
2.墨菲定律
"会出错的总会出错"
数据库作为数据存储的媒介，也作为数据落地的最后一道屏障，应该尽最大可能避免脏数据的落库。一般好的系统，都会在代码层面尽可能地做好数据的校验，但不论是什么系统，都应该在数据库层面严格把控好数据质量这关。如果一个字段业务上不允许为空，那么请设置为非空，如果一个字段有唯一性，那么请设置唯一约束。做过脏数据订正的人都知道，脏数据落库容易，订正却要花上数倍的时间精力。
3.unsigned的陷阱
很多人在设计数字字段的时候，都会勾上unsigned属性，这样可以在不改变存储空间的前提下，使存储的数量增加一倍（牺牲了负数）。但是要注意，数据库中的int和java中的int一样，都是4个字节，范围-2147483648~2147483647，如果你把数据库的unsigned勾上了，当数据库里出现2147483648的时候，映射到java中就超出范围了。所以当数据库中使用unsigned字段时，代码中应该使用更大的数据类型。
4.业务时间和数据时间
设计表的时候，我们常常每个表必须要有created_by, updated_by, created_time, updated_time以及is_deleted。这是没错的，不过很多人弄错了这里的created_time, updated_time的含义，将他们当作业务时间来使用，严格上来讲这是不对的（虽然绝大多数情况下业务时间和数据时间是一样的）。这两个字段应当由数据库本身来维护，比如MySQL:
created_timedatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_timedatetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
这是数据创建和更新的时间，和业务无关，业务的时间应该使用另外有业务含义的字段。拿用户表来说，创建时间可以使用registered_time，编辑时间（如果需要存储的话）可以用edited_time等，这些是业务时间。为什么不能直接使用数据时间？因为应用服务器时间和数据库时间可能会有差异，同时数据时间的主要作用是数仓团队每天抽数的条件。且当表进行水平切分，或者进行数据库切换的时候，数据时间也会相应的变化，如果这个时候还将其当作业务时间使用，那么就会有问题。
5.纠结的状态位
数据表中常常会有状态字段，这里有两点需要注意：
- 状态的值必须互斥
- 不要用定时更新的状态进行业务判断
拿订单表来说，我们在设计状态的时候，有时会将订单完成状态、支付状态、发货状态等记录到统一的“订单状态”中。这其实是有问题的，因为这几个状态其实是不互斥的。比如一个“已结束”的订单，可能是“已收货”且“已支付”的，也可能是“已退货”且“已退款”的，甚至在某一个还可能是“已收货”且“申请退款”。当我们保存了订单完成维度的订单状态，就无法保存发货维度和支付维度的状态，这几个维度其实是平行的。当状态不互斥时，我们就必须将它们保存在单独的字段中，这样才可以保存不同的状态组合，而不是在一个字段上不停地增加譬如“收货且退款”、“收货已支付”之类的组合状态，归根结底这种设计就是违背了第一范式：每个属性都应该是不可切分最小的单元。BTW，代码中应该有状态机来实现状态的流转，并且清晰定义起始态和终态。
对于第二点，常见的是在状态字段中保存譬如“已过期”这样的根据定时任务来赋值的状态，且代码中还用这个字段来进行业务逻辑判断。而我们在表中其实常常还有一个单独的时间字段来保存准确的业务过期时间。由于定时任务始终存在一定延迟，所以在代码中常常因为只判断过期状态不保险而增加过期时间的判断，那么“已过期”状态其实根本就是多余的。我个人建议是不需要保存任何的定时更新的状态，对于这类业务判断直接使用时间做。
6.计算字段
这点我之前在《oracle迁移mysql总结》中也提到过。这里再啰嗦一下：
mysql 5.7新增了计算字段generated column，可以根据一般字段动态地生成计算字段中的值。弥补了mysql没有函数索引的缺憾。通过把时间类型的字段自动格式化成不同粒度，这样当我们需要根据这些粒度进行汇总时就可以直接使用这些字段进行group by，通过走索引增加效率。这几个字段的值并不需要人为生成，在插入相应的源字段时会自动生成，所以对现有代码是透明的。
摘录涉及到的建表语句如下：
CREATE TABLE ORDER
……
CREATE_DAY char(8) GENERATED ALWAYS AS (date_format(CREATE_DATETIME,'%Y%m%d')) STORED COMMENT '创建日，格式：20171207',
PAY_DAY char(8) GENERATED ALWAYS AS (date_format(PAY_DATETIME,'%Y%m%d')) STORED COMMENT '支付日，格式：20171207',
PAY_MONTH char(6) GENERATED ALWAYS AS (date_format(PAY_DATETIME,'%Y%m')) STORED COMMENT '支付月，格式：201712',
PAY_QUARTER char(6) GENERATED ALWAYS AS (concat(year(PAY_DATETIME),'Q',quarter(CREATE_DATETIME))) STORED COMMENT '支付季度，格式：2017Q4',
PAY_YEAR char(4) GENERATED ALWAYS AS (year(PAY_DATETIME)) STORED COMMENT '支付年，格式：2017'
……
KEY IDX_CREATE_DAY (CREATE_DAY),
KEY IDX_PAY_DAY (PAY_DAY),
KEY IDX_PAY_MONTH (PAY_MONTH),
KEY IDX_PAY_QUARTER (PAY_QUARTER),
KEY IDX_PAY_YEAR (PAY_YEAR)
……
