第33期：MySQL 表标准化设计

爱可生开源社区 2021-08-05

434

关系表设计合理与否是影响关系型数据库性能的核心要素之一。

谈到关系型数据库表设计问题，首先想到的是范式理论。也就是说一张表的设计首先要满足一定的范式，完了后再根据一定的需求来反范式设计，也即冗余备用设计。

数据库范式一般包含6个，分别为1NF、2NF、3NF、BCNF、4NF、5NF。这6个范式级别分别从数据是否允许一定范围的冗余、数据是否更加精细化的管理这两个关键点出发，越往后，数据越规范，冗余越小，可读性越强。

一般来说，达到3NF或者BCNF即可，或者更进一步，达到4NF即可，5NF更加偏向学术。

再次假设我们对各种依赖关系非常明确（部分函数依赖，完全函数依赖，传递函数依赖，多值依赖，连接依赖等）。下面我用经典的员工表与学生表来举例说明每个范式的逐级优化。

1NF：也即属性具备原子性，不可拆分。

对数据如何存放要求最低，目的是让关系表的属性（字段或列）保持原子性，不可再次拆分。

1NF 用员工表来做示例，表结构如下：

(debian-ytt1:3500)|(ytt)>desc employee;+-----------------+-------------+------+-----+---------+-------+| Field           | Type        | Null | Key | Default | Extra |+-----------------+-------------+------+-----+---------+-------+| employee_number | varchar(64) | YES  |     | NULL    |       || employee_name   | varchar(64) | YES  |     | NULL    |       || salary          | json        | YES  |     | NULL    |       || dept            | varchar(64) | YES  |     | NULL    |       || dept_desc       | text        | YES  |     | NULL    |       |+-----------------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

表 employee 有五个字段，分别为 employee_number（员工号码）、employee_name（员工姓名）、salary（员工薪水）、dept（所属部门）、dept_desc（所属部门描述信息）。

员工薪水字段数据类型为 JSON ，用来存放数组或者字典数据，很明显员工薪水字段不具备原子性，员工表不符合1NF。插入一条记录，更加直观：

(debian-ytt1:3500)|(ytt)>select * from employee\G*************************** 1. row ***************************employee_number: 202010001  employee_name: 小王         salary: {"base_salary": 10000, "extra_salary": 100000}           dept: 财务部      dept_desc: NULL1 row in set (0.00 sec)

以上结果中可以看出，员工薪水字段包含了两个属性：基本工资和额外工资。那改造下这个字段，一个变两个，改造后的员工表结构如下：

(debian-ytt1:3500)|(ytt)>desc employee;+-----------------+-------------+------+-----+---------+-------+| Field           | Type        | Null | Key | Default | Extra |+-----------------+-------------+------+-----+---------+-------+| employee_number | varchar(64) | YES  |     | NULL    |       || employee_name   | varchar(64) | YES  |     | NULL    |       || base_salary     | varchar(30) | YES  |     | NULL    |       || extra_salary    | varchar(30) | YES  |     | NULL    |       || dept            | varchar(64) | YES  |     | NULL    |       || dept_desc       | text        | YES  |     | NULL    |       |+-----------------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)

员工薪水字段被拆分成两个：基本薪水和额外薪水。此时表 employee 每个字段都具备原子性，不可拆分，符合1NF。

但是符合1NF的表冗余依然太多，比如一个部门有10个员工，那 dept 和 dept_desc 两个字段就会有很多重复记录。可以这么理解，除了1NF用来保证列原子性外，其余更高级的范式都是保证在特定场景下的表原子性。

2NF：非主键字段必须对主键字段完全函数依赖，不存非主键字段对主键字段的部分函数依赖。

2NF 比1NF在数据规范上更进一步，减少更多的冗余。

2NF改用学生表来做示范，学生表结构如下：

(debian-ytt1:3500)|(ytt)>desc `学生表` ;+--------------------+------------------+------+-----+---------+-------+| Field              | Type             | Null | Key | Default | Extra |+--------------------+------------------+------+-----+---------+-------+| 学号               | varchar(64)      | NO   | PRI | NULL    |       || 姓名               | varchar(64)      | YES  |     | NULL    |       || 年龄               | tinyint unsigned | YES  |     | NULL    |       || 性别               | char(1)          | YES  |     | NULL    |       || 课程               | varchar(64)      | NO   | PRI | NULL    |       || 课程学分           | tinyint unsigned | YES  |     | NULL    |       || 所属系名称          | varchar(64)      | YES  |     | NULL    |       || 课程成绩           | varchar(10)      | YES  |     | NULL    |       || 系地址             | varchar(100)     | YES  |     | NULL    |       || 系电话             | varchar(20)      | YES  |     | NULL    |       |+--------------------+------------------+------+-----+---------+-------+10 rows in set (0.00 sec)

这里对于学生表，设计主键为（学号，课程），姓名、年龄、性别等字段依赖学号字段，但是不依赖课程字段；课程成绩、课程学分依赖课程字段，不依赖学号字段。此表存在部分函数依赖问题，不满足2NF，需要做进一步的拆分。拆分后的表结构如下：

学生表：

(debian-ytt1:3500)|(ytt)>desc `学生表`;+-----------------+------------------+------+-----+---------+-------+| Field           | Type             | Null | Key | Default | Extra |+-----------------+------------------+------+-----+---------+-------+| 学号            | varchar(64)      | NO   | PRI | NULL    |       || 姓名            | varchar(64)      | YES  |     | NULL    |       || 年龄            | tinyint unsigned | YES  |     | NULL    |       || 性别            | char(1)          | YES  |     | NULL    |       || 所属系名称      | varchar(64)      | YES  |     | NULL    |       || 系地址          | varchar(100)     | YES  |     | NULL    |       || 系电话          | varchar(20)      | YES  |     | NULL    |       |+-----------------+------------------+------+-----+---------+-------+7 rows in set (0.00 sec)

课程表：

(debian-ytt1:3500)|(ytt)>desc `课程表`;+--------------+------------------+------+-----+---------+-------+| Field        | Type             | Null | Key | Default | Extra |+--------------+------------------+------+-----+---------+-------+| 课程         | varchar(64)      | NO   | PRI | NULL    |       || 课程学分     | tinyint unsigned | YES  |     | NULL    |       |+--------------+------------------+------+-----+---------+-------+2 rows in set (0.00 sec)

选课表：

(debian-ytt1:3500)|(ytt)>desc `选课表`;+--------------+-------------+------+-----+---------+-------+| Field        | Type        | Null | Key | Default | Extra |+--------------+-------------+------+-----+---------+-------+| 学号         | varchar(64) | NO   | PRI | NULL    |       || 课程         | varchar(64) | NO   | PRI | NULL    |       || 课程成绩     | varchar(10) | YES  |     | NULL    |       |+--------------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)

拆完后，学生表满足2NF。大部分场景都够用了，但是拆分后的学生表在某些场景下依然存在冗余问题。比如新开了几个冷门系，没有招到任何学生，那学生表里就会被插入一些除了所属系名称
，系地址
，系电话
等的空记录。那针对这种场景，2NF暂时无法满足要求，需要更高级别的规范设计。

3NF：不存在非主键字段对主键字段的传递依赖。

3NF在2NF基础上拆的更细，消除了在满足2NF的表中非主键字段对主键字段的传递依赖关系。

拿拆分后的学生表来举例：非主键字段都依赖主键字段学号
，但是同时非主键字段系地址
和系电话
还同时依赖字段所属系名称
。所以此时存在这样的关系：

系地址
和系电话
依赖所属系名称
，同时所属系名称
在此表中又依赖学号
，所以此时学生表不满足3nf。为了满足3NF，我们再次对学生表做更进一步的拆分：

(debian-ytt1:3500)|(ytt)>desc `学生表`;+-----------------+------------------+------+-----+---------+-------+| Field           | Type             | Null | Key | Default | Extra |+-----------------+------------------+------+-----+---------+-------+| 学号            | varchar(64)      | NO   | PRI | NULL    |       || 姓名            | varchar(64)      | YES  |     | NULL    |       || 年龄            | tinyint unsigned | YES  |     | NULL    |       || 性别            | char(1)          | YES  |     | NULL    |       || 所属系名称      | varchar(64)      | YES  |     | NULL    |       |+-----------------+------------------+------+-----+---------+-------+5 rows in set (0.00 sec)

院系表：

(debian-ytt1:3500)|(ytt)>desc `院系表`;+--------+--------------+------+-----+---------+-------+| Field  | Type         | Null | Key | Default | Extra |+--------+--------------+------+-----+---------+-------+| 系名称   | varchar(64)  | NO   | PRI | NULL    |       || 地址   | varchar(100) | YES  |     | NULL    |       || 电话   | varchar(20)  | YES  |     | NULL    |       |+--------+--------------+------+-----+---------+-------+3 rows in set (0.00 sec)

拆分后，新学生表满足3NF。接下来把之前的员工表 employee 也改造成3NF，拆分成employee 和 dept 两张表。

(debian-ytt1:3500)|(ytt)>desc employee;+-----------------+-------------+------+-----+---------+-------+| Field           | Type        | Null | Key | Default | Extra |+-----------------+-------------+------+-----+---------+-------+| employee_number | varchar(64) | NO   | PRI | NULL    |       || employee_name   | varchar(64) | NO   |     | NULL    |       || base_salary     | varchar(30) | YES  |     | NULL    |       || extra_salary    | varchar(30) | YES  |     | NULL    |       || dept            | varchar(64) | YES  |     | NULL    |       |+-----------------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)(debian-ytt1:3500)|(ytt)>desc dept;+-----------+-------------+------+-----+---------+-------+| Field     | Type        | Null | Key | Default | Extra |+-----------+-------------+------+-----+---------+-------+| dept      | varchar(64) | NO   | PRI | NULL    |       || dept_desc | text        | YES  |     | NULL    |       |+-----------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)

重新拆分后，员工表也符合3NF。对于大部分场景来讲，3NF足以。

BCNF：在3NF基础上，消除联合主键中每个键之间的部分依赖与传递依赖关系。

BCNF，强调联合主键内的字段依赖关系。

比如上面已经满足3NF的学生表和员工表，假设有这种场景：

有部分学生可能就读多个系，那么学生和系是多对对的关系，把表主键由学号
改为联合主键（学号
,所属系名称
）

(debian-ytt1:3500)|(ytt)>alter table `学生表` drop primary key, add primary key (`学号`,` 所属系名 `);Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0

此时联合主键的两个字段互相依赖，符合3NF，但是不符合BCNF，有可能导致重复记录。

比如小李学习很好，又对很多其他的院系有兴趣，同时攻读物理系、数学系、心理学系，相关记录如下：

(debian-ytt1:3500)|(ytt)>select * from `学生表`;+------------+--------+--------+--------+-----------------+| 学号       | 姓名   | 年龄   | 性别   | 所属系名称      |+------------+--------+--------+--------+-----------------+| 2020100090 | 小李   |     21 | 男     | 心理学系        || 2020100090 | 小李   |     21 | 男     | 数学系          || 2020100090 | 小李   |     21 | 男     | 物理系          |+------------+--------+--------+--------+-----------------+3 rows in set (0.00 sec)

可以看到非主键字段有多个重复值！继续拆分学生表：

(debian-ytt1:3500)|(ytt)>desc `学生表`    -> ;+--------+------------------+------+-----+---------+-------+| Field  | Type             | Null | Key | Default | Extra |+--------+------------------+------+-----+---------+-------+| 学号   | varchar(64)      | NO   | PRI | NULL    |       || 姓名   | varchar(64)      | YES  |     | NULL    |       || 年龄   | tinyint unsigned | YES  |     | NULL    |       || 性别   | char(1)          | YES  |     | NULL    |       |+--------+------------------+------+-----+---------+-------+4 rows in set (0.00 sec)(debian-ytt1:3500)|(ytt)>desc `学生_系_关系表`;+-----------------+--------------+------+-----+---------+----------------+| Field           | Type         | Null | Key | Default | Extra          |+-----------------+--------------+------+-----+---------+----------------+| id              | int unsigned | NO   | PRI | NULL    | auto_increment || 学号            | varchar(64)  | YES  |     | NULL    |                || 所属系名称      | varchar(64)  | YES  |     | NULL    |                |+-----------------+--------------+------+-----+---------+----------------+3 rows in set (0.00 sec)

再看看改造后的两表记录：

(debian-ytt1:3500)|(ytt)>select * from `学生表`;+------------+--------+--------+--------+| 学号       | 姓名   | 年龄   | 性别   |+------------+--------+--------+--------+| 2020100090 | 小李   |     21 | 男     |+------------+--------+--------+--------+1 row in set (0.00 sec)(debian-ytt1:3500)|(ytt)>select * from  `学生_系_关系表`;+----+------------+-----------------+| id | 学号       | 所属系名称      |+----+------------+-----------------+|  1 | 2020100090 | 心理学系        ||  2 | 2020100090 | 数学系          ||  3 | 2020100090 | 物理系          |+----+------------+-----------------+3 rows in set (0.00 sec)

同样针对员工表，也存在这样的场景，比如有部分员工可能身兼数职，隶属于多个部门，改造方法类似。

(debian-ytt1:3500)|(ytt)>desc employee;+-----------------+-------------+------+-----+---------+-------+| Field           | Type        | Null | Key | Default | Extra |+-----------------+-------------+------+-----+---------+-------+| employee_number | varchar(64) | NO   | PRI | NULL    |       || employee_name   | varchar(64) | NO   |     | NULL    |       || base_salary     | varchar(30) | YES  |     | NULL    |       || extra_salary    | varchar(30) | YES  |     | NULL    |       |+-----------------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)(debian-ytt1:3500)|(ytt)>desc employee_vs_dept;+-----------------+--------------+------+-----+---------+----------------+| Field           | Type         | Null | Key | Default | Extra          |+-----------------+--------------+------+-----+---------+----------------+| id              | int unsigned | NO   | PRI | NULL    | auto_increment || employee_number | varchar(64)  | YES  |     | NULL    |                || dept            | varchar(64)  | YES  |     | NULL    |                |+-----------------+--------------+------+-----+---------+----------------+3 rows in set (0.01 sec)

4NF: 在3NF基础上，消除多值依赖关系。

比3NF更加精细，依然拿员工表来举例。比如一家外企，在中国有分公司，中国分公司的员工有中文名字，但是必须每天跟外国总部沟通，所以每个人取了一个英文名字，这时候员工表的主键就变为联合主键（employee_no,employee_name）一些示例数据如下：

(debian-ytt1:3500)|(ytt)>select * from employee;+-----------------+---------------+-------------+--------------+| employee_number | employee_name | base_salary | extra_salary |+-----------------+---------------+-------------+--------------+| 202010050       | lucy          | 50000       | 60000        || 202010050       | 小青          | 50000       | 60000        || 202010051       | simon         | 60000       | 20000        || 202010051       | 小张          | 60000       | 20000        |+-----------------+---------------+-------------+--------------+4 rows in set (0.00 sec)

从记录结果发现，其中薪水这两个字段有重复数据，员工表一般还有其他的很多字段，所以重复数据在这种场景下会更多。

那消除这种重复记录的方法就是再次拆分这张表，拆分为三张表，表结构如下：

(debian-ytt1:3500)|(ytt)>desc employee;+-----------------+-------------+------+-----+---------+-------+| Field           | Type        | Null | Key | Default | Extra |+-----------------+-------------+------+-----+---------+-------+| employee_number | varchar(64) | NO   | PRI | NULL    |       || base_salary     | varchar(30) | YES  |     | NULL    |       || extra_salary    | varchar(30) | YES  |     | NULL    |       |+-----------------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)(debian-ytt1:3500)|(ytt)>desc employee_zh;+-----------------+-------------+------+-----+---------+-------+| Field           | Type        | Null | Key | Default | Extra |+-----------------+-------------+------+-----+---------+-------+| employee_number | varchar(64) | NO   | PRI | NULL    |       || employee_name   | varchar(64) | NO   |     | NULL    |       |+-----------------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)(debian-ytt1:3500)|(ytt)>desc employee_en;+-----------------+-------------+------+-----+---------+-------+| Field           | Type        | Null | Key | Default | Extra |+-----------------+-------------+------+-----+---------+-------+| employee_number | varchar(64) | NO   | PRI | NULL    |       || employee_name   | varchar(64) | NO   |     | NULL    |       |+-----------------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)(debian-ytt1:3500)|(ytt)>select  * from employee;+-----------------+-------------+--------------+| employee_number | base_salary | extra_salary |+-----------------+-------------+--------------+| 202010050       | 50000       | 60000        || 202010051       | 60000       | 20000        |+-----------------+-------------+--------------+2 rows in set (0.00 sec)(debian-ytt1:3500)|(ytt)>select  * from employee_zh;+-----------------+---------------+| employee_number | employee_name |+-----------------+---------------+| 202010050       | 小青          || 202010051       | 小张          |+-----------------+---------------+2 rows in set (0.00 sec)(debian-ytt1:3500)|(ytt)>select  * from employee_en;+-----------------+---------------+| employee_number | employee_name |+-----------------+---------------+| 202010050       | lucy          || 202010051       | simon         |+-----------------+---------------+2 rows in set (0.00 sec)

此时这种场景下，冗余数据又被拆分掉了。