数据库规范化原理基础介绍_MySQL

如果你跟数据库打交道的日子足够长的话，你肯定听过“规范化”这个术语。可能会有人问你：“你的数据库是规范化数据库吗？”或者“你的数据库满足BCNF范式吗？”大家常常会把规范化晾在一边，觉得这是个多余的东西，认为只有学院派作风的人才有那个闲心去对数据进行规范化。不过，了解规范化的原理，并把这些原理应用到日常数据库设计任务中并非那么复杂，而且规范化能显着提升数据库管理系统的性能。

本文将为大家介绍规范化的概念，并分别对最常用的几种范式进行详细说明。

什么是规范化？

规范化是对数据库数据进行有效组织的过程。规范化过程的两个主要目的是：消除冗余数据（如把相同的数据存储在超过一个表里）和确保数据的依赖性处于有效状态（相关数据只存储在一个表里）。这两个目标的实现很有意义，因为能够减少数据库和表的空间消耗，并确保数据存储的一致性和逻辑性。

范式

国际数据库界制定了一系列构建数据库必须遵循的特殊规则，以确保数据库的规范化。在关系数据库里，这种规则就是范式，在数据库的世界里用数字来定义不同级别的范式，从低到高共分为五种：第一范式（简称1NF）、第二范式（2NF）、第三范式（3NF）、第四范式（4NF）和第五范式（5NF）。第一范式需要满足的要求最低，第二范式在第一范式的基础上增加了更多的要求，以此类推。在实际应用当中，最常见的是第一范式、第二范式和第三范式，也将是本文介绍的重点，偶有满足第四范式的，而第五范式就更加少见，本文也不再赘述。

在我们开始讨论具体的范式之前，必须要清楚一个概念：这些范式是规则，而且只是规则而已。有时候，我们为了满足实际的商业应用需求，必然会出现偏离这些规则的情况。不管怎样，如果出现了这些偏倚的情况，我们要做的就是评价这些情况可能对系统造成的任何影响以及可能带来的数据不一致性，这非常重要。

第一范式（1NF）

对于关系数据库而言，第一范式就是最基本的规则设置，必须满足以下两项要求：

从同一个表中删除重复列

为各个相关数据组创建独立表格，并以唯一列或列集（主键）来识别每行。

在考虑实际设计一个数据库时，这些规则意味着什么呢？其实很简单。

第一条规则表示在一个表的同一行中不能出现重复数据，指的是数据库表的不可分割属性。举例而言，在一个人力资源的数据库中，存储了主管及其下级之间的对应关系。为了说明第一范式的规则，我们设定了这样的商业规则，即每个主管可以有一个或多个下属，而每个下属只能有一个主管。当我们创建一个列表或电子表格来跟踪这一信息时，我们可能会创建一个具有下列属性列的表：

主管	下属1	下属2	下属3	下属4
A	小明	小兰	小军
B	小丹
C	小罗	小白	小东	小王

不过，想想第一范式所实行的规则：在同一个表格中删除重复列。显然，从下属1到下属4这四个列都重复了。停下来，想想这样会引发什么问题。如果一个主管只有一个下属，那么下属2-下属4这几个列纯粹是浪费存储空间（要知道存储空间是数据库最有用的东西）。此外，假设某个主管已经有了四个下属，如果他又招聘了一个新下属，该怎么办？很可能就需要修改整个表的结构了。

这时，数据库新手往往会想出第二个“好主意”：试试下面的结构，这样我们就不用再增加一列，而且可以灵活利用数据存储空间：

主管	下属（们）
A	小明，小兰，小军
B	小丹
C	小罗，小白，小东，小王

这样表中下属（们）项可以输入多个值，如果A主管有三个下属，就可以这样输入“小明，小兰，小军”。

这个解决方法已经很接近了，但还是存在缺陷。下属（们）列仍然是重复（多个值）的，而且并非不可分割。如果我们需要增加或删除某位下属时，我们就需要对表的全部内容进行读写操作。如果这还不算很严重，那如果某位主管手下的下属多大上百人呢？而且，这样的结构使将来从数据库搜索数据的查询复杂化。下面才是符合第一范式的表：

主管	下属
A	小明
A	小兰
A	小军
B	小丹
C	小罗
C	小白
C	小东
C	小王

在这种情况下，每一位下属只能输入一次，而主管可以输入多次。

接着，我们来看看第二条规则：唯一列或列集（主键）来识别每行。看着上面的表你可能会建议把下属列作为主键来用。就我们假定每个下属只能有一个主管而言的商业规则而言，下属列是确实是主键的首选列。不过我们选择存储到这个表中的数据使这一列不太适合充当主键。试想，如果我们雇佣了两个叫小明的员工的话怎么办？我们应该怎么把他们和主管的上下级关系存储到数据库中呢？所以，最好使用真正唯一的识别标志（例如员工ID）作为主键。那么我们最终获得的表将是这样的：

主管ID	下属ID
200101	200506
200101	200708
200101	200709
200302	200404
200010	200102
200010	200303
200010	200507
200010	200608

第二范式（2NF）

第二范式（2NF）进一步深化了去除重复数据的概念，主要要求包括：

满足第一范式的所有要求

去除一个表中应用于多个行的数据子集，把它们分割到独立的表中。

利用外键为这些新创建的表和原表建立联接关系。

这些规则可以简单归纳如下：第二范式通过把冗余数据抽取出来，放置在新表里，并为新表建立联接关系，从而减少冗余数据量。来看个例子，假设有一家网上商店把客户资料都保存在一个数据库里，可能只是一个单一的名为客户的表，含有以下的属性项：客户ID、姓名、地址、城市、省份、邮编；如下表所示：

客户ID	姓名	地址	省份	城市	邮政编码
01	小明	农林下路12号	广东	广州	510000
02	小白	北京路14号	广东	广州	510000
03	小军	中山路4号	浙江	杭州	310000
04	小兰	滨江路2号	四川	成都	610000

随便扫一眼上面这个表格就会发现存在少量冗余数据。像“广东，广州，510000”这样的数据集很可能输入过好几次。从上面这个简单的例表中看起来似乎冗余数据不是很多，不过假设如果这个表有成千上万行的数据，那么就会浪费大量的空间。而且，假设广州的邮政编码改变了的话（尽管这不大可能，不过在其他类型的表中，相关的信息很可能会有所变化），我们就需要对整个数据库的很多地方进行修改。在遵从第二范式的数据库结构中，这个冗余的信息将被抽提出来，存储在另外一个独立的表中，我们把这个新表称为邮编表，表示如下：

邮政编码	省份	城市
510000	广东	广州
310000	浙江	杭州
610000	四川	成都

如果你想效率更高，你甚至可以先到邮局去收集城市省份和对应的邮政编码资料，事先把这份表填好。这种类型的数据库在下订单时候可能非常有用，当某接线员为你下订单时，可能会先问你的邮编，然后不用你说也知道你所在城市省份了。这样的设置有利于减少操作员的失误，并提高效率。

现在我们已经把重复数据从客户表中去除了，满足了第二范式的第一个规则。接下来就要使用外键把两个表联系起来。在这个例子中，我们利用邮政编码（邮编表中的主键）作为外键来创建联接。下面是新的客户表：

客户ID	姓名	地址	邮政编码
01	小明	农林下路12号	510000
02	小白	北京路14号	510000
03	小军	中山路4号	310000
04	小兰	滨江路2号	610000

这样就可以最大限度的减少存储在数据库中的冗余信息，并使我们的表结构符合第二范式的要求。

第三范式（3NF）

符合第三范式的数据库要满足以下两个基本要求：

首先要满足第一范式和第二范式的要求。

删除所有不完全依赖主键的列。

假设我们有一个商品订单表，包含了以下属性：订单号、客户编号、单价、数量、总价。如下表：

订单号	客户编号	单价	数量	总价
A1001	001	20	30	600
A1002	002	18	50	900
A1003	003	30	5	150
A1004	001	15	100	1500
A1005	004	20	40	800

记住，我们第一个要求就是这个表必须满足第一范式和第二范式的要求。看看有没有重复列？很好，没有。那么有没有可行的主键？有，订单号可以作为主键。这样我们满足了第一范式的要求。接下来看有没有应用于多个行的数据子集？没有。那么也满足了第二范式的要求。

现在，检查一下是不是所有的列都完全依赖于主键，而不依赖于表中的其他属性？客户编号会随着订单号变化而变化，似乎不依赖于其他属性。那单价呢？如果在对每位客户制定一个标准价格的情况下，这个属性可能会依赖于客户编号这个属性变化。不过，看上表的数据，我们可以发现即使是对于同一个客户也可能给出不一样的单价（见编号为001的客户）。因此，单价也是完全依赖于订单号。而商品数量不用说也是随着不同订单而变化，所以商品数量属性也没有问题。

那么总价呢？问题就出在这里了。总价是单价和商品数量相乘得出来的，因此不是完全依赖于主键。因此，为了符合第三范式的要求，我们必须把总价属性从此表中剔除。那这个表就只用了以下的属性：订单号、客户编号、单价、数量，而且符合了第三范式的要求。不过，你可能会问，那总价怎么办？这时一个衍生的属性，最好不要存储在数据库中。我们在执行数据库查询的同时很容易就可以计算总价来。例如， 在删除总结属性之前，我们可能会用以下的查询语句来获取订单号和总价信息：

SELECT 订单号，总价

FROM 商品订单

在删除了总价属性后，我们就可以用同样的方法来获得相同的结果，而不会违反范式规则；只需对查询语句做一点小改动：

SELECT 订单号， 单价 * 数量 AS 总价

FROM 商品订单

设计数据库时想要符合第四和第五范式，难度就相当大了；特别是第五范式，很难实现，而且强制实现有可能会破坏数据库完整性，所以很少考虑。数据库规范化以及设计数据库时需要遵循的范式其实时数据库设计的基础理论，在任何一本数据库基础教程里可能都会涉及到。但是很多人在进入数据库设计的实操阶段，往往就很难做到遵守上述的几个范式。希望大家在设计数据库的时候，最好还是认真想想有没有遵守这些范式规则，毕竟数据库结构合理、消除冗余数据、保持数据一致性对于数据库管理和查询性能的提高而言百利而无害