数据库范式Database Normalization

最新推荐文章于 2024-09-26 10:11:11 发布

OnlyQi

最新推荐文章于 2024-09-26 10:11:11 发布

阅读量1.9k

点赞数 4

分类专栏：关系型数据库通用知识

本文链接：https://blog.csdn.net/OnlyQi/article/details/8584393

版权

关系型数据库通用知识专栏收录该内容

8 篇文章 0 订阅

订阅专栏

数据库范式化是为了最小化冗余和依赖，对关系型数据库中的字段和表进行合理组织的过程。

数据库范式有很多种，常见的有1nf,2nf,3nf和bcnf 4种：http://en.wikipedia.org/wiki/Database_normalization。如果是为了面试，掌握第一到第三范式就足够了。

第一范式是关系型数据库最主要的属性。也就是说第一范式是关系型数据库的最低要求。

如果一个关系型数据库满足第三范式，则称为“范式化的”。关系型数据库满足第三范式就可以了。

第一范式

A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain.

即表中每个字段的值是原子的，不可再分的。

例如一个人设计了这样一个客户表：

Customer
Customer ID	First Name	Surname	Telephone Number
123	Robert	Ingram	555-861-2025
456	Jane	Wright	555-403-1659
789	Maria	Fernandez	555-808-9633

之后他意识到一个客户可能有多个电话号码。于是他修改了一下设计：

Customer
Customer ID	First Name	Surname	Telephone Number
123	Robert	Ingram	555-861-2025
456	Jane	Wright	555-403-1659 555-776-4100
789	Maria	Fernandez	555-808-9633

然而，如果电话号码字段是按照普通号码格式来设计的，例如字段长度为12个字符，那么上面的设计就不符合第一范式。事实上，任何一个关系型数据库系统都不允许一个字段存储多个值(注意上面的设计并不是将两个号码存成一个长字符串，而是说一个字段可以存储多个值)。因此我们不可能设计出违背第一范式的数据库。

符合第一范式的设计应该是设计两个表：

Customer Name
Customer ID	First Name	Surname
123	Robert	Ingram
456	Jane	Wright
789	Maria	Fernandez

Customer Telephone Number
Customer ID	Telephone Number
123	555-861-2025
456	555-403-1659
456	555-776-4100
789	555-808-9633

或者在customer表中，对于那些有两个号码的人，有两行记录，其中ID，Name等是重复的。

第二范式

表在满足第一范式之后，满足以下条件才能满足第二范式：表中的非关键字段要么依赖于candidate key，要么依赖于另外一个非关键字段。

No Partial Dependencies on a candidate Key( which also is a concatenated key).

所谓的candidate key可以理解为行的唯一key。

也就是说如果不满足第一范式，就不可能满足第二范式。

考虑这样一个员工技能表：

Employees' Skills
Employee	Skill	Current Work Location
Jones	Typing	114 Main Street
Jones	Shorthand	114 Main Street
Jones	Whittling	114 Main Street
Bravo	Light Cleaning	73 Industrial Way
Ellis	Alchemy	73 Industrial Way
Ellis	Flying	73 Industrial Way
Harrison	Light Cleaning	73 Industrial Way

Employee字段和Skill字段都不合适作为行key。因为一个特定的员工可能出现多次，而一个特定的skill也可能出现多次。只有Employee和Skill的组合key才能唯一定位一行数据，在该表中只有Employee和Skill的组合key才是candidate key。

剩下的地址字段实际上是被Employee字段决定的。

因此该设计不符合第二范式：非关键字段Current Work Location依赖candidate key的一部分(即Employee)。

不符合第二范式的直接后果就是数据冗余，上例中很明显，地址信息是重复的。而数据冗余的直接后果就是可能出现数据不一致：

1，更新异常

假如由于某种原因，修改了Jones的skill为Typing那行对应的地址，但是没有修改另外两种skill对应的地址，数据就出现了不一致。

2，插入异常

如果插入一行Jones的新技能，但是地址不是114 Main Street，则数据出现了不一致。

将上例改为下面的设计使之符合第二范式，并消除了数据冗余：

Employees
Employee	Current Work Location
Jones	114 Main Street
Bravo	73 Industrial Way
Ellis	73 Industrial Way
Harrison	73 Industrial Way

Employees' Skills
Employee	Skill
Jones	Typing
Jones	Shorthand
Jones	Whittling
Bravo	Light Cleaning
Ellis	Alchemy
Ellis	Flying
Harrison	Light Cleaning

第三范式

Third Normal Form: No Dependencies on Non-Key Attributes

下面这个例子满足第二范式，但不满足第三范式。该表存储的数据是几个不同的奖项和每年不同奖项各自的冠军。

Tournament Winners
Tournament	Year	Winner	Winner Date of Birth
Indiana Invitational	1998	Al Fredrickson	21 July 1975
Cleveland Open	1999	Bob Albertson	28 September 1968
Des Moines Masters	1999	Al Fredrickson	21 July 1975
Indiana Invitational	1999	Chip Masterson	14 March 1977

从表中可以得知candidate key是{Tournament，Year}，而非关键字段Winner Data of Birth依赖于另一个非关键字段Winner，因此满足第二范式。但是仍然可以发现有数据冗余，并可能导致数据不一致。

第三范式的意思是表中的非关键字段只能依赖于candidate key，因此修改表结构为：

Tournament Winners
Tournament	Year	Winner
Indiana Invitational	1998	Al Fredrickson
Cleveland Open	1999	Bob Albertson
Des Moines Masters	1999	Al Fredrickson
Indiana Invitational	1999	Chip Masterson

Player Dates of Birth
Player	Date of Birth
Chip Masterson	14 March 1977
Al Fredrickson	21 July 1975
Bob Albertson	28 September 1968

范式设计与反范式设计

通过上文的介绍，我们可知在范式化的数据库中，每个事实数据(不包括自动生成的ID等列)会出现并且只出现一次(即没有冗余数据)。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。

对schema进行范式化设计是推荐的设计基准，尤其是写密集的场景。范式化通常能够带来诸多好处：
1，范式化的更新操作通常比反范式化要快。原因很简单，范式化设计的数据库中数据没有冗余，因此数据量更小。

2，当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。

3，范式化的表通常更小，可以更好地放在内存里，以及更快地备份恢复。

4，很少有多余的数据意味着检索列表数据时更少需要distinct或者group by语句。

范式化设计的schema缺点是通常需要关联。这不但代价昂贵，也可以使一些索引策略无效。反范式化的schema因为所有数据都在一张表中，可以很好的避免关联。
同时单独的表能使用更有效的索引策略。例如有一个网站，允许用户发送消息，且其中一部分用户是付费用户。现在想查看付费用户最近的10条信息。
如果是范式化的结构并且索引了发送日期字段published,这个查询也许看起来像这样：

select message_text, user_name
from message
inner join user on message.user_id=user.id
where user.account_type='premiumv'
order by message.published desc limit 10;

要有效地执行这个查询，有两种执行计划：
1，扫描message表的published字段后，到user表中检查该用户是不是付费用户。但如果大部分用户都不是收费用户，则效率比较低下。
2，或者从user表开始，选择所有的付费用户，获得他们所有的信息并且排序。但这可能更加糟糕。
主要问题是关联，使得需要在一个索引中又排序又过滤。如果使用反范式化的设计，在message表中加入user_type，则完全不会有这个问题。

因此我们在实际设计中，一般都是范式和反范式混合使用。在开始设计数据库时，应由范式化的设计开始，在大体结构定下之后，按照具体查询来做反范式化。如上例中，将user和message信息放在两个表中是合理的符合范式的设计，但如果有大量需要查询付费用户top10信息的查询，则应适当做反范式化设计。例如对于一些特定的表，使其符合第二范式但不符合第三范式。