数据库的范式设计

原文链接：(三)MySQL之库表设计篇：一、二、三、四、五范式、BC范式与反范式详解！ - 掘金 (juejin.cn)

【一】引言

• MySQL的库表设计，在很多时候我们都是根据我们自己的个人喜好和习惯创建出来的，在前期的设计中总是会有考虑不到的地方，对于库表结构的划分也并不明确

• 这也就导致了我们在开发过程中，代码写着写着就会想去重构表的结构，甚至需要大面积的重构多张表结构

• 这种随心所欲的设计方式，无疑给开发造成了很大困扰，令我们很头疼。

• 但实际上设计DB库表结构时，也有一些共同需要遵守的规范，这些规范在数据库设计中被称为“范式”，理解并掌握这些设计时的规范，能让咱们在项目之初，设计的库表结构更为合理且优雅。

• 数据库范式中，声名远扬的有三大范式，但除此之外也有一些其他设计规范，如：

  • 数据库三大范式（1NF、2NF、3NF）

  • 第四范式(4NF）和第五范式：完美范式（5NF）

  • 巴斯-科德范式（BCNF）

  • 反范式设计

【一】数据库三大范式

• 范式(Normal Form)在前面也提到过，它就是指设计数据库时要遵守的一些原则
• 而数据库的三大范式，相信诸位在学习数据库知识时也定然接触过。
• 三大范式之间，它们是递进的关系，也就是后续的范式都基于前一个范式的基础上推行，就好比下面这句话：

今天我要先炒菜，然后吃饭，最后洗碗。

• 炒菜、吃饭、洗碗三者也属于递进关系
  • 后者都建立在前者之上，其顺序不能颠倒
  • 比如先吃饭再炒菜，这必然是行不通的。
• 数据库的三大范式也一样，第二范式必须建立在第一范式的基础之上，如若设计的库表第一范式都不满足，那定然是无法满足第二范式的。

写在前面的话：

​ 其实对于数据库三范式相关的资料，网上也有很多很多，但大部分资料都涉及了太多的概念，通篇看下来也很难让人理解

​ 因此下述的三范式则会结合具体的设计实例来让诸位彻底理解三范式。

【1】第一范式(1NF)

• 库表设计时的第一范式，主要是为了确保原子性的，也就是存储的数据具备不可再分性
• 这话咋理解呢？上个案例：

SELECT * FROM `zz_student`;

+----------------------+--------+-------+
| student              | course | score |
+----------------------+--------+-------+
| 竹子，男，185cm      | 语文   |    95 |
| 竹子，男，185cm      | 数学   |   100 |
| 竹子，男，185cm      | 英语   |    88 |
| 熊猫，女，170cm      | 语文   |    99 |
| 熊猫，女，170cm      | 数学   |    90 |
| 熊猫，女，170cm      | 英语   |    95 |
+----------------------+--------+-------+

• 在上述的学生表中，其中有一个student学生列，这一列存储的数据则明显不符合第一范式：
  • 原子性的规定，因为这一列的数据还可以再拆分为姓名、性别、身高三项数据
• 因此为了符合第一范式，应该将表结构更改为：

+--------------+-------------+----------------+--------+-------+
| student_name | student_sex | student_height | course | score |
+--------------+-------------+----------------+--------+-------+
| 竹子         | 男          | 185cm          | 语文   |    95 |
| 竹子         | 男          | 185cm          | 数学   |   100 |
| 竹子         | 男          | 185cm          | 英语   |    88 |
| 熊猫         | 女          | 170cm          | 语文   |    99 |
| 熊猫         | 女          | 170cm          | 数学   |    90 |
| 熊猫         | 女          | 170cm          | 英语   |    95 |
+--------------+-------------+----------------+--------+-------+

• 将student这一列数据，分别拆分为姓名、性别、身高三列，然后分别存储对应的数据才合理
• 通过这样的优化后，此时zz_student这张表则符合了数据库设计的第一范式。

那此刻思考一下：如果不去拆分列满足第一范式，会造成什么影响呢？

• 客户端语言和表之间无法很好的生成映射关系。
• 查询到数据后，需要处理数据时，还需要对student字段进行额外拆分。
• 插入数据时，对于第一个字段的值还需要先拼装后才能进行写入。

简单来说

​ 如果按照原本那张形式去做业务开发，显然操作起来会更加麻烦且复杂一些

​ 但第一范式的原子性，除开对列级别生效之外，行级别的数据也是同理

​ 也就是每一行数据之间是互不影响的，都是独立的一个整体。

【2】第二范式(2NF)

• 上述的第一范式还是比较容易理解
• 紧接着来看看第二范式
  • 第二范式的要求表中的所有列，其数据都必须依赖于主键
• 也就是一张表只存储同一类型的数据，不能有任何一列数据与主键没有关系
• 还是上面的那张表数据为例：

+--------------+-------------+----------------+--------+-------+
| student_name | student_sex | student_height | course | score |
+--------------+-------------+----------------+--------+-------+
| 竹子         | 男          | 185cm          | 语文   |    95 |
| 竹子         | 男          | 185cm          | 数学   |   100 |
| 竹子         | 男          | 185cm          | 英语   |    88 |
| 熊猫         | 女          | 170cm          | 语文   |    99 |
| 熊猫         | 女          | 170cm          | 数学   |    90 |
| 熊猫         | 女          | 170cm          | 英语   |    95 |
+--------------+-------------+----------------+--------+-------+

• 虽然此时已经满足了数据库的第一范式，但此刻观察course课程、score分数这两列数据，跟前面的几列数据实际上依赖关系并不大，同时也由于这样的结构，导致前面几列的数据出现了大量冗余
• 所以此时可以再次拆分一下表结构：

SELECT * FROM `zz_student`;

+------------+--------+------+--------+--------------+--------------+
| student_id | name   | sex  | height | department   | dean         |
+------------+--------+------+--------+--------------+--------------+
|          1 | 竹子   | 男   | 185cm  | 计算机系     | 竹子老大     |
|          2 | 熊猫   | 女   | 170cm  | 金融系       | 熊猫老大     |
+------------+--------+------+--------+--------------+--------------+

SELECT * FROM `zz_course`;

+-----------+-------------+
| course_id | course_name |
+-----------+-------------+
|         1 | 语文        |
|         2 | 数学        |
|         3 | 英语        |
+-----------+-------------+

SELECT * FROM `zz_score`;

+----------+------------+-----------+-------+
| score_id | student_id | course_id | score |
+----------+------------+-----------+-------+
|        1 |          1 |         1 |    95 |
|        2 |          1 |         2 |   100 |
|        3 |          1 |         3 |    88 |
|        4 |          2 |         1 |    99 |
|        5 |          2 |         2 |    90 |
|        6 |          2 |         3 |    95 |
+----------+------------+-----------+-------+

• 经过上述结构优化后，之前的一张表此时被我们拆分成学生表、课程表、成绩表三张
• 每张表中的id字段作为主键，其他字段都依赖这个主键。
• 无论在那张表中，都可以通过id主键确定其他字段的信息。

主键可以不用id，但最好是自增的主键ID，这跟索引有关，后续索引篇详细讲解。

• 此时再将目光看到先后两张学生表
  • 原本的学生表有六条学生记录，其中有四条是冗余数据
  • 此时的学生表则只有两条数据，同时这张学生表中只存储学生信息相关的数据。
• 经过本次结构优化后，每张表的业务属性都具备“唯一性”，也就是每张表都只会描述了“一件事情”，不会存在一张表中会出现两个业务属性（例如之前的学生表包含了学生信息和课程成绩）。

【3】第三范式(3NF)

• 前面已经对第一范式、第二范式进行了直观阐述
• 接下来聊一聊数据库的第三范式
  • 第三范式要求表中每一列数据不能与主键之外的字段有直接关系
• 怎么理解呢？基于上述的例子：

+------------+--------+------+--------+--------------+--------------+
| student_id | name   | sex  | height | department   | dean         |
+------------+--------+------+--------+--------------+--------------+
|          1 | 竹子   | 男   | 185cm  | 计算机系     | 竹子老大     |
|          2 | 熊猫   | 女   | 170cm  | 金融系       | 熊猫老大     |
+------------+--------+------+--------+--------------+--------------+

• 比如这张学生表，目前即符合第一范式，也符合第二范式
  • 但看最后的两个字段，department表示当前学生所属的院校，dean则表示这个院系的院长是谁。
• 一般来说，一个学生的院长是谁，首先是取决于学生所在的院系的
  • 因此最后的dean字段明显与department字段存在依赖关系
• 因此需要进一步调整表结构：

SELECT * FROM `department`;

+---------------+-----------------+-----------------+
| department_id | department_name | department_dean |
+---------------+-----------------+-----------------+
|             1 | 计算机系        | 竹子老大        |
|             2 | 金融系          | 熊猫老大        |
+---------------+-----------------+-----------------+

SELECT * FROM `zz_student`;

+------------+--------+------+--------+---------------+
| student_id | name   | sex  | height | department_id |
+------------+--------+------+--------+---------------+
|          1 | 竹子   | 男   | 185cm  |             1 |
|          2 | 熊猫   | 女   | 170cm  |             2 |
+------------+--------+------+--------+---------------+

• 经过进一步的结构优化后
  • 又将原本的学生表拆为了院系表、学生表两张
    • 学生表中则是只存储一个院系ID，由院系表存储院系相关的所有数据。
  • 至此，学生表中的每个非主键字段与其他非主键字段之间，都是相互独立的，之间不会再存在任何依赖性，所有的字段都依赖于主键。

那这里为什么要调整呢？不调整不行吗？还真不行，来简单思考一下不调整结构的情况下会发生什么问题：

• 当一个院系的院长换人后，需要同时修改学生表中的多条数据。
• 当一个院长离职后，需要删除该院长的记录，会同时删除多条学生信息。
• ......

也就是如果设计的表结构，无法满足第三范式，在操作表时就会出现异常，使得整个表较难维护。

【4】数据库三范式小结

总结如下

• 到这里就已经将库表设计的三范式做了直观阐述：

  • 第一范式：

    • 确保原子性，表中每一个列数据都必须是不可再分的字段。

  • 第二范式：

    • 确保唯一性，每张表都只描述一种业务属性，一张表只描述一件事。

  • 第三范式：

    • 确保独立性，表中除主键外，每个字段之间不存在任何依赖，都是独立的。

必要性

• 经过三范式的示例后，数据库中的表数量也逐渐多了起来，似乎设计符合三范式的库表结构，反而更加麻烦了对吗？

• 答案并非如此，因为在没有按照范式设计时，会存在几个问题：

  • 整张表数据比较冗余，同一个学生信息会出现多条。

  • 表结构特别臃肿，不易于操作，要新增一个学生信息时，需添加大量数据。

  • 需要更新其他业务属性的数据时，比如院系院长换人了，需要修改所有学生的记录。

• 但按照三范式将表结构拆开后，假设要新增一条学生数据，就只需要插入学生相关的信息即可

• 同时如果某个院系的院长换人了，只需要修改院系表中的院长就行，学生表中的数据无需发生任何更改。

因此，经过三范式的设计优化后，整个库中的所有表结构，会显得更为优雅，灵活性也会更强。