外行人都能看懂的技术文章系列——数据库范式1NF,2NF,3NF,BCNF,4NF,5NF,6NF举例详解

数据库范式1NF,2NF,3NF,BCNF,4NF,5NF,6NF举例详解

目录

1. 致读者

2. 基本概念（作者自己的定义）

3. 本文范例场景

4. 正文

第一范式（1NF）

第二范式（2NF）

第三范式（3NF）

BC范式（BCNF）

第四范式（4NF）

第五范式（5NF）

第六范式（6NF）

总结

补充说明

---

1. 致读者

此文章是作者首次写作的技术文章，内容都是自己的理解，其中一定有不少错误，如果您有不赞同或者不理解的地方，恳请您提出宝贵意见！

2. 基本概念（作者自己的定义）

数据库：就是用来存储数据并提供增删改查功能的一种产品。

关系型数据库：这是众多种类数据库中的现在最流行的一种，这种数据库把数据存储在表（Table）中，表里的每一行称之为一个记录（Record），每一列称之为一个属性（Column）。每张表又被称作一种关系（Relation）。

数据库范式（Normalization Form， 简称NF）：关系型数据库的一张表中属性与属性之间是有约束关系的。范式就是对这种约束关系达到的规范程度的一种描述。目前根据规范程度的递进关系排有1NF、2NF、3NF、BCNF、4NF、5NF、6NF这七个级别， 其中BCNF是3NF的一个加强版。

主属性：可以用来唯一确定一条记录的属性。主属性可以是单个属性，也可以是多个属性。主属性之外的属性称作非主属性。

冗余：就是不必要的存储开销，包括保存在数据库表里的数据和查询出来的结果表数据都可能导致冗余，比如同样的数据被保存多份，或者一次查询出来的结果包含一些不需要使用的数据。

3. 本文范例场景

为了让大家特别是外行人都能很好的理解和应用这七种范式，我用以下场景的数据为例：

现实中的一个求职者本身带有很多属性，比如求职者编号（id)、用户名（user_id)、名字（name）、年龄（age)、性别（gender），手机号（mobile）、住址（address）、婚姻状况（marriage）、教育背景（education）、应聘职位（position）、应聘部门(department)、部门经理（manager）等等。以下正文均部分使用这个数据举例。

如果不需要遵守任何范式，那我们可以用一张表就把所有以上属性都包括了，如下表：

Candidate
id	name	age	gender	mobile	address	position	department
id1	name1	age1	gender1	mobile1	city1,street1	postion1	department1
id2	name2	age2	gender2	mobile2	city2,street2	position2	department2
id3	name3	age3	gender3	mobile3	city3,street3	position3	department3

从这张表上你一下看不出有任何问题，因为数据没有具体化，而且没有考虑在软件系统运行阶段会出现什么状况。

4. 正文

第一范式（1NF）

那我们现在首先来理解一下什么是第一范式（1NF）。

1NF是关系型数据的最低要求：业务上属性不需要再分。

举个例子，假设业务上我们需要区分求职者是本市的求职者还是一个外地的求职者以便给他们提供不同的面试安排比如线上还是线下的面试。表Candidate中address这个属性中包含了城市（city）和街道（street），业务上需要再分，所以表Candidate不符合1NF。我们可以把它改造为下表以满足1NF：

Candidate_1NF
id	name	age	gender	mobile	city	street	position	department
id1	name1	age1	gender1	mobile1	city1	street1	position1	department1
id2	name2	age2	gender2	mobile2	city2	street2	position2	department2
id3	name3	age3	gender3	mobile3	city3	street3	position3	department3

注意这里我特别强调要业务上有需要才拆分，假如没有业务上的需求，我们完全可以像表Candidate那样把city和street当作完整的address保存在一个属性里仍然满足1NF。可见是否符合某个范式跟具体的业务逻辑也是紧密相关的。

以上例子我着重分析了address这个属性是否满足1NF。同样的分析适用于所有的属性，如果所有属性都满足了1NF，则我们说这个表满足1NF。如果某个数据库中所有表都满足了1NF，则我们说这个数据库满足1NF。

为什么我们说1NF是关系型数据的最低要求呢？还是以刚才的业务场景来举例，假如像表Candidate那样把city和street当作完整的address保存在一个属性里， 业务上我们需要区分求职者是本市的求职者还是一个外地的求职者以便给他们提供不同的面试安排比如线上还是线下的面试。那会导致什么呢？是不是会导致我们的代码从数据库读取到address之后还需要解决如何从address里面截取出city的信息问题？而这个问题在使用表Candidate_1NF的设计的时候就很自然地解决了？数据库的存在除了有保存数据的作用，还要和业务逻辑相配合，便于代码的实现，不是吗？否则和把数据直接存在文件里面有什么区别呢？这样一分析你就可以理解为什么我说1NF是关系型数据的最低要求了吧！

第二范式（2NF）

接下来我们来看看第二范式（2NF）的例子。

2NF是关系型数据的必要要求：在1NF的基础上，非主属性都要全部主属性来唯一确定，而不可以由部分主属性就可以确定。

那么什么是主属性（Primary Key，简称PK），什么是非主属性呢？在我们定义一张表的时候往往需要指定由那个或者哪些属性来唯一确定一条记录，这个或者这些被指定用来唯一确定一条记录的属性就称作主属性，从这个表述中你不难看出主属性可以是一个属性，也可以是多个属性。主属性之外的所有其他属性都称为非主属性。

如果某个表没有任何属性被指定为主属性，那它一定满足2NF（因为根本不存在主属性了嘛，更不可能有部分主属性可以确定非主属性的问题了嘛）。

如果某个表只有一个属性被指定为主属性，从以上2NF的条件容易判断它一定满足2NF（因为根本不存在部分主属性的问题了嘛）。

那么，接下来我来举个多个属性被指定为主属性，而且不满足2NF的例子，如下表：

Candidate_1NF_02
id(PK)	name	age	gender	mobile	address	position(PK)	department
id1	name1	age1	gender1	mobile1	address1	postion1	department1
id2	name2	age2	gender2	mobile2	address2	position2	department2
id3	name3	age3	gender3	mobile3	address3	position3	department3
id1	name1	age1	gender1	mobile1	address1	position2	department2

对比表Candidate_1NF_02和Candidate，你可以发现除了Candidate_1NF_02指定了id和position作为主属性（PK）之外，id1这个求职者还同时应聘了position1和position2。观察发现，name， age，gender， mobile是可以由id唯一确定的，而department是可以由position唯一确定的，这就不满足2NF的要求了。

进一步思考发现，这张表之所以不得不指定多个主属性，是因为如果只指定id做主属性或者只指定position做主属性，最后一条记录就插不进去了（因为主属性的记录在该表上必须是唯一的）。同时进一步观察还发现，这导致了id1的name， age，gender， mobile被重复存在两条记录上，position2的department也被重复存在两条记录上，这在专业上称作数据冗余（不必要的数据被重复的意思），如果这样的数据冗余大量发生，必然浪费很多存储空间。

那么为了满足2NF，我们不妨把表Candidate_1NF_02分拆成以下三个表：

Candidate_2NF
id(PK)	name	age	gender	mobile	address
id1	name1	age1	gender1	mobile1	address1
id2	name2	age2	gender2	mobile2	address2
id3	name3	age3	gender3	mobile3	address3

Position_2NF
position(PK)	department
postion1	department1
position2	department2
position3	department3

Candidate_Position_2NF
id	position
id1	postion1
id2	position2
id3	position3
id1	position2

经过改造，你会发现表Candidate_2NF和Position_2NF都只指定了一个属性为主属性，而表Candidate_Position_2NF则没有指定任何属性为主属性，它们都满足了2NF。进一步观察发现，经过改造，id1的name， age，gender， mobile以及position2的department都只被保存在一条记录上，这样数据冗余导致存储空间浪费的问题也得到了解决。

进一步思考还发现，改造前如果求职者记录被删掉，该求职者应聘的职位信息也会被删掉，如果某个职位的最后一个求职者被不小心删掉了，该职位的信息也会无意中丢失了（不是删除求职者信息的本意），导致该职位又来了一个新的求职者时找不到这个职位的position和department了，不得不重建数据。改造之后我们可以大胆从表Candidate_2NF和表Candidate_Position_2NF删除求职者信息而不会丢失表Position_2NF中职位信息了。

总结一下，2NF就是为了减少数据冗余要求把一个大表分拆为多个小表同时也避免了数据的无意识丢失。

第三范式（3NF）

接下来我们来看看第三范式（3NF）的例子。

3NF是关系型数据的另一个必要要求：在2NF的基础上，非主属性都要由主属性才能唯一确定，而不可以出现由另一个或者另一些非主属性也可以唯一确定的情况。

如果某个表只有一个非主属性，从以上2NF的条件容易判断它一定满足3NF（因为根本不存在另一个或者另一些非主属性了嘛）。

那么，接下来我来举个多个非主属性，而且不满足3NF的例子，如下表：

Position_2NF_02
position(PK)	department	manager
postion1	department1	manager1
position2	department2	manager2
position3	department3	manager3
position4	department1	manager1

对比表Postion_2NF_02和Postion_2NF，你可以发现除了Postion_2NF_02多了manager属性之外，position4这个职位也是来自于department1。观察发现，manager是可以由department这个非主属性唯一确定的，这就不满足3NF的要求了。进一步观察还发现，department1的manager被重复存在两条记录上，也就是发生了数据冗余，如果这样数据冗余大量发生，必然浪费很多存储空间。

那么为了满足3NF，我们不妨把表Postion_2NF_02分拆成以下两个表：

Position_3NF
position(PK)	department
postion1	department1
position2	department2
position3	department3
position4	department1

Department_3NF
department(PK)	manager
department1	manager1
department2	manager2
department3	manager3

经过改造，你会发现表Position_3NF和Department_3NF都只有一个非主属性，它们都满足了3NF。进一步观察发现，经过改造，department1的manager只被保存在一条记录上，这样数据冗余导致存储空间浪费的问题也得到了解决。

进一步思考还发现，改造前如果职位记录被删掉，该职位的部门信息也会被删掉，如果某个部门开放的的最后一个职位记录被不小心删掉了，该部门的信息也会无意中丢失了（不是删除职位信息的本意），导致该部门又要开放新的职位时找不到这个部门的department和manager了，不得不重建数据。改造之后我们可以大胆从表Position_3NF删除求职者信息而不会丢失表Department_3NF中的部门信息了。

总结一下，3NF也是为了减少数据冗余要求把一个大表分拆为多个小表同时也避免了数据的无意识丢失。

BC范式（BCNF）

接下来我们来看看BC范式（BCNF）的例子。

BCNF是关系型数据的另一个必要要求：在3NF的基础上，主属性都要由全部主属性才能唯一确定，而不可以出现由部分主属性也可以唯一确定，或者由另一个或者另一些非主属性唯一确定的情况。

通过以上描述，可以看出BCNF是对2NF和3NF的一个加强，2NF和3NF都只约束非主属性，而没有对主属性进行约束，BCNF则是对主属性进行类似的约束。

我们先在来举一个例子主属性可以由部分主属性唯一确定的例子：

假设业务上要求同一个应聘者id只能有一个user id, 同时同一个user_id只能注册给一个应聘者id。如何实现这样的业务要求呢？

方案一如下表，我们来看看行不行：

Candidate_User_3NF_01
id(PK)		user_id(PK)
id1		uid1
id2		uid2
id3		uid3
id1		uid4
id4		uid1

表Candidate_User_3NF_01同时指定id和user_id为主属性，因为业务上要求id和user_id可以互相唯一确定对方，也就意味着主属性id可以由另一个主属性user id来唯一确定,或者主属性user id可以由另一个主属性id来唯一确定。这就不符合BCNF的要求了。而与此同时我们还发现其实这张表上可以插入多个id1和多个uid1，导致id和user_id互相都不能唯一确定对方，这又不满足业务上的要求了。

方案二如下表，我们来看看行不行：

Candidate_User_3NF_02
id		user_id(PK)
id1		uid1
id2		uid2
id3		uid3
id1		uid4

表Candidate_User_3NF_02只指定user_id为主属性，因为业务上要求id和user_id可以互相唯一确定对方，也就意味着user id可以由另一个非主属性id来唯一确定。这就不符合BCNF的要求了。而与此同时我们还发现其实这张表上仍然可以插入多个id1，导致id不能唯一确定user_id，这又不满足业务上的要求了。

方案三如下表，我们来看看行不行：

Candidate_User_3NF_03
id		user_id
id1		uid1
id2		uid2
id3		uid3
id1		uid4
id4		uid1

表Candidate_User_3NF_02没有指定主属性，终于满足了BCNF的要求了。但和表User_3NF_01一样，这张表上其实可以插入多个id1和多个uid1导致id和user_id，导致id和user_id互相都不能唯一确定对方，这又不满足业务上的要求了。

方案四： 那么如果想同时满足BCNF和业务要求，我们要如何改造这张表呢？这就需要引入unique约束到下表中：

Candidate_User_BCNF
id(PK)		user_id(unique)
id1		uid1
id2		uid2
id3		uid3

总结一下，BCNF就是对主属性的指定提出来更高的要求，尤其是当主属性是多个属性的时候，BCNF要求这几个属性中不能出现可以相互唯一确定的情况。

第四范式（4NF）

接下来我们来看看第四范式（4NF）的例子。

我们不妨把表Candidate_2NF改造为下表，原因是在现实中往往一个人可以有多个手机号和多个地址，专业上把这种场景叫做多值。此处出现了两个非主属性的多值现象。如果业务允许，我们可以把多个手机号和多个地址放在一起作为字符串来保存也不违反1NF。

Candidate_BCNF
id(PK)	name	age	gender	mobile	address
id1	name1	age1	gender1	mobile1，mobile11	address1，address11
id2	name2	age2	gender2	mobile2，mobile21	address2，address21
id3	name3	age3	gender3	mobile3，mobile31	address3，address31

但是，如果业务上需要把多个手机号和多个地址分开保存，实现方法有好几种，比如增加新的属性，如下：

Candidate_BCNF_01
id(PK)	name	age	gender	mobile	backup_mobile	address	backup_address
id1	name1	age1	gender1	mobile1	mobile11	address1	address11
id2	name2	age2	gender2	mobile2	mobile21	address2	address21
id3	name3	age3	gender3	mobile3	mobile31	address3	address31

如果业务上只允许每个求职者最多提供两个手机号和最多两个地址，上表是够用的。但万一哪天还需要允许更多的手机号和更多的地址呢？难道继续增加属性吗？一旦系统上线，表结构的变更总是不受欢迎的，因为这样会导致代码逻辑也要跟着变。

如果不改变表结构，是否可以改造为下表？

Candidate_BCNF_02
id	name	age	gender	mobile	address
id1	name1	age1	gender1	mobile1	address1
id1	name1	age1	gender1	mobile11	address11
id2	name2	age2	gender2	mobile2	address2
id2	name2	age2	gender2	mobile21	address21
id3	name3	age3	gender3	mobile3	address3
id3	name3	age3	gender3	mobile31	address31

观察发现，表Candidate_BCNF_02有一个重大缺陷：手机号和地址之间其实是没有直接关系的，我们把id1的mobile1和address1放在一条记录里面，而把mobile11和address11放在另一条记录里面具有随机性，因为我们也完全可以把mobile1和address11放在一条记录里面，而把mobile11和address1放在另一条记录里面。而且这样保存数据由大量的数据冗余发生。

到这里，我们可以开始介绍4NF的要求了。

4NF是关系型数据的另一个必然要求：在BCNF的基础上，一张表上不可以有多个非主属性的多值现象出现。

一张表上有多个非主属性的多值现象出现导致的后果我们已经在表Candidate_BCNF_02上分析清楚了，那么要怎样才能满足4NF呢？不言而喻，我们又要分拆表了，比如分拆为以下三张表：

Candidate_4NF
id(PK)	name	age	gender
id1	name1	age1	gender1
id2	name2	age2	gender2
id3	name3	age2	gender1

Candidate_mobile_4NF
id	mobile
id1	mobile1
id1	mobile11
id2	mobile2
id2	mobile21
id3	mobile3
id3	mobile31

Candidate_address_4NF
id	address
id1	address1
id1	address11
id2	address2
id2	address21
id3	address3
id3	address31

总结一下，4NF就是要杜绝出现一张表上有两个多值属性的情况。

第五范式（5NF）

大家会发现从1NF到4NF, 我们一直在用拆表的方式来解决各种各样的数据冗余问题，也就是说原来可能是很大的一张表，经过1NF到4NF的规范化，已经被分拆到很多很多的小表里去了，这些小表之间通过外键关联在一起，当我们想要查询出原来的大表的完整记录的时候势必要用关联操作，此时还原出来的大表可能会出现很多数据冗余，消除这样的关联之后存在的冗余就是第五范式想要解决的问题。

举个例子,第四范式分拆出来的表Candidate_4NF，Candidate_address_4NF，Candidate_address_4NF如果关联成一个大表又会出现大量的数据冗余。这三张表是不是符合5NF不是绝对的，而是取决于业务上会不会出现需要关联查询mobile和address。如果没有这样的业务需求，则它们是符合5NF的。但如果有这样的业务需求，比如需要在网络页面上的同一张大表上显示出mobile和address的信息。

5NF对关系型数据的冗余现象提出了更高的要求：在4NF的基础上，多表关联查询时不可以有多个非主属性的多值现象出现。

那么，如果有这样的业务需求，比如需要在网络页面上的同一张大表上显示出mobile和address的信息。如何来解决这个问题才符合5NF的原则呢？

此时就需要从业务上或者代码逻辑上去解决，比如网页上也分成两张表来显示或者代码逻辑上分成两次查询。

另外，从业务上分析，也会发现mobile和address之间没有直接联系，它们之间的联系是间接的，多个mobile和多个address之间的联系是通过id间接实现的，某个mobile和某个address互相并没有直接联系。所以拿这个例子来举例有点牵强。

那么我们再来举一个多个多值属性之间有直接业务联系的例子作为补充：

假设某个candidate可以申请多个department的多个position:

Candidate_BCNF_03
id	name	age	gender	position	department
id1	name1	age1	gender1	postion1, position2	department1, department2, department3
id2	name2	age2	gender2	postion2, position3	department3， department4

按照4NF的解决办法，我们把它分拆为以下三张表：

Candidate_4NF
id	name	age	gender
id1	name1	age1	gender1
id2	name2	age2	gender2

Candidate_Position_4NF
id	postion
id1	postion1
id1	postion2
id2	postion2
id2	postion3

Candidate_Department_4NF
id	department
id1	department1
id1	department2
id1	department3
id2	department3
id2	department4

从业务上分析，会发现position和department之间也是有直接联系的，也就是我们可能需要查询某个department开放了哪些position的职位或者某个职位被开放到哪些部门，如果只是满足到以上三个4NF的表的程度，势必需要关联查询Candidate_Position_4NF和Candidate_Department_4NF出现这样的查询结果表：

Select * from Candidate_Position_4NF t1, Candidate_Position_4NF t2 where t1.id = t2.id;

关联查询结果表1
id	postion	department
id1	postion1	department1
id1	postion1	department2
id1	postion1	department3
id1	postion2	department1
id1	postion2	department2
id1	postion2	department3
id2	postion2	department3
id2	postion2	department4
id2	postion3	department3
id2	postion3	department4

这样就会产生大量的数据冗余导致空间浪费（注意：临时的查询结果表也是要消耗数据库服务器的存储空间的）。

解决方案是再增加一个position和department之间的关联表如下：

Candidate_5NF
id	name	age	gender
id1	name1	age1	gender1
id2	name2	age2	gender2

Candidate_Position_5NF
id	postion
id1	postion1
id1	postion2
id2	postion2
id2	postion3

Candidate_Department_5NF
id	department
id1	department1
id1	department2
id1	department3
id2	department3
id2	department4

Position_Department_5NF
position	department
position1	department1
position1	department2
position1	department3
position2	department3
position2	department4

这样一来，我们可以通过查询Position_Department_5NF来避免关联查询了。对比发现，关联查询结果表1比Position_Department_5NF整整多出一倍的数据，查询结果表的数据冗余问题得到了解决。但是这是通过增加了一个关联表来实现的。

总结来说，4NF是通过分拆表把多个多值属性分拆到多个关联表中，如果这几个多值属性之间是不相关的，那么分拆之后也同时满足了5NF的要求。但如果某几个多值属性之间是相关的，则5NF希望通过增加相关的多值属性之间的关联表来避免多表关联查询。

从业务代码层面来看4NF和5NF有利于简化代码实现，从而提高重用性（复杂的代码往往不容易被重用到；代码越简单，重用的可能越大）。

第六范式（6NF）

6NF是最高级别的要求了：在5NF的基础上，每张表上最多只能有一个非主属性。

大家会发现，从1NF到6NF一直在分拆或者增加新的表，那么到了什么程度会再也无法分拆了呢？那自然是每张表上至多只有一个非主属性的时候。

举个例子：

Candidate_5NF_01
id(PK)	name	age	gender
id1	name1	age1	gender1
id2	name2	age2	gender2
id3	name3	age2	gender1

这张表有什么问题呢？假设某个业务代码只需要查询出某个candidate id的名字，那么查询所有属性（select * from Candidate_5NF_01）查到的age和gender就是数据冗余。

解决办法是继续拆分为以下三张表：

Candidate_Name_6NF
id(PK)	name
id1	name1
id2	name2
id3	name3

Candidate_Age_6NF
id(PK)	age
id1	age1
id2	age2
id3	age2

Candidate_Gender_6NF
id(PK)	gender
id1	gender1
id2	gender2
id3	gender1

总结来说，6NF通过把所有的非主属性分拆到不同的表中来避免每次全属性查询可能出现的不需要的数据引起的结果表数据冗余。

同理，从业务代码层面来看6NF有利于简化代码实现，从而提高重用性（复杂的代码往往不容易被重用到；代码越简单，重用的可能越大）。

总结

1NF：业务上属性不需要再分；

2NF：在1NF的基础上，非主属性都要全部主属性来唯一确定，而不可以由部分主属性就可以确定；

3NF：在2NF的基础上，非主属性都要由主属性才能唯一确定，而不可以出现由另一个或者另一些非主属性也可以唯一确定的情况；

BCNF：在3NF的基础上，主属性都要由全部主属性才能唯一确定，而不可以出现由部分主属性也可以唯一确定，或者由另一个或者另一些非主属性唯一确定的情况；

4NF：在BCNF的基础上，一张表上不可以有多个非主属性的多值现象出现；

5NF：在4NF的基础上，多表关联查询时不可以有多个非主属性的多值现象出现；

6NF：在5NF的基础上，每张表上最多只能有一个非主属性。

从1NF到6NF一直在付出一个代价，就是表的数量不断增加，从而带来因为表的数量增加带来的存储开销（比如外键属性会被重复保存），以及查询次数增加带来的结果表存储开销。

基于此，采用到哪个程度的范式最合适，需要综合考虑短期业务需求和长期业务需求来折衷选择。本人将择机另写文章阐述这方面的内容。

补充说明

有一种说法说1NF要求每张表都必须有主属性，这个说法不是必须的，但是在实践上是有道理的。对于业务数据库，我们往往希望限制每次查询的结果，这样就需要主属性。对于那些没有主属性的关联表，或者无法找出单个属性来担当主属性但可以找出多个属性来担当主属性的表，我们可以增加record_id来解决这个问题，冗长record_id会通过sequence number或者uuid等取值。

但是对于数据仓库或者大数据应用场景，由于系统性能的提高和对处理速度要求的降低，也允许出现没有主属性的表，也就是说1NF可以不需要主属性。此时辅助以分区分库实现可以实现整表查询。