第33期:MySQL 表标准化设计

爱可生云数据库
English

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

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

数据库范式一般包含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: NULL
1 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。

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)

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

总结:

其实现实场景中,并不需要严格按照范式来设计表,有时候可能需要反着来。毕竟遵循的范式理论越高,表拆的就越细,检索表数据需要的表连接就越多,表连接可是开销很大的。


关于 MySQL 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧!

阅读 294

中国领先的企业数据处理技术整体解决方案提供商,开源数据库领域优秀企业。为大型行业用户的特定场景提...

242 声望
144 粉丝
0 条评论
你知道吗?

中国领先的企业数据处理技术整体解决方案提供商,开源数据库领域优秀企业。为大型行业用户的特定场景提...

242 声望
144 粉丝
文章目录
宣传栏