写在前面

数据库范式过于理论化。在实践中,只应用数据库范式显然是不够的,数据库范式也没有很好指导大家实践。

我总结了一套非常简单有用的数据库设计流程。总的思路就是:拆表 ,就是先抓住一个表,分析具体的情境,分成多个表。对于具体情况我在下面进行具体的说明。这个思路做完后的分表方案,既尽量符合了数据库范式,又考虑了实际情况。

当然,这也只是我的一家之言,仅供参考,欢迎评论讨论。

为什么要表设计

合理的表设计主要有这几个好处:

  • 减少冗余重复数据
  • 方便修改,减少修改时间

拆表法

从一个表开始

先找到一个表做切入点,任何表都没有问题。
如果你是从0开始设计数据库表,那么你先建一个你认为的核心表作为切入点
比如,
对于一个论坛,切入点可以是 帖子表,也可以是 用户表
对于一个学生系统,切入点可以是课程表
对于一个商城,切入点可以是商品表

如果是对于一个新的需求,切入点就是这个新的需求的表。

列的粒度尽量小

在设计每个表的列的时候,先要确保列的粒度尽量小,即一个列不要表示多个东西。这也是第一范式要求的。
错误示范

地址
广东省深圳市XXX小区

修改后:

省份城市具体地址
广东省深圳市XXX小区

当然我的修改也没有完全按照第一范式,具体地址也可以再分成行政区和街道,这个要按照具体的业务来确定要不要再分小。所以是尽量小。

运用到具体设计中,我以论坛的帖子表为例进行设计示范:

帖子表

  • 帖子id
  • 帖子分类
  • 帖子时间
  • 帖子标题
  • 帖子内容
  • 发帖人id
  • 发帖人姓名
  • 发帖人等级
  • 发帖人性别
  • 跟帖集合
  • 点赞用户集合

是否需要拆表

在运用拆表之前,先判断一下这个表要不要拆成多个表。
一般来说,有一种情况我们是不需要拆表的,就是全量读取,不逐条修改的表
比如日志表,一般只会插入,最后再合并分析,这种表虽然有很多冗余数据,但是一般都不需要拆表。因为拆表节省了修改时间,但是会增加插入时间和查询时间,所以在空间允许的情况下,不需要考虑拆表。

和主键存在间接关系的列要拆表

主键一般表示一行的唯一特征,比如商品表的主键是商品 id,学生表的 id 是学号等
我们在确定主键后,需要判断每一列是否和主键存在间接关系,如果存在,需要拆表。这也是第三范式的要求。

如果有一列,他和某一非主键列是关联的,那么我们可能要考虑拆表。

以帖子表为例,发帖人的等级、姓名、性别和发帖人 id 有直接关系,而和帖子id 就是间接关系了,这个时候我们需要拆表,拆出一个用户表

帖子表

  • 帖子id
  • 帖子分类
  • 帖子时间
  • 帖子标题
  • 帖子内容
  • 发帖用户 id
  • 跟帖集合
  • 点赞用户集合

用户表

  • 用户 id
  • 姓名
  • 等级
  • 级别

一对多要拆表

我们想在一列里面表示多个相同种类的不同对象的时候,即存在一对多关系,要拆表。拆表后在拆出来的表中查询

比如帖子表的 跟帖集合、点赞用户集合 就是一个帖子 id 对应多个跟帖对象和点赞用户对象。

还有另外一种情况,就是存的不是对象而是多个文本。比如,我们可能会遇到用户手机号有多个的情况,存的时候我们可以存成 手机号1/手机号2 ,这种原则上也是要拆表的。当然,我们可以根据“是否需要拆表”的原则来判断要不要拆表,如果我们不用经常改用户的手机号,或者改的时候一次性读取再一次性写入,那么,不拆也是可以的。可以参考的的做法是用 mysql 的 json 数据格式存储。

以论坛表设计为例,跟帖集合、点赞用户集合需要拆表

帖子表

  • 帖子id
  • 帖子分类
  • 帖子时间
  • 帖子标题
  • 帖子内容
  • 发帖用户 id

用户表

  • 用户 id
  • 姓名
  • 等级
  • 级别

跟帖表

  • 跟帖 id
  • 帖子 id
  • 跟帖内容
  • 跟帖用户 id
  • 跟帖时间

点赞表

  • 点赞 id
  • 帖子 id
  • 用户 id
  • 点赞时间

多对一看情况拆表

如果多个列的不同主键,对应的某个列都是同一个值,那么,也要考虑是否要拆表。

如果这个属性以后可能要有新需求的话,最好拆表,这个比较灵活。

比如,论坛表中的 帖子表 帖子分类,很有可能 a,b,c 帖子是 a 类,d,e,f 是 b 类,那么,这里就存在一个多对一的关系。我们也可以考虑拆表。

其实,这和 “和主键存在间接关系的列要拆表”这一原则是相似的,因为如果我们不拆,后面有新需求直接加到帖子表后面,也变成了 “和主键存在间接关系的列要拆表”的情景。

帖子分类表

  • 分类 id
  • 分类名

帖子表

  • 帖子id
  • 帖子分类 id
  • 帖子时间
  • 帖子标题
  • 帖子内容
  • 发帖用户 id

这样子拆表,就可以方便以后帖子分类扩展,比如某分类是要指定用户才能发。我们只需要在帖子分类表加一个权限规则列即可。

新需求分析

上面简单的对一个论坛的分表做了一个规划,当然并不全面,我们还需要再根据新需求循环拆表法的步骤
比如有一个新需求,要有帖子板块,我们就根据拆表法再分析一次。

根据需求,新建一个帖子板块表:
帖子板块表

  • 帖子板块 id
  • 帖子板块名
  • 帖子模块对应的分类集合
  • 帖子板块对应的帖子集合
  • 帖子板块对应的版主集合

显然有三个集合,根据一对多拆表原则,要拆到三个表中。

对于的分类集合,要拆到分类表中。

帖子分类表

  • 分类 id
  • 分类名
  • 板块 id

对于帖子集合,要拆到帖子表中。

帖子表

  • 帖子id
  • 帖子分类
  • 帖子时间
  • 帖子标题
  • 帖子内容
  • 发帖用户 id
  • 板块 id

对于版主集合,我们可能会首先想到拆到用户表,但是一个用户可能管理多个板块,所以又构成一对多关系,所以要再拆到一个新表上面:拆到版主-版块表。这也称为多对多关系,本质上市两个一对多关系,只要再拆出一个中间表,就可以满足一对多拆表的要求。

版主-版块表

  • 序号 id
  • 版主用户 id
  • 版块 id

论坛拆表最终结果

我们从一个帖子表开始,运用了两次拆表法,拆出了这么多个表。你可以根据不同的需求,就可以把整一套数据库表设计出来:

帖子表

  • 帖子id
  • 帖子分类
  • 帖子时间
  • 帖子标题
  • 帖子内容
  • 发帖用户 id
  • 板块 id

用户表

  • 用户 id
  • 姓名
  • 等级
  • 级别

跟帖表

  • 跟帖 id
  • 帖子 id
  • 跟帖内容
  • 跟帖用户 id
  • 跟帖时间

点赞表

  • 点赞 id
  • 帖子 id
  • 用户 id
  • 点赞时间

帖子分类表

  • 分类 id
  • 分类名
  • 板块 id

板块表

  • 帖子板块 id
  • 帖子板块名

版主-版块表

  • 序号 id
  • 版主用户 id
  • 版块 id

参考资料

https://www.codeproject.com/Articles/359654/%2FArticles%2F359654%2F11-important-database-designing-rules-which-I-fo-2