MySQL数据库设计之范式

数据库设计之范式

1. 范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

范式来自英文Normal form，简称NF。要想设计—个好的关系，必须使关系满足一定的约束条件，此约束已经形成了规范，分成几个等级，一级比一级要求得严格。满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入(insert)、删除(delete)和更新(update)操作异常。反之则是乱七八糟，不仅给数据库的编程人员制造麻烦，而且面目可憎，可能存储了大量不需要的冗余信息。

关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了。

1. 第一范式

所谓第一范式（1NF）主要是确保数据表中每个字段的值都具有原子性，也就是说表中每个字段不能再被拆分。这个范式一定需要遵守。

例如某个字段user_info，包含了家庭住址，邮箱，电话，这显然是不可以的，需要将user_info拆分成对应的三个字段。

但是原子性事实上是主观的，例如姓名name可能有firstName，lastName，那是否需要拆分，再例如是否需要将地址拆分成省份、区域等，这取决于应用程序是否需要查询到哪种粒度。

2. 第二范式

在满足第一范式的基础上，还要满足数据库表中的每一条数据，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。那么在创建数据表的时候我们就需要

正确示例：
成绩表（学号，课程号，成绩）关系中，（学号，课程号）可以决定成绩，但是学号不能决定成绩，课程号也不能决定成绩，所以（学号，课程号）和成绩就是完全依赖关系。

错误示例：
比赛表，里面包含了球员编号，姓名，年龄，比赛编号，比赛时间，比赛场地等属性，由于单单球员编号和比赛编号都无法确定唯一一条数据，因此需要将球员编号和比赛编号联合作为主键：

(球员编号，比赛编号) --> (姓名，年龄，比赛时间，比赛场地，得分)

但是这个表并不满足第二范式，因为数据表中的字段并不满足完全依赖主键的条件：

(球员编号)  --> (姓名，年龄)
(比赛编号)  --> (比赛时间，比赛场地)

不满足第二范式的问题：

1. 数据冗余：如果一个球员参加了n场比赛，那么球员的姓名和年龄就重复了n次，一个比赛也有可能有m个球员参加，那比赛的时间和地点就重复了m次。
2. 插入异常：如果我们想要添加一场新的比赛，但是这时球员还没有确定，那么就无法插入。
3. 删除异常：如果我们想要删除某个球员编号，会将比赛信息删除掉。
4. 更新异常：如果我们想要调整某个比赛的时间，那么数据库表中所有关于这个比赛的时间都需要进行调整，否则会出现一场比赛时间不同的情况。

因此为了避免上面的问题，我们可以将上面球员比赛表设计成三张表，这样每张表都符合了第二范式

球员表：球员编号，姓名，年龄等
比赛表：比赛编号，比赛场地，比赛时间等
球员比赛关系表：球员编号，比赛编号，得分等

3. 第三范式

在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段字段。（即不能存在非主属性A依赖非主属性B，非主属性B依赖于主键C的情况），通俗来说，该规则的意思是所有非主键属性之间不能有依赖关系，必须相互独立。

举例：

员工信息表：员工编号，姓名，部门编号，部门名称。

上面的员工信息表是符合第二范式的，因为姓名，部门编号，部门名称都完全依赖员工编号这个主键，但是并不符合第三范式，因为有非主键字段 部门名称 依赖于非主键字段 部门编号。因此需要将部门编号，部门名称再抽取成一张表。

4. 巴斯-科德范式

人们在3NF的基础上进行了改进，提出了巴斯范式（BCNF）。

若一个关系达到了第三范式，并且它只有一个候选键，或者它每个候选键都是单属性，则为巴斯范式。简单来说就是主属性和其他主属性存在依赖关系。

案例分析：

在这个表中，一个仓库只有一个管理员，并且一个管理员只管理一个仓库。

进行分析可知，（仓库名，物品名）或者（管理员，物品名）可以决定数量，因此（仓库名，物品名）或者（管理员，物品名）就是这个表的候选键。

符合第一范式：所有字段都是原子性的。
符合第二范式：表中非主属性 “数量” 完全依赖两个候选键
符合第三范式：并不存在非主属性依赖于非主属性。

存在的问题：

增加一个仓库，但是还没有存放物品，由于主键（仓库名，物品名）不能有空值存在，因此会插入异常。
如果仓库的物品全部卖完了，那么会导致仓库名和管理员名称也会随之删除。
如果仓库的管理员更换了，那么会有多条记录都需要更改。
因此，即便表符合了3NF，但是还是可能存在插入，删除，更新异常的问题。

问题出现的原因：

主属性仓库名对于候选键（管理员，物品名）有部分依赖，这样就导致有可能出现上面的问题，因此引入BCNF，它在3NF的基础上消除了主属性对候选键的部分依赖或者传递性依赖。

如何解决：

将仓库名和管理员拆分出来形成一张表，然后（仓库名，物品名，数量）形成一张库存表。

一般来说，数据库设计达到第三范式或巴斯范式就可以了。

4. 第四范式

如果表中存在多个 1对多关系 时，需要进行拆分。
例如有职工表（职工编号，职工孩子，职工选修课程），在这张表中，每个职工可能有多个孩子，也有可能有多个选修课程，因此需要进行拆分成两张表：职工表一（职工编号，职工孩子），职工表二（职工编号，职工选修课程）。

5. 第五范式

第五范式（5NF）：是最终范式。消除了4NF中的连接依赖。
第五范式有以下要求：

• 必须满足第四范式
• 表必须可以分解为较小的表，除非那些表在逻辑上拥有与原始表相同的主键。

第五范式是在第四范式的基础上做的进一步规范化。第四范式处理的是相互独立的多值情况，而第五范式则处理相互依赖的多值情况。

有一个销售信息表sales（salepersion，vendor，product）。
salepersion代表销售人员，vendor代表供和商，product则代表产品。在某些情况下，这个表中会产生一些冗余。可以将表分解为

persion_vendor表(salepersion，vendor)
persion_product表(salepersion，product)
vendor­_product表(vendor，product)

2. 反范式化

有时候为了性能，并不一定会完全遵守范式标准。

示例：

有员工表employee，和部门表department，如果经常需要查询员工的部门名称，并且员工很多，那么可以考虑在员工表中添加部门名称 这一冗余字段：

select emp_id, dept_name
from employee e join department d
on e.dept_id = d.dept_id;

反范式的问题：

• 存储空间变大了
• 一个表中的字段做了修改，另一个表中的冗余字段也需要做相应的修改。
• 在数据量小的情况下，反范式反而不能体现性能上的优势，可能还会让数据库的设计变复杂。

反范式的使用场景：

• 当加上冗余信息后能够大幅度提高查询效率
• 这个冗余字段不需要经常修改

3. 总结

• 第一范式：确保每列的原子性
• 第二范式：非主键列完全依赖着主键列
• 第三范式：非主键列之间不存在依赖关系

范式的目的是为了降低数据的冗余，缺点是可能会降低了查询效率，因为范式等级越高，设计出来的表就越多，越精细，进行查询时就可能需要关联多张表。

实际上设计数据库时，并非会完全遵守这些标准，经常会为了性能违反范式原则，通过增加冗余的数据来提高数据库的性能。