关系数据库设计：谈谈规范化技术

---

大家好，我是只谈技术不剪发的 Tony 老师。今天我们来聊聊关系数据库的规范化设计问题。本文不涉及数据库教材上晦涩难懂的各种公式，而是从实际应用出发，通过简单直白的方式介绍规范化的设计过程和常见范式。

为什么需要规范化？

很多教材和文章都是直接从第一范式开始介绍如何进行数据库设计，完全忽略了对事物前因后果的分析；从而导致我们看完之后，只知道要关系数据库要进行规范设计，但却不知道为什么要这么做。因此，我们首先来给大家介绍一下规范化之前发生了什么。

假设我们需要为某公司设计一个数据库，用于管理员工、部门、职位等相关的信息。首先从直观上考虑，可以将员工信息、所在部门以及职位信息存储到一个表中，如下图所示：

每一行数据对应一个员工的信息，包括他/她所在的部门、职位等。如果真的这么设计，我们在实际应用中很快就会发现以下各种问题：

• 数据冗余，同一个部门的信息存储了多份，这就需要占用更多的磁盘空间。另外，数据冗余有时候也可能是指在不同的表中存储了重复的信息；
• 插入异常，假如现在需要成立一个新的部门，由于还没有增加新的员工，因此无法录入这个部门的信息；
• 更新异常，如果需要修改某个部门信息，需要更新多行数据，效率低下；不小心忽略了某些记录的话，还会会导致数据不一致，尤其是当一个信息存储到多个表中时更容易出现这种情况。
• 删除异常，如果某个部门的所有员工都被删除，将会导致这个部门的信息也将不复存在；

关系数据库之父 Edgar F. Codd 显然意识到了这些问题，并且为此引入了规范化（Normalization）的设计过程。规范化使用范式（normal form）来定义和衡量，范式就是数据库设计时遵循的一种标准级别。Codd 最早提出了第一范式（1NF）、第二范式（2NF）以及第三范式（3NF），每个范式都基于前面的范式定义，例如第二范式需要先满足第一范式。

????更高级别的范式包括 BC 范式（BCNF）、第四范式（4NF）、基本元组范式（ETNF）、第五范式（5NF）、DK 范式（DKNF）以及第六范式（6NF）；一般来说，满足第三范式的数据库就可以避免数据冗余和操作异常问题。

通过以上介绍，我们知道了规范化是数据库设计过程中的一系列原理和技术，使用范式来定义和衡量，主要用于减少表中数据的冗余，消除异常，提高数据完整性和一致性。

下面我们基上面的非规范化数据库结构，逐步介绍第一范式到第三范式的实现过程。

第一范式

第一范式（First Normal Form）要求满足以下条件：

• 表中的字段都是不可再分的单一属性;
• 表需要定义主键（PRIMARY KEY）。

简单来说，首先就是每个属性要有单独的字段。在上面的不规范设计中，员工的个人电话和工作电话存储在一个字段中，破坏了原子性。另外，还需要为表定义一个主键，用于唯一识别表中的每一行数据；假设每个部门中的员工不会同名，可以使用部门名称加员工姓名作为主键。

将上面的示例修改成以下结构就可以满足第一范式：

第一范式要求表中的字段具有不可分割的原子特性；不过我们知道，原子是化学反应不可再分的基本微粒，但在物理状态中可以分割，它是由原子核和绕核运动的电子组成。因此，我们同样需要考虑字段不可分割到底是针对什么而言。

例如，上面的“姓名”字段，实际上也可以拆分成两个字段：姓氏和名字。那么到达要不要拆分呢？显然这个取决于应用程序如何使用这些信息，一般我们将姓名作为一个字段存储；有些应用可能需要拆分，这样在给客户发送消息时可以方便地显示为“尊敬的刘先生/女生”。

另一个类似的情况是地址信息，例如“XX省XX市XX区XX小区”，存储到一个字段还是拆分成多个字段？大部分情况下，应用程序可能需要统计不同地区的用户情况，拆分成多个字段便于分析。不过这时候需要注意的是如何确保数据的标准化，因为不同的用户虽然住在相同的小区，但会输入不一致的数据；所以最好提供一组标准的数据，提供下拉列表给用于进行选择。

除了基本的数字、字符、日期等数据类型之外，SQL 还提供了一些复杂的类型，例如数组、XML、JSON 以及自定义类型等。假如我们使用一个 JSON 字段存储电话号码，数据如下所示：

{
  "phoneNumbers": [
    {
      "type": "office",
      "number": "61238888"
    },
    {
      "type": "mobile",
      "number": "13612345678"
    }
  ]
}

那么这种设计算不算违反第一范式？从定义来说这显然不属于第一范式，因为这个字段中包含了多个可以分割的属性。

但是，从 SQL 标准来说这些类型都属于原生类型，而且提供了对这种数据进行处理和查询的内置函数和方法；如果从应用程序的角度来看，例如电商平台中的产品信息、博客文章中的评论信息，可以将它们看作一个原子数据存储在 XML 或者 JSON 字段中，因为没有进行分割处理的需求。

????SQL 是关系数据库的标准语言，但 SQL 远远不只能够存储和处理关系模型，XML 或者 JSON 文档、多维数组、图形存储以及流数据处理已经成为了 SQL 标准中的一部分，具体可以参考这篇文章。

以上表结构满足第一范式，但仍然存在数据冗余（例如部门信息），可能导致插入异常、删除异常、修改异常等问题；所以我们还需要进一步规范化。

第二范式

第二范式（Second Normal Form）要求满足以下条件：

• 满足第一范式；
• 非主键字段必须完全依赖于主键或者候选键，不能只依赖于主键或者候选键的一部分。

上面表结构中的“部门地址”取决于“部门名称”，也就是主键的一部分；这种依赖关系称为部分函数依赖（partial functional dependency）。显然，此时表中的部门信息存在冗余，可能导致各种操作异常。

为此我们可以将部门信息单独存储到一张部门表中，并且在部门表和员工表之间维护一个一对多的关系。我们继续将表的结构修改如下：

我们将员工表拆成了 3 个表，员工表中的部门编号和职位编号是外键，分别引用了部门表的主键和职位表的主键。另外，我们为每个表增加了一个 id 主键字段（工号、部门编号、职位编号）。因为部门名称、职位名称等信息并不适合作为主键；如果使用部门名称作为主键，当需要修改某个部门的名称，员工表中可能需要相应修改多条记录。

如果考虑到同一个部门中可能存在同名的员工，直接在员工表中增加一个 id 主键字段也可以满足第二范式的要求。

以上表结构满足第二范式，但仍然存在数据冗余（例如部门信息），可能导致插入异常、删除异常、修改异常等问题；所以我们还需要进一步规范化。

第三范式

第三范式要求满足以下条件：

• 满足第二范式；
• 属性不依赖于其它的非主属性，也就是非关键字段不依赖于其他非关键字段。

当主键决定字段 A，字段 A 又决定字段 B 时，称为传递函数依赖（transitive functional dependency）。例如员工编号决定了部门编号，部门编号决定了部门名称；如果将部门信息和员工信息放在一张表中，就存在这种依赖。显然，在上一节中将员工表拆分成三个表之后就不存在这种问题，因此满足第三范式。

最终，我们设计的公司数据库结构（ER 图）如下：

其中，部门和员工的关系是一对多的关系；职位和员工的关系也是一对多的关系。

现在我们来回顾一下非规范化设计时的几个问题：

• 部门、员工以及职位信息分别存储一份，通过外键保持它们之间的联系。因此，不存在数据冗余的问题；
• 如果想要成立一个新的部门，直接录入部门信息即可，解决了插入异常的问题；
• 如果某个部门的所有员工都被删除，该部门的信息不会受到影响，不存在删除异常；
• 如果需要修改部门信息，直接更新部门表即可，不会导致数据不一致。

对于前三个范式而言，只需要将不同的实体/对象单独存储到一张表中，并且通过外键建立它们之间的联系即可满足。这也是大多数在线交易系统数据库理想的设计方法。

反规范化

简单来说，规范化就是将大表拆分成多个小表，并且通过外键建立它们之间的联系。但是，规范化可能导致连接查询（JOIN）过多。例如，为了查看员工所在的部门名称和职位名称，我们需要关联查询 3 个表：

SELECT e.emp_name, e.hire_date, d.dept_name, j.job_title
FROM employee e 
JOIN department d ON (d.dept_id = e.dept_id)
JOIN job j ON (j.job_id = e.job_id)
WHERE e.emp_name = '孙尚香';

emp_name|hire_date |dept_name|job_title|
--------|----------|---------|---------|
孙尚香   |2002-08-08|财务部    |财务经理  |

如果表中的数据量很大，过多的表连接查询会增加数据库的 IO 操作，从而降低数据库的性能。因此，有时候为了提高某些查询或者应用的性能而故意降低规范反的程度，也就是反规范化（denormalization）。一般来说，数据仓库（Data Warehouse）和在线分析系统（OLAP）会使用到反规范化的技术，因为它们以复杂查询和报表分析为主。

常用的反规范化方法包括增加冗余字段、增加计算列、将小表合成大表等。例如想要知道每个部门的员工数量的话，需要同时连接部门表和员工表；可以在部门表中增加一个字段（emp_numbers），查询时就不需要再连接员工表，但是每次增加或者删除员工时需要更新该字段。

需要注意的是，反规范化会增加更新和修改数据的开销，导致数据存在冗余，可能带来数据完整性和一致性的问题；因此，通常我们应该先进行规范化设计，再根据实际情况考虑是否需要反规范化。

关于外键

在数据库结构设计时，还有一个经常争论的问题就是需不需要使用外键（FOREIGN KEY）。外键是数据库用于实现参照完整型的约束，利用数据库的外键可以保证数据的完整性和一致性；外键的级联操作可以方便数据的自动处理，减少了程序出错的可能性。

例如，员工属于部门，员工的部门字段上可以创建一个外键引用部门表的主键。此时，我们必须先创建部门，然后才能为该部门创建员工；不会出现员工属于一个不存在的部门的情况，保证了数据的完整性。同时，如果要删除一个部门的话，必须同时处理该部门下的员工；可以选择级联删除员工或者将员工的部门修改为其他部门等操作。

既然外键拥有这么多好处，为什么我们还要讨论是否需要使用外键呢？主要是性能问题。因为任何事情都是有代价的，数据库为了维护外键需要牺牲一定的性能，尤其是在大数据量高并发的情况下。因此出现了另一种解决方案，就是将完整性检查放到应用层去实现，而应用程序相对比较容易扩展。

不过，在应用端实现约束也可能导致一些问题。首先，无法百分之百保证不会出现问题，尤其是多个应用同时共享一个数据库时。缺失外键可能导致数据库的结构不明确，需要依赖相应的文档进行说明。

总之，在系统的设计之初应该尽量使用外键确保完整性。如果随着业务增长出现性能问题，可以考虑在应用中实现约束。

---

总结

本文从非规范化数据库结构可能导致的问题出发，介绍了关系数据库为什么应该进行规范化设计以及常用的各种范式。同时，我们还讨论了特殊应用场景下的反规范化问题和外键的取舍。

如果觉得文章对你有用，欢迎关注❤️、评论????、点赞????