【翻译】数据库设计——范式

简介

数据库所需要的特性是为了有效的存储以及容易维护数据。

第一点，有效的存储是指应避免数据冗余；第二点，易于维护是指一个好的数据库设计是将数据有逻辑地分散在数据表中。

规范化是一种实现这些目的的数据库设计方法。

什么是规范化？

数据库规范化，又称数据库或资料库的正规化、标准化，是数据库设计中的一系列原理和技术，以减少数据库中数据冗余，增进数据的一致性。(wikipedia.com)

规范化提供了一系列原则来帮助：

• 有效的组织数据
• 减少数据冗余
• 确保只有相关的数据被存储在一张表中

范式

随着时间的推移，已经发展出了一些好的规则，这些规则允许一个数据库被设计成具有不同程度的数据库。这些规则被称为范式，这些范式用0到5数字进行编号，即0NF，1NF，2NF，3NF，4NF，5NF。

在这里，你并不需要关心4NF和5NF，因为它们暂时在这里不做讨论。我们将只讨论1NF，2NF，3NF。

你应该知道范式是数据库设计者的唯一指导方针——但是他们并不一定要遵循它们！

You should be aware that normal forms are only guidelines for the database designer - they do not have to follow them!

数据库设计者根据需要开发的数据库类型来选择范式。他们也许不规范化他们的数据库，此时这种数据库被称为未规范化数据库或0NF。

第一范式-1NF

对于一个属于第一范式的数据库，数据库中的每一张表都必须符合下面的规则：

• 没有重复的或相似数据的列
• 每一项都不能再分割
• 每一行都是唯一的，也就是说数据库有一个主码
• 每一个域都有一个唯一的名字

原子性这个词用于描述一个数据项不可再分割的性质。

原子性数据示例

• 身份证号码
• 社会保险号
• 书的ISBN编码，如1-931841-62-4
• 股票代码
• 一个人的姓
• 一个人的名
• 电话号码
• 学校名字，如 'Abington Hall Comprehensive School'
• 一个完整的说明，如'A fountain pen is a writing instrument'

最后的两个示例说明了原子性并不代表是一个单词。它只是简单的指当我们去除数据项中的某些字符时，它将会变得无法理解，因为它失去了原本的意思。

非原子性数据示例

• 一个人的全名，如John Hunt，它实际包括了姓和名，一起存储在数据库
• 一个完整的地址，如6 Picton Road, London, WR1 4PG，它实际包括了街道，城市等信息
• 一个更大数据集的部分数据（Data that are part of a larger dataset），如Oxford Book club and Coventry Health club

重复数据示例

ID	First name	Surname	TelePhone1	TelePhone2	TelePhone3
2	Tom	Smith	22323	45634	3456345

在这种情况下，数据库尝试保存每一个人的多个号码。数据库设计者创建了三个域用于保存电话号码。这就是所谓的重复数据，电话号码属于同一种类型的数据。

1NF示例 1

提醒：第一范式规则

• 没有重复的或相似数据的列
• 每一项都不能再分割
• 每一行都是唯一的，也就是说数据库有一个主码
• 每一个域都有一个唯一的名字

下面哪个数据表不符合第一范式？

| Title |Firstname |Surname| Full name| Address| City |Postcode |
|:-😐:-😐:-😐:-😐:-😐:-😐
| Mr|Tom|Smith|Tom Smith|42 Mill Street|London|WE13GW|

ID[主码]	IP Address	username	last accessed	Activity	Result	active
1003[主码]	198.168.1.5	Smith	20081021:14.10	Save file	success	y

ItemID[主码]	Product	Description	Size	Colour	Colour	Colour
234	Shoe	High Heel	6	red	blue	brown

StudentID[主码]	Firstname	Surname	SchoolID*	ClassID*
354	Tom	Smith	6	5F

说明：

表一：不属于1NF，没有定义主码，因此每一条记录不能保证是唯一的。Full name 是冗余的——数据并非原子性，因为它是 Firstname和Surname的简单组合。
表二：至少属于1NF，它有一个主码。数据是原子性的。每一个域都有唯一的名字。没有重复数据。
表三：不属于1NF，它有主码，数据是原子性，但是color域重复了，并且这些域的名字也是相同的！
表四：属于1NF，符合1NF的所有规则

思考问题判断1NF

• 它是否有主码
• 每一个域的名字是否唯一
• 数据是否原子性
• 是否有重复或冗余的域

1NF示例 2

假设为一个粉丝俱乐部网站设计数据库。粉丝们访问网站来寻找志同道合的朋友。

存储的实体如下面所示

这表明每一个乐队都拥有许多粉丝，每一个人是唯一一个乐队的粉丝。

BAND	FAN
The attributes of band are:band id ,band name , musictype	The attributes of a fan are: fan id,firstname, surname,email addresse(s)

数据库需要满足第一范式。

第一次尝试

这是数据库设计者第一次设计数据库，他真的不太确定如何进行。他设计了一个FAN表，并且加载以下记录

FanID[主码]	Firstname	Surname	BandID*	email
1	Tom	Smith	23	tm@fan.org
2	Mary	Holden	56	mh@fan.org , mary@myhome.org

他成功的建立了一个主码，并且也使用了外码来引用乐队。但是这不属于1NF，因为Mary拥有的两个邮箱地址加载在同一个email域。数据不是原子性了。这样存储数据也很难提取邮箱地址。同时email域的数据长度必须足够大以存储许多邮箱地址，这非常低效率且如果超出一定的范围会产生一定的问题。

第二次尝试

他马上意识到这不是一个好主意，所以打算创建两个email域

FanID	Firstname	Surname	BandID*	email	email2
1	Tom	Smith	23	tm@fan.org
2	Mary	Holden	56	mh@fan.org	mary@myhome.org

这也是一个不好的方法——注意：email2在TOM的记录中未被使用到，造成一定的空间浪费，因此不属于1NF（用于避免冗余和浪费）。另一个问题是假如一个粉丝拥有更多的邮箱那该怎么办？增加更多的email域会使空间浪费更严重。

另一个问题是查询提取邮箱地址更加复杂，因为需要检验每一个email域的邮箱地址。

解决方案

在尝试了几种不同的方法之后，他想到了一个不错的方法——创建另一个email实体，同时在表中使用一个外码去链接粉丝和邮箱地址。ER图如下：

ER表示了每一个粉丝能够拥有许多邮箱地址，但是一个邮箱地址只能属于一个粉丝。

现在FAN和EMAIL表如下所示：

FAN

FanID[主码]	Firstname	Surname	BandID*
1	Tom	Smith	23
2	Mary	Holden	56

EMAIL

EID[主码]	FanID*	email
1	1	tm@fan.org
2	2	mh@fan.org
3	2	mary@myhome.org

第二范式-2NF

大多数表格通常都有一个单属性的主码。就像这样：

CUSTOMER

ID [主码]	Firstname	Surname	Telephone	email
2	Tom	Smith	22323	ts@aol.com

但是有时候一个表拥有的主码是由多个属性构成的，即拥有一个复合主码

CONCERT

Venue [主码]	Artist [主码]	Attendance	Profit	Style
Wembley	Girls Aloud	53000	12334	Girl band
NEC	Leona Lewis	45000	66433	Female soloist

上面表格中使用了venue和artist作为复合主码。

正是在这种情况下，对于第二范式的额外规则就派上用场了。

• 非关键属性必须依赖主码的每一部分
• 表必须已经符合第一范式

所以本质上，任何符合1NF并且有一个单一属性的主码的表同时也符合第二范式了。
思考上面的CONCERT表格——它不属于第二范式。注意到Style属性用于描述artist的风格，它和CONCERT在哪儿演出（即venue）没有任何联系！因此它的值并不依赖主码的每一部分，所以不符合第二范式的规则。

这个规则是为了确保没有冗余的数据被存储。

比如，我们添加另一个Girls Aloud的演唱会记录到表格中

Venue [主码]	Artist [主码]	Attendance	Profit	Style
Wembley	Girls Aloud	53000	12334	Girl band
NEC	Leona Lewis	45000	66433	Female soloist
NEC	Girls Aloud	76090	53789	Girl band

注意到Girl band值是重复的，因此导致数据库比原来所需的更大。

构建一个2NF数据库

继续看CONCERT表

Venue [主码]	Artist [主码]	Attendance	Profit	Style
Wembley	Girls Aloud	53000	12334	Girl band
NEC	Leona Lewis	45000	66433	Female soloist
NEC	Girls Aloud	76090	53789	Girl band

这个表格需要分解，移除不依赖全部主码的非关键属性，保证它只被存储一次。

在这种情况下，STYLE表格拥有一个artist单一主码

CONCERT

Venue [主码]	Artist [主码]	Attendance	Profit
Wembley	Girls Aloud	53000	12334
NEC	Leona Lewis	45000	66433
NEC	Girls Aloud	76090	53789

STYLE

Artist [主码]	Style
Girls Aloud	Girl band
Leona Lewis	Female soloist

现在，上面这两个表格已经符合2NF的规则了——每一个非关键属性依赖于全部主码。这里没有冗余的数据了。

构建一个2NF数据库 2

当然，可能有多个属性依赖主码的不同部分，考虑下面的表格

CONCERT

Venue [主码]	Artist [主码]	Date	Attendance	Profit	City	No1Hits	Style
Wembley	Girls Aloud	1/10/09	53000	12334	London	5	Girl band
NEC	Leona Lewis	1/10/09	45000	66433	Birmingham	2	Female soloist
NEC	Girls Aloud	7/11/09	76090	53789	Birmingham	5	Girl band

像前面的Style属性只依赖于Artist一样，现在NO1Hits也只依赖于Artist。这个表格也包括了只依赖于Venue的City。

所以为了使表格符合第二范式，需要创建四个表格

CONCERT

VenueID [主码]	Artist [主码]	Date	Attendance	Profit
005	0112	1/10/09	53000	12334
006	0115	1/10/09	45000	66433
006	0112	7/11/09	76090	53789

STYLE

Style ID [主码]	Style
001	Girl band
002	Solo artist
003	Rap

ARTIST

ArtistID [主码]	Artist	No1Hits	StyleID
0112	Girls Aloud	20	001
0115	Leona Lewis	3	002

VENUE

Venue ID [主码]	Venue	City
005	Wembly	London
006	NEC	Birmingham

总结

第二范式的规则是

• 非关键属性必须依赖主码的每一部分
• 表必须已经符合第一范式

第三范式-3NF

对于一个属于3NF的数据库，需要符合一下规则：

• 它已经是2NF
• 没有任何非关键属性依赖其他非关键属性

这么做是为了发现冗余数据的其他来源。如果一个属性的值可以简单的通过表格中的其他属性获得，那么它就不需要存储在这张表格中了。把属性加载到另一张表，同时链接到它会使数据库变得更小。

思考下面的表格

CONCERT

Venue[主码]	Artist[主码]	Date[主码]	Attendance	Profit	City	Country
Wembley	Girls Aloud	1/10/08	53000	12334	London	UK
NEC	Leona Lewis	1/10/08	45000	66433	Birmingham	UK
Carnegie Hall	Girls Aloud	7/11/08	76090	53789	New York	USA

注意到county国家能够通过城市获得——如果演唱会在伦敦举办，那么同时也可以得出也在英国举办——不需要查看主码！
所以为了使数据库符合第三范式，那么可以按下面分解该表格

CONCERT

Venue[主码]	Artist[主码]	Date[主码]	Attendance	Profit	City
Wembley	Girls Aloud	1/10/08	53000	12334	London
NEC	Leona Lewis	1/10/08	45000	66433	Birmingham
Carnegie Hall	Girls Aloud	7/11/08	76090	53789	New York

CITIES

City[主码]	Country
London	UK
Bimingham	UK
New York	USA

新的表格CITIES拥有一个City主码和county属性。Concert表格拥有City作为外码，因此现在你能够获取某一特定演唱会举办的国家，同时没有冗余数据产生。

3NF示例

提醒，3NF意味着

• 它已经是一个2NF
• 没有任何非关键属性依赖其他非关键属性

示例 1

CUSTOMER

CustomerID[主码]	Firstname	Surname	City	PostCode
12123	Harry	Enfield	London	SW7 2AP
12443	Leona	Lewis	London	WC2H 7JY
354	Sarah	Brightman	Coventry	CV4 7AL

这不完全符合3NF，因为City能够通过Postcode获得。如果你创建一个包含Postcode的表格，则City可以得到。

CustomerID[主码]	Firstname	Surname	PostCode*
12123	Harry	Enfield	SW7 2AP
12443	Leona	Lewis	WC2H 7JY
354	Sarah	Brightman	CV4 7AL

POSTCODES

PostCode[主码]	City
SW7 2AP	London
WC2H 7JY	London
CV4 7AL	Coventry

示例 2

VideoID[主码]	Title	Certificate	Description
12123	Saw IV	18	Eighteen and over
12443	Igor	PG	Parental Guidance
354	Bambi	U	Universal Classification

Certificate（证书）意思的描述，即Description属性可以从Certificate属性获得——它并不需要查看参考主码VideoID。因此可以使用主码和辅助码的方式分解表格。

示例 3

CLIENT

ClientID[主码]	CinemaID*	CinemaAddress
12123	LON23	1 Leicester Square. London
12443	COV2	34 Bramby St, Coventry
354	MAN4	56 Croydon Rd, Manchester

CINEMAS

CinemaID[主码]	CinemaAddress
LON23	1 Leicester Square. London
COV2	34 Bramby St, Coventry
MAN4	56 Croydon Rd, Manchester

在这种情况下，数据库已经接近3NF，但是因为某些原因，CinemaAddress在CLIENT表格中重复了，即使它可以在CINEMAS表格中获得。因此可以简单的移除CLIENT中的CinemaAddress列。

示例 4

ORDER

OrderID[主码]	Quantity	Price	Cost
12123	2	10.00	20.00
12443	3	20.00	60.00
354	4	30.00	120.00

在这种情况下，Cost能够通过Quantity乘以Price获得。这是一个计算域。数据库会比原本所需的大，因为原本任一菜单的消费能够通过一次查询计算出。因此为了完全符合3NF，需要移除Cost列。

规范化优点

1. 数据库不含冗余数据，大小更小，节省存储成本
2. 因为更少的数据需要搜索，因此查询效率更快
3. 因为没有数据重复，可以更好的保持数据一致性和更少的错误
4. 因为没有数据重复，所以存储多于2次不同数据的拷贝机会更少
5. 一旦变化，变化能够立即级联任何相关的记录

规范化缺点

1. 你需要细心地使数据保持原子性。仅仅因为你能够再次分解数据的类型，它并不总是正确的做法。例如，电话号码可能包含下面这些代码0123456789，但是将电话号码分解成两部分是不明智的。
2. 和仅有一个未规范化的表格相比，你最后得到的是多个表格
3. 数据库表格越多，越复杂，查询的速度会越慢
4. 它需要分配更多的关系来表示大量表格之间的交互
5. 对于更多表格，建立一个搜索会变得更加复杂

总结

规范化的目的是为了避免数据冗余和更好的维护数据库，只需在一处地方更新一个记录即可。

规范化的特性和范式：

未规范化形式(0NF)

• 数据可能重复
• 数据可能非原子性
• 表格可能没有主码
• 可能存在重复的域，也就是说每一个域的名字不是唯一的

第一范式(1NF)

• 没有重复的或相似数据的列
• 每一项都不能再分割
• 每一行都是唯一的，也就是说数据库有一个主码
• 每一个域都有一个唯一的名字

第二范式(2NF)

• 表必须已经符合第一范式
• 非关键属性必须依赖主码的每一部分

第三范式(3NF)

• 它已经是2NF
• 没有任何非关键属性依赖其他非关键属性

原文链接：Database Design
本文链接：数据库设计