数据库设计之范式
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. 第二范式
在满足第一范式的基础上,还要满足数据库表中的每一条数据,都是可唯一标识的。而且所有非主键字段,都必须完全依赖主键,不能只依赖主键的一部分。那么在创建数据表的时候我们就需要
正确示例:
成绩表(学号,课程号,成绩)关系中,(学号,课程号)可以决定成绩,但是学号不能决定成绩,课程号也不能决定成绩,所以(学号,课程号)和成绩就是完全依赖关系。
错误示例:
比赛表,里面包含了球员编号,姓名,年龄,比赛编号,比赛时间,比赛场地等属性,由于单单球员编号和比赛编号都无法确定唯一一条数据,因此需要将球员编号和比赛编号联合作为主键:
(球员编号,比赛编号) --> (姓名,年龄,比赛时间,比赛场地,得分)
但是这个表并不满足第二范式,因为数据表中的字段并不满足完全依赖主键的条件:
(球员编号) --> (姓名,年龄)
(比赛编号) --> (比赛时间,比赛场地)
不满足第二范式的问题:
- 数据冗余:如果一个球员参加了n场比赛,那么球员的姓名和年龄就重复了n次,一个比赛也有可能有m个球员参加,那比赛的时间和地点就重复了m次。
- 插入异常:如果我们想要添加一场新的比赛,但是这时球员还没有确定,那么就无法插入。
- 删除异常:如果我们想要删除某个球员编号,会将比赛信息删除掉。
- 更新异常:如果我们想要调整某个比赛的时间,那么数据库表中所有关于这个比赛的时间都需要进行调整,否则会出现一场比赛时间不同的情况。
因此为了避免上面的问题,我们可以将上面球员比赛表设计成三张表,这样每张表都符合了第二范式
球员表:球员编号,姓名,年龄等
比赛表:比赛编号,比赛场地,比赛时间等
球员比赛关系表:球员编号,比赛编号,得分等
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. 总结
- 第一范式:确保每列的原子性
- 第二范式:非主键列完全依赖着主键列
- 第三范式:非主键列之间不存在依赖关系
范式的目的是为了降低数据的冗余,缺点是可能会降低了查询效率,因为范式等级越高,设计出来的表就越多,越精细,进行查询时就可能需要关联多张表。
实际上设计数据库时,并非会完全遵守这些标准,经常会为了性能违反范式原则,通过增加冗余的数据来提高数据库的性能。