《数据库基础语法》15. 什么是 ER 图，如何进行数据库规范化设计？

楔子

我们已经学习了许多 SQL 高级功能，包括空值的处理、连接查询、子查询、集合运算、通用表表达式、高级分组选项、窗口函数等等，这些特性可以帮助我们实现各种复杂的数据分析和报表功能。

从今天开始我们将会进入开发篇的学习，了解如何设计数据库的模式、管理表和操作表中的数据、理解数据库事务、索引、视图以及编写服务器端程序。首先，让我们来看看如何设计数据库的结构。

数据库设计流程

数据库设计是对数据进行组织和结构化的过程，关键问题是数据模型的设计。一个良好的设计对于数据库系统至关重要，它可以减少系统中的数据冗余、确保数据的一致性和完整性，同时易于维护和扩展。

常见的数据库设计流程主要包括以下几个步骤：

• 需求分析，收集和分析用户对系统的数据存储和处理需求，记录需要存储的数据对象。
• 概念设计，根据需求创建数据库的概念模型。也就是找出其中的实体、实体的属性以及实体之间的关系，结果通常是实体关系图（Entity-Relationship Diagram）。
• 逻辑设计，设计数据库的关系模式，包括定义表的主键和外键。另外，还需要通过规范化的流程对关系模式进行优化，减少数据的冗余，维护数据的一致性和完整性。
• 物理设计，结合具体使用的数据库管理系统，确定物理数据的存储结构，包括索引的优化等。
• 实施运行，根据设计结果建立实际的数据库环境，进行测试和上线运行。同时，还包含运行环境的维护、调整和优化。

以上流程之间并不是简单的顺序关系，有可能需要反复迭代；但对于简单的应用系统，也有可能跳过一些步骤进行快速设计。

接下来我们介绍数据库设计过程中使用的两种常用技术：实体关系图 和 规范化。

实体关系图

实体关系图（Entity-Relationship Diagram）是一种用于数据库设计的结构图，它描述了数据库中的实体以及这些实体之间的关系。ERD 包括实体、属性以及关系三个部分。

实体代表了一种对象或者概念。例如，员工、部门和职位都可以称为实体。实体在 ERD 中通常使用长方体来表示。

属性表示实体的某种特性，例如员工拥有姓名、性别、工资、所在部门等属性。属性在 ERD 中通常使用椭圆来表示。

关系则用于表示两个实体之间的相互联系，在 ERD 中通常使用菱形来表示。三种主要的关系类型是一对一、一对多和多对多关系。例如，一夫一妻制是一种典型的一对一的关系；一个员工只能属于一个部门，一个部门可以有多个员工，部门和员工是一对多的关系；一个学生可以选修多门课程，一门课程可以被多个学生选择，学生和课程是多对多的关系。

比如主要包括部门、职位以及员工信息的数据库模型，它们的 ERD 图如下所示：

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

进一步来说，ERD 可以按照抽象层次分为三种：

• 概念 ERD，即概念数据模型。描述系统中存在的业务对象以及它们之间的联系，一般给业务分析人员使用。上图就是一个概念 ERD。
• 逻辑 ERD，即逻辑数据模型。对概念数据模型进一步的分解和细化，转换为关系模型。同时，还需要引入规范化过程，对关系模式进行优化。
• 物理 ERD，即物理数据模型。物理 ERD 是针对具体数据库的设计描述，需要为每列指定类型、长度、可否为空等属性，为表增加主键、外键以及索引等。

规范化设计

规范化（Normalization）是数据库设计的一系列原理和技术，主要用于减少表中数据的冗余，增加完整性和一致性。

为什么需要规范化？

假设我们先不考虑规范化，将员工信息、所在部门以及职位信息存储到一个表中，如下图所示：

这种表设计相当于将员工信息、所在部门以及职位信息都存储到一个表中了，显然会存在如下问题：

• 数据冗余: 同一个部门的信息存储了多份, 需要占用更多的磁盘空间; 数据冗余有时候也可能是在不同的表中存储了重复的数据, 比如最开始的那个关于购买商品的栗子;
• 插入异常: 如果想要成立一个新的部门, 由于还没有增加新的员工, 因此无法录入这个部门的信息;
• 删除异常: 如果某个部门的所有员工都被删除, 该部门的信息也将不复存在;
• 更新异常: 如果需要修改部门信息, 那么会需要更新多行数据, 效率低下; 不小心忽略了某些记录的话，将会导致数据不一致;

为了解决这些问题，数据库引入了规范化过程。规范化使用范式来定义和衡量，关系模式的创始人 Edgar Codd 最早提出了第一范式（1NF）、第二范式（2NF）以及第三范式（3NF）。随后又出现了更高级别的范式，包括 BC 范式（BCNF）、第四范式（4NF）等。每个范式都基于前面的范式，例如第二范式需要先满足第一范式。它们之间的关系如下图所示：

下面我们对范式进行具体的分析，对于大多数的数据库系统而言，到达第三范式就已经足够了。

 

第一范式：

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

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

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

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

这里我们将原来的"居住信息"拆分成了两个字段，因为员工的手机号和住址是相互独立的；此外我们将"部门名称"和"姓名"作为了联合主键(假设不重复)，当然更常见的做法是使用数据库的自增主键。

 

第二范式：

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

• 满足第一范式;
• 非主键字段必须完全依赖于主键, 不能只依赖于主键的一部分(不能存在部分函数依赖);

A和B能够确定C，但是单独的A或B不能确定C，此时C完全依赖于(A, B)；A和B能够确定C，但是单独的A或B也能确定C，那么C部分依赖于(A, B)。而在第二范式中，非主键字段必须要完全依赖于主键。

示例中的"部门地址"只取决于"部门名称"、也就是主键的一部分，它和"姓名无关"，因此出现了部分函数依赖。显然，此时部门信息存在冗余，可能带来各种操作异常。

此时，可以将部门信息和职位信息存储到别的表中，并通过外键让它们和员工表之间建立一对多的关系。我们可以继续将表的结构修改如下：

第三范式：

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

• 满足第二范式;
• 属性不依赖于其它的非主键属性(不能存在传递函数依赖);

怎么理解呢？如果通过A能够确定B，通过B能够确定C，但是通过C不能确定A，那么C的传递依赖于A。

对于前三个范式而言，只需要将不同的实体/对象单独存储到一张表中，并且通过外键建立它们之间的联系即可满足。

此时，我们再来看看非规范化设计时的几个问题，会发现都得到了解决：

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

但是搞大数据的话，其实是不需要遵循第三范式的。因为搞大数据重点是在数仓的建设，分好层才是最重要的，很多不搞大数据的公司可能要求在设计表的时候，必须严格符合第三范式。而如果在数仓建设中严格遵循第三范式的话，那么需要很多的外键，而为了维护外键需要额外的性能。但如果不用外键的话，在查询的时候就又会出现大量的join。

反规范化：

简单来说，规范化就是将大表拆分成多个小表，并且通过外键建立它们之间的联系。但是，规范化可能导致连接查询（JOIN）过多，从而降低数据库的性能。因此，有时候为了提高某些查询或者应用的性能而故意降低规范反的程度，也就是反规范化（denormalization）。

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

反规范化可能带来数据完整性的问题；因此，通常我们应该先进行规范化设计，再根据实际情况考虑是否需要反规范化。一般来说，数据仓库（Data Warehouse）和在线分析系统（OLAP）会使用到反规范化的技术，因为它们以复杂查询和报表分析为主。

关于外键：

在数据库结构设计时，还有一个经常争论的问题就是需不需要使用外键。

外键（FOREIGN KEY）是数据库用于实现参照完整型的约束。利用数据库的外键可以保证数据的完整性和一致性；级联操作可以方便数据的自动处理，减少了程序出错的可能性。

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

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

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

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

小结

合理的设计是数据库有效运行和易于扩展的前提，数据库设计本质上一个多方面因素权衡的过程。利用 ERD 和规范化技术设计数据库的结构，可以提高数据库的存储效率、完整性和可扩展性。