Java OpenResty Spring Spring Boot MySQL Redis MongoDB PostgreSQL Linux Android Nginx 面试 小程序 Arthas JVM AQS juc Kubernetes Docker DevOps


数据库表的设计规范-范式

SQL 面试 大约 2307 字

简介

Normal Form,简称NF

在关系型数据库中,关于数据表设计的基本原则、规则就称为范式。

一张数据表的设计结构需要满足的某种设计标准的级别

范式设计越高阶,冗余度越低,同时高阶的范式一定符合低阶范式的要求。

一般数据库表设计中普遍要求设计到第三范式,最高遵循到巴斯范式。

有时候为了提高某些查询性能,我们还需要破坏范式规范,也就是反范式

按照范式级别,从低到高:

  • 第一范式:1NF
  • 第二范式:2NF
  • 第三范式:3NF
  • 巴斯-科德范式:BCNF,也叫巴斯范式
  • 第四范式:4NF
  • 第五范式:5NF,又称完美范式

优缺点

有点

数据的标准化有助于消除数据库中的数据冗余,第三范式通常被认为在性能、扩展性和数据完整性方面达到了最好的平衡。

缺点

可能会降低查询的效率。可能需要关联多张表,可能使一些索引策略失效。

键和属性

  • 超键:能唯一标识一行记录的属性集叫做超键。
  • 候选键:如果超键不包括多余的属性,那么这个超键就是候选键。
  • 主键:用户可以从候选键中选择一个作为主键。
  • 外键:如果数据表R1中的某属性集不是R1的主键,而是另一个数据表R2的主键,那么这个属性集就是数据表R1的外键。
  • 主属性:包含在任一候选键中的属性称为主属性。
  • 非主属性:与主属性相对,指的是不包含在任何一个候选键中的属性。

举例

球员表:球员编号|姓名|身份证号|年龄|球队编号

球队表:球队编号|主教练|球队所在地

  • 超键:对于球员表来说,超键就是包括球员编号或者身份证号的任意组合,比如:(球员编号)(球员编号,姓名)(身份证号,年龄)等。
  • 候选键:就是最小的超键,对于球员表来说,候选键就是(球员编号)或者(身份证号)
  • 主键:我们自己选定,也就是从候选键中选择一个,比如:(球员编号)
  • 外键:球员表中的球队编号。
  • 主属性:在球员表中,主属性是(球员编号)(身份证号)
  • 非主属性:在球员表中,除了两个主属性外,其他属性都是非主属性(姓名)(年龄)(球队编号)

第一范式

第一范式主要是确保数据表中每个字段的值必须具有原子性,也就是说数据表中每个字段的值为不可再次拆分的最小数据单元。

属性的原子性是主观的。如收获地址可以是一列,也可以拆为多列,按需要来拆分。

举例

用户表:user_info包含:真实姓名、电话、住址

id|username|password|user_info

拆分user_info

id|username|password|real_name|phone|address

第二范式

在满足第一范式的基础上,还要满足数据表里的每一条数据记录,都是可唯一标识的。而且所有非主键字段,都必须完全依赖主键,不能只依赖主键的一部分。

第二范式可理解为:一张表就是一个独立的对象。

举例一

成绩表:(学号,课程号)可以决定成绩,但是学号不能决定成绩,课程号也不能决定成绩,所以(学号,课程号)->成绩就是完全依赖关系。

学号|课程号|成绩

举例二

比赛表:候选键和主键都是(球员编号,比赛编号)

球员编号|比赛编号|姓名|年龄|比赛时间|比赛场地|得分

但这个表不满足第二范式,因为数据表中的字段之间还存在着如下的对应关系

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

为了满足第二范式,可改为三张表

球员表:球员编号|姓名|年龄
比赛表:比赛编号|比赛时间|比赛场地
球员比赛关系表:球员编号|比赛编号|得分

第三范式

第三范式是在第二范式的基础上,确保数据表中的每一个非主键字段都和主键字段直接相关。

要求数据表中的所有非主键字段不能依赖于其他非主键字段。

举例

部门信息表

部门编号|部门名称|部门简介

员工信息表

员工信息表中不能再将部门名称、部门简介等与部门相关的信息加入员工信息表中。否则会造成部门名称依赖部门编号,非主属性依赖了非主属性。

员工编号|姓名|部门编号

反范式

有时候不能简单按照规范要求设计数据库表,因为数据看似冗余,但对业务来说十分重要。

遵循业务优先的原则,首先满足业务需求,再尽量减少冗余。

如果数据库中的数据量比较大,系统的访问频次比较高,完全按照三大范式设计数据库表,读取数据时会产生大量的关联查询,在一定程度上会影响数据库的读性能。可以通过在数据库表中增加冗余字段来提供数据库的读性能。

举例

如果需要经常关联查询(员工姓名,部门名称),可以在员工表中增加冗余字段:部门名称

员工表

员工编号|姓名|年龄|部门编号

部门表

部门编号|部门名称|部门简介

可能带来的问题

  • 存储空间变大了
  • 一个表中字段做了修改,另一个表中冗余的字段也需要做同步修改,否则数据不一致
  • 数据量小的情况下,反范式不能体现性能的优势,可能还会让数据库的设计更加复杂

适用场景

  • 冗余字段不需要经常修改
  • 冗余字段查询的时候不可或缺
  • 历史快照、历史数据。如:每次发生的订单收货信息都属于历史快照,需要进行保存

巴斯范式

巴斯-科德范式:Boyce-Codd Normal Form,简称:BCNF

第三范式的基础上进行了改进。称为修正的第三范式或扩充的第三范式,但不称为第四范式。

若达到了第三范式,且他只有一个候选键,或者他的每个候选键都是单属性的,则达到巴斯范式。

第四范式

在满足巴斯范式的基础上,把同一表内的多对多关系删除。

第五范式

在满足第四范式的基础上,消除不是由候选键所蕴含的连接依赖。如果关系模式R中的每一个连接依赖均由R的候选键所隐含,则称此关系模式符合第五范式。

处理的是无损连接问题,基本没有实际意义。

阅读 364 · 发布于 2022-05-06

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb

扫描下方二维码关注公众号和小程序↓↓↓

扫描二维码关注我
昵称:
随便看看 换一批