五分钟,带你理解数据库三大范式
概述
第一范式(1NF):强调的是列的原子性,即列不能够再分成其他几列
第二范式(2NF):在1NF的基础上,所有非主键属性必须全部依赖主键
第三范式(3NF):在2NF的基础上,所有非主键属性必须直接依赖主键,即不能存在传递依赖
第一范式(1NF)举例
用户ID | 用户名 | 性别 | 个人信息 |
---|---|---|---|
zhangSan | 张三 | 男 | 手机号:18xxx; 职务:蓝领 |
wangWu | 王五 | 女 | 手机号:15xxx;职务:白领 |
上表中,个人信息字段就不满足第一范式,还可以拆分成手机号和职务,可调整如下
用户ID | 用户名 | 性别 | 手机号 | 职务 |
---|---|---|---|---|
zhangSan | 张三 | 男 | 18xxx | 蓝领 |
wangWu | 王五 | 女 | 15xxx | 白领 |
调整后每列不可再拆分,满足第一范式
第二范式(2NF)举例
满足1NF的前提下,所有非主键属性必须全部依赖主键
用户ID | 用户名 | 性别 | 手机号 | 角色ID | 角色名称 | 角色说明 | 部门ID | 部门名称 |
---|---|---|---|---|---|---|---|---|
zhangSan | 张三 | 男 | 18xxx | R001 | PM | 项目经理角色 | BM001 | 研发部 |
zhangSan | 张三 | 男 | 18xxx | R002 | 开发 | 研发角色 | BM001 | 研发部 |
zhangSan | 张三 | 男 | 18xxx | R003 | 测试 | 测试角色 | BM001 | 研发部 |
liSi | 李四 | 男 | 18xxx | R002 | 开发 | 开发角色 | BM001 | 研发部 |
wangWu | 王五 | 女 | 15xxx | R004 | HR | 人力资源角色 | BM002 | 人力资源部 |
上表中, 由于一个用户可能有多个角色,所以要用【用户ID,角色ID】作为主键,该表存在以下依赖关系:
- (用户名,性别,手机号,部门ID,部门名称) 依赖 用户ID
- (角色名称,角色说明) 依赖 角色ID
虽然表中每列属性不可再分割,但是非主键属性没有全部依赖主键属性,这将存在以下问题:
- 数据冗余:
- 当一个角色被n个人选择,角色说明冗余了n次;
- 当一个人拥有m个角色,用户名,性别,手机号,部门ID,部门名称字段冗余了m-1次;
- 更新复杂:
- 当一个角色被n个人选择,如果要调整角色说明字段,表中的多行数据要进行更新
- 当一个人拥有m个角色,如果要更新用户手机号,表中的多行数据要进行更新
- 删除异常
- 当某个用户注销了,删除用户时,如果角色ID只被一个用户使用,角色信息也一并被删除了。
综上,不满足第二范式的情况一般是同张表中存在一对多的数据,此时需要复合主键,造成数据冗余,比如用户有多个角色,学生选多个课程
解决办法: 增加子表,维护数据间的关系
上述例子中,可以增加角色表,用户角色表,调整后如下
- 用户表
用户ID | 用户名 | 性别 | 手机号 | 部门ID | 部门名称 |
---|---|---|---|---|---|
zhangSan | 张三 | 男 | 18xxx | BM001 | 研发部 |
liSi | 李四 | 男 | 18xxx | BM001 | 研发部 |
wangWu | 王五 | 女 | 15xxx | BM002 | 人力资源部 |
- 角色表
角色ID | 角色名称 | 角色说明 |
---|---|---|
R001 | PM | 项目经理角色 |
R002 | 开发 | 研发角色 |
R003 | 测试 | 测试角色 |
R004 | HR | 人力资源角色 |
- 用户角色表
用户ID | 角色ID |
---|---|
zhangSan | R001 |
zhangSan | R002 |
zhangSan | R003 |
liSi | R001 |
wangWu | R004 |
第三范式(3NF)举例
满足2NF的前提下,所有非主键属性必须直接依赖主键,即不能存在传递依赖
- 用户表
用户ID | 用户名 | 性别 | 手机号 | 部门ID | 部门名称 |
---|---|---|---|---|---|
zhangSan | 张三 | 男 | 18xxx | BM001 | 研发部 |
liSi | 李四 | 男 | 18xxx | BM001 | 研发部 |
wangWu | 王五 | 女 | 15xxx | BM002 | 人力资源部 |
接第二范式举例中调整后的用户表,上表中还存在如下依赖关系:
- (用户名,性别,手机号,部门ID) 直接依赖 用户ID
- (部门名称) 直接依赖 部门ID
- 由于部门ID 依赖 用户 ID, 部门名称 依赖 部门ID, 所以 部门名称 间接依赖 用户ID
这将存在以下问题:
- 数据冗余:
- 当多个人属于同一个部门,部门名称字段冗余了多次;
- 更新复杂:
- 当多个人属于同一个部门,如果要更新部门名称,表中的多行数据要进行更新
- 删除异常
- 当上表中wangWu用户注销了,删除用户时,部门BM002信息也被删除。
不满足第三范式,一般是表中冗余了基础表(表之间的数据关系是一对一的)的非主键信息,造成部分字段冗余。
解决办法: 增加基础表,在基础表中维护部门名称等信息
上述例子中,可以增加部门表,调整后如下
- 用户表
用户ID | 用户名 | 性别 | 手机号 | 部门ID |
---|---|---|---|---|
zhangSan | 张三 | 男 | 18xxx | BM001 |
liSi | 李四 | 男 | 18xxx | BM001 |
wangWu | 王五 | 女 | 15xxx | BM002 |
- 部门表
部门ID | 部门名称 |
---|---|
BM001 | 研发部 |
BM002 | 人力资源部 |
结语
实际应用场景中,一般满足第二范式即可,没必要生搬硬套满足所有范式要求;是否要满足第三范式应根据实际业务以及性能效率方面去综合考虑。比如对一些几乎不会修改的基础表数据字段,那完全可以进行冗余,减少表关联,提高查询效率。