通过生动形象的例子理解三大范式、BCNF范式以及反范式化,简单易懂。
本文用生活中易于理解的概念,直观地描述这些范式和它们的应用场景,同时也给出了相应的实际数据库案例。

目录

  • 范式的概念
  • 1. 第一范式(1NF)
    • 1.1 类比举例:
    • 1.2 真实举例:
  • 2. 第二范式(2NF)
    • 2.1 类比举例:
    • 2.2 真实举例:
  • 3. 第三范式(3NF)
    • 3.1 类比举例:
    • 3.2 真实举例:
  • 4. BC范式(BCNF)
    • 4.1 类比举例:
    • 4.2 真实举例:
  • 5. 反范式化
    • 5.1 类比举例:
    • 5.2 真实举例:

范式的概念

首先,什么是范式,范式的英文是 Normal Form,其实就是标准形式的意思,就是说数据库就该设计成这样,这就是数据库的标准形式(当然,并不总是要遵守,有些时候可能需要反范式化)。

除了范式的定义,还需要先理解以下几个概念:

  • 属性: 把数据库中的一个表类比成一个公司,那么公司里的每个人都是一个“属性”(表中的一个字段视为一个属性),不管老板还是员工,只要是公司里的人,就都是一个属性。
  • 主键: 老板就是“主键”,所有普通员工都得听他的(主键可以唯一地标识属性元组)。但是,老板未必是一个人,可能在法律上我和我兄弟同时是公司的老板(主键可能是多个属性的组合)。要指挥员工,需要我们俩一起指挥才行(主键中所有属性的组合才能唯一地标识属性元组)。
  • 候选键: 公司不是独裁制的,老板这个位置有很多候选人,他们都有能力指挥所有普通员工(候选键都可以唯一标识属性元组),这些候选人就是“候选键”,但是所有候选人里只能选出一个做老板(主键是一个被选中的候选键),老板是特殊的候选人。当然,候选人可能也其实是几个人的组合,比如另外几个兄弟的组合,但他们也得一起行动才有能力指挥员工(候选键中所有属性的组合才能唯一地标识属性元组)。
  • 超键: 有候选人(包括老板)的一堆人就是“超键”(能唯⼀标识元组的属性集)。当然这堆人也可以只包含候选人或老板,这样也叫“超键”。
  • 主属性: 是候选人或老板的人(或是候选人组合中的一个)就是“主属性”(候选键中的属性称为主属性)。
  • 非主属性: 不是候选人或老板的人(也不在任何候选人组合中)就是“非主属性”(不属于任何候选码的属性称为非主属性)。
  • 外键: 公司里某个人(或某几个人的组合)是另一个公司的老板,那他或他们就是“外键”(一个表中存在的另一个表的主键称为此表的外键)。

这几个概念的正式定义是这样的:

  • 属性(attribute): 数据库中的字段,即数据库中表的列。
  • 超键(super key): 在关系中能唯⼀标识元组的属性集称为关系模式的超键
  • 候选键(candidate key): 不含有多余属性的超键称为候选键
  • 主键(primary key): ⽤户选作元组标识的⼀个候选键称为主键
  • 主属性: 候选键中的属性称为主属性。
  • 非主属性: 不属于任何候选键的属性称为非主属性。
  • 外键(foreign key): 在一个表中存在的另一个表的主键称为此表的外键。

注: 前面的类比定义中对几个概念的定义是乱序的,但这样更易于理解。

有了这几个概念和它们的类比,就可以开始描述三大范式、BC范式和反范式化的概念了。

1. 第一范式(1NF)

除非连体婴儿,否则各干各的:公司里的员工应该都是不可再分的,不是他们的肉体不可再分,而是他们的作用应该不可再分。
列要符合原子性,即表中的每个属性都不可再分

1.1 类比举例:

公司里有两个人,他们是搭档,一起面试一起进的公司,一个叫A ,一个叫B,他们说他俩不管干什么都得一起才行。
结果有一天,老板叫他俩去办公室,要给他俩安排一项工作,他俩一起去了之后发现这个工作其实只需要 A 自己就能做,并不需要 B 帮忙,于是 B 白跑一趟。
这就不满足第一范式了,显然 A 和 B 两个人是“可再分”的,不应该作为一个永不分离的组合存在。

这时候该怎么解决呢?
很简单,把 A 和 B 拆开变成两个员工就行了。

1.2 真实举例:

数据库中有如下一张表(其中主键为 姓名

姓名年龄地址
张三22广东省深圳市福田区
李四25江苏省南京市六合区

需求是经常要查询每个人所在的,注意,往往只需要知道省份,不需要知道详细地址。
但是每次查询都需要把整个详细地址取出来,再进行划分,从而得到需要的省份信息。
这就像是把 A 和 B 同时叫到办公室再让 B 回去,无疑是一种浪费。

因此,应该把省份、城市、区分开存储,避免多余的查询。

姓名年龄
张三22广东省深圳市福田区
李四25江苏省南京市六合区

2. 第二范式(2NF)

一山不容二虎:如果老板是多个人,他们的权力必须是同等的,不能有些员工只听一个老板的,而不用听另一个老板的。
非主属性必须依赖于整个主键或候选键,不能只依赖于主键或候选键的一部分属性

2.1 类比举例:

我和我兄弟同时是公司的老板,我俩本应该共同指挥员工,但是有几个员工只听我兄弟的,我根本管不着;同时又有几个员工只听我的,也不用听我兄弟的。
那这样我和我兄弟的权利就不是同等的了,公司就会出现分裂。

怎么解决呢?
一山不容二虎,我和我兄弟应该分家,听他话的员工跟他走,听我话的员工跟我走,变成两个公司。当然,我俩也不能彻底断了联系。

2.2 真实举例:

数据库中有如下一张表(其中主键为 员工ID 和 部门ID 的组合

员工ID姓名年龄部门ID部门名称部门地址员工在该部门中的工作年数
1001张三22304人力资源部广东省深圳市南山区3
1002李四25306业务部广东省深圳市福田区5

虽然 员工在该部门中的工作年数 需要 员工ID部门ID 组合在一起才能确定,但是 姓名年龄 只由 员工ID 就可以确定;而部门名称部门地址 只由 部门ID 就可以确定。
这样数据的存储就是冗余的,增删改查也可能会出现很多麻烦的问题。

因此,应该把这张表拆分成如下三张表:
员工 表

员工ID姓名年龄
1001张三22
1002李四25

部门 表

部门ID部门名称部门地址
304人力资源部广东省深圳市南山区
306业务部广东省深圳市福田区

员工-部门 表

员工ID部门ID员工在该部门中的工作年数
10013043
10023065

这样一来,即避免了数据冗余,又不会让员工与其所在的部门失去链接关系。

3. 第三范式(3NF)

不能架空老板:公司中不能有一个员工在听命于老板的同时,还听命于另一个员工。
任何非主属性不能依赖于其他非主属性

3.1 类比举例:

我作为公司的老板,指挥着公司的 100 个员工,可是居然有几个员工也同时听从着某一个员工的指挥。
这个员工成了中间管理者,让员工们不知道到底听老板的还是听他的,这是老板所不能接受的。

这时候怎么解决呢?
既然这个员工这么喜欢管人,那我干脆直接封他为“小组长”,让他去管理这些员工。
我则不再去管他们了,而是只管这个小组长就行了。

3.2 真实举例:

数据库中有如下一张表(其中主键为 员工ID

员工ID姓名年龄职位ID职位名称电话
1001张三2253HR123456789
1002李四2568后端开发工程师987654321

显然,职位名称 不仅可以由 员工ID 决定,也可以由 职位ID 决定,这造成了数据存储的冗余。

因此,应该把这张表拆分成如下两张表:
员工 表

员工ID姓名年龄职位ID电话
1001张三2253123456789
1002李四2568987654321

职位 表

职位ID职位名称
53HR
68后端开发工程师

4. BC范式(BCNF)

在野者不能干政:老板不能受到候选人的制约。
如果在关系R中,U为主键,A属性是主键的一个属性,若存在A->Y,Y为主属性,则该关系不属于BCNF

4.1 类比举例:

还是我和我兄弟同时是公司的老板,当时我们两人一起从众多候选人中被选中做了老板。
但是我发现我兄弟仍然和一个候选人有着千丝万缕的联系,他总是会受到那个候选人的影响,让我感觉那个候选人在“垂帘听政”。

怎么解决?
我不管理这个候选人了,也不能让他再影响到公司,我直接让他离开公司,然后让兄弟跟他私下保持联系。

4.2 真实举例:

数据库中有如下一张表(其中主键为 仓库名 和 物品名 的组合,同时 管理员 和 物品名 的组合是候选键 )

仓库名管理员物品名数量
北京仓张三iPhone XR10
北京仓张三iPhone 8P20
上海仓李四iPhone 830
上海仓李四iPhone 740

显然,管理员的名字可以决定仓库名,那么这个候选键中的主属性就影响到主键中的属性了。
主键中的主属性对于候选键是部分依赖关系,这可能导致插入、删除和更新数据时产生异常。

因此,应该把这张表拆分成如下两张表:
仓库 表

仓库名物品名数量
北京仓iPhone XR10
北京仓iPhone 8P20
上海仓iPhone 830
上海仓iPhone 740

仓库-管理员 表

仓库名管理员
北京仓张三
北京仓张三
上海仓李四
上海仓李四

5. 反范式化

尽信书不如无书:有时候,一味地追求符合范式,可能会导致数据库在查询时的时间开销很大、效率很低。
反范式是一种对范式化设计的数据库的性能优化策略,通过在表中增加冗余或重复的数据来提供数据库的读取性能

5.1 类比举例:

我是公司的老板,我手下有一个小组长,他的手下又有一些员工。
根据第三范式,我不直接管理小组长手下的员工,而是只跟小组长对接工作。
但是我实际上总是需要看小组长手下的一个员工的工作报告,但是我又不能直接跟他沟通,每次还都需要让小组长做中介,让那个员工先把报告给小组长,小组长再转给我,这很没有效率。

怎么解决这一问题呢?
我决定还是直接管理那个员工,虽然这样让我和小组长在同时管理他,不符合公司的规矩,但是确实有效地提高了我们的工作效率。

5.2 真实举例:

数据库中有如下一张表(它们的主键分别为 员工ID部门ID
员工 表

员工ID姓名年龄部门ID
1001张三22304
1002李四25306

部门 表

部门ID部门名称部门地址
304人力资源部广东省深圳市南山区
306业务部广东省深圳市福田区

如果在实际需求中要频繁地查询某个员工所在的部门的名称,比如调用如下 SQL 语句

select employee_id,department_namefrom employees e join departments d on e.department_id = d.department_id;

那么每次都需要进行两个表的连接操作,会浪费大量时间资源和计算资源。

因此,可以在员工表中增加一个冗余的字段 部门名称 ,这样每次的查询就可以直接获取所需信息,而不用进行连接操作了。
员工 表

员工ID姓名年龄部门ID部门名称
1001张三22304人力资源部
1002李四25306业务部