前言
上周我们分配任务分到了部分的订单的业务,涉及到一些建表的操作,于是连夜把一些之前总结的资料翻出来看哈哈哈,正好趁着晚上下班,把之前总结的一些内容分享出来。之前记得大学的时候有了解过,基本上创建数据库表都躲不过这个原则:就是数据库的三范式,那在建表的时候究竟要不要遵循这个三范式呢?今天就跟大家分享一下自己的见解。
数据库范式
我们设计关系数据库时,通常会有不同的考量,设计出合理的关系型数据库。那这就需要遵从不同的规范要求,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
范式:
范式来自英文Normal form,简称NF。要想设计—个好的关系,必须使关系满足一定的约束条件,此约束已经形成了规范,分成几个等级,一级比一级要求得严格。满足这些规范的数据库是简洁的、结构明晰的,同时,不会发生插入(insert)、删除(delete)和更新(update)操作异常。反之则是乱七八糟,不仅给数据库的编程人员制造麻烦,而且面目可憎,可能存储了大量不需要的冗余信息。
第一范式(1NF)
所有字段的值都是不可分解的原子值。即实体中的某个属性有多个值时,必须拆分为不同的属性。例如:
用户信息表
编号 | 姓名 | 年龄 | 地址 |
---|---|---|---|
1 | 小王 | 23 | 浙江省杭州市拱墅区湖州街51号 |
当实际需求对地址没有特定的要求下,这个用户信息表的每一列都是不可分割的。但是当实际需求对省份或者城市有特别要求时,这个用户信息表中的地址就是可以分割的,改为:
用户信息表
编号 | 姓名 | 年龄 | 省份 | 城市 | 区县 | 详细地址 |
---|---|---|---|---|---|---|
1 | 小王 | 23 | 浙江省 | 杭州市 | 拱墅区 | 湖州街51号 |
好处
- 表结构相对清晰
- 易于查询
第二范式(2NF)
所有非主键列必须全部依赖于全部主键。符合与不符合的场景如下:
案例如下:
学生课程表
学生编号 | 课程编号 | 学生名称 | 课程名称 | 所在班级 | 班主任 |
---|---|---|---|---|---|
S1 | C1 | 小王 | 计算机导论 | 计算机3班 | 陈老师 |
S1 | C2 | 小王 | 数据结构 | 计算机3班 | 陈老师 |
S2 | C1 | 小马 | 计算机导论 | 软件1班 | 李老师 |
将学生编号和课程编号作为主键,能确定唯一一条数据,但是学生名称只跟学生编号有关,跟课程编号无关,即不满足完全函数依赖,改为:
学生表
学生编号 | 学生名称 | 所在班级 | 班主任 |
---|---|---|---|
S1 | 小王 | 计算机3班 | 陈老师 |
S2 | 小马 | 软件1班 | 李老师 |
课程表
课程编号 | 课程名称 |
---|---|
C1 | 计算机导论 |
C2 | 数据结构 |
学生课程关系表
学生编号 | 课程编号 |
---|---|
S1 | C1 |
S1 | C2 |
S2 | C1 |
好处
- 相对节约空间,当学生表和课程表属性越多,效果越明显
- 解决插入异常,当新增一门课程时,原表因为没有学生选课,导致无法插入数据
- 解决更新繁琐,当更改一门课程名称时,原表要更改多条数据
- 解决删除异常,当学生学完一门课,原表若要清空学生上课信息,课程编号与课程名称的关系可能会丢失
第三范式(3NF)
在满足1NF和2NF的前提下,非主键列之间不存在间接依赖关系(消除传递函数依赖)。
案例如下:
学生表
学生编号 | 学生名称 | 班级编号 | 班级名字 |
---|---|---|---|
S1 | 小王 | 001 | 计算机1班 |
S2 | 小马 | 003 | 计算机3班 |
学生编号作为主键满足第二范式(2NF)。通过学生编号 ⇒⇒ 班级编号 ⇒⇒ 班级名字,所以班级编号和班级名字之间存在依赖关系,改为:
学生表
学生编号 | 学生名称 | 班级编号 |
---|---|---|
S1 | 小王 | 001 |
S2 | 小马 | 003 |
班级表
班级编号 | 班级名称 |
---|---|
001 | 计算机1班 |
003 | 计算机3班 |
好处
- 相对节约空间
- 解决更新繁琐
- 解决插入异常,当班级分配了老师,还没分配学生的时候,原表将不可插入数据
- 解决删除异常,当学生毕业后,若要清空学生信息,班级和老师的关系可能会丢失
巴斯-科德范式(BCNF)
在3NF基础上,主属性之间不存在部分或传递依赖。例如:
配件表
仓库号 | 配件号 | 职工号 | 配件数量 |
---|---|---|---|
W1 | P1 | E1 | 10 |
W1 | P2 | E1 | 10 |
W2 | P1 | E2 | 20 |
有以下约束:
- 一个仓库有多个职工
- 一个职工只在一个仓库
- 一种配件可以放多个仓库
- 一个仓库,一个职工管理多个配件,一种配件由唯一一个职工管理
由此,将(仓库号,配件号)作为主键,满足 3NF,但是(仓库号,配件号)⇒⇒ 职工号 ⇒⇒ 仓库号,造成传递函数依赖,改为:
仓库表
仓库号 | 职工号 |
---|---|
W1 | E1 |
W2 | E2 |
工作表
职工号 | 配件号 | 配件数量 |
---|---|---|
E1 | P1 | 10 |
E1 | P2 | 10 |
E2 | P1 | 20 |
好处
- 解决一些冗余和一些异常情况
不足
- 丢失一些函数依赖,如丢失(仓库号,配件号)⇒⇒ 职工号,无法通过单表来确定一个职工号
第四范式(4NF)
在BCNF基础上,需要消除多值依赖。
例如:
客户联系方式
客户编号 | 固定电话 | 移动电话 |
---|---|---|
10 | 88-123 | 151xxxxxxxx |
10 | 88-124 | 183xxxxxxxx |
一个用户拥有多个固定电话和移动电话,给表的维护带来很多麻烦。比如增加一个固定电话,那么移动电话这一栏就较难维护,改为:
客户电话表
客户编号 | 电话号码 | 电话类型 |
---|---|---|
10 | 88-123 | 固定电话 |
10 | 88-124 | 固定电话 |
10 | 151xxxxxxxx | 移动电话 |
10 | 183xxxxxxxx | 移动电话 |
好处
- 解决一些异常,使表结构更加合理
第五范式(5NF)
在4NF基础上,消除传递依赖。
例如:销售表
销售人员 | 供应商 | 产品 |
---|---|---|
S1 | V1 | P1 |
S2 | V2 | P2 |
S1 | V1 | P1 |
S2 | V2 | P2 |
要想找到某一条数据,必须以(销售人员,供应商,产品)为主键,改为:
销售人员_供应商表
销售人员 | 供应商 |
---|---|
S1 | V1 |
S2 | V2 |
销售人员_产品表
销售人员 | 产品 |
---|---|
S1 | P1 |
S2 | P2 |
供应商_产品表
供应商 | 产品 |
---|---|
V1 | P1 |
V2 | P2 |
好处
- 解决某些异常操作
注意事项
在实际应用中,并不是一定按照所谓的三范式来开发,按照三范式开发的优点很明确,就是数据不会有冗余,但是层级越高的范式所需要的创建的表也就越多,那在企业日常的业务中,如果严格遵循范式来做的话,必然会有多表查询,如果两张表甚至多张表的数据量都很大,那么关联查询会做笛卡尔积,因此产生的数据量是惊人的。对于整个数据库的效率和性能就会有很大的影响。所以一般都会在一些比较常用的关联字段中给放入一个表中,这样数据虽然有冗余,并且在插入更新时需要额外维护相关数据,但查询却因此变成了单表查询,数据量大大减少,因此查询效率会大幅度提升。所以需不需要遵循第三范式需要根据实际情况,既不能明确不遵循,也不能死板地遵循。