大家好，我是只谈技术不剪发的 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）。外键是数据库用于实现参照完整型的约束，利用数据库的外键可以保证数据的完整性和一致性；外键的级联操作可以方便数据的自动处理，减少了程序出错的可能性。

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

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

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

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

总结

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

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

本文代码均已在 MATLAB R2019b 测试通过，如有错误，欢迎指正。

一、数据规范化的原理

数据规范化处理是数据挖掘的一项基础工作。不同的属性变量往往具有不同的取值范围，数值间的差别可能很大，不进行处理可能会影响到数据分析的结果。为了消除指标之间由于取值范围带来的差异，需要进行标准化处理。将数据按照比例进行缩放，使之落入一个特定的区域，便于进行综合分析。

（1）最小-最大规范化
假定min和max分别为属性A的最小值和最大值，则通过下面公式将属性A上的值v映射到区间[new_min, new_max]中的v’：
$$v^{\prime }=\frac{v-min}{max-min}(new\_max-new\_min)+new\_min$$

（2）零-均值规范化
将属性A的值根据其平均值mean和标准差std进行规范化：
$$v^{\prime }=\frac{v-mean}{std}$$

（3）小数定标规范化
通过移动属性A的小数点位置进行规范化，小数点的移动依赖于A的最大绝对值：
$$v^{\prime }=\frac{v}{10^j}$$

其中，j是使 Max(| v’ |)<1的最小整数。

二、Matlab代码实现

1.最小-最大规范化

Matlab的代码可以写简单一点，不用像C++那样写两个for循环。

• mi=min(A)默认求矩阵A每列的最小值，返回一个行向量mi。
• repmat(mi,n,1)重复mi，重复行n次，重复列1次，从而形成与A相同大小的矩阵。
• ./可直接将两个矩阵所有相同位置的元素相除（不用写两个for循环）。

clear;clc;

%% 数据存入A
A=[78 521 602 2863	
144 -600 -521 2245	
95 -457 468 -1283
69 596 695 1054
190 527 691 2051
101 403 470 2487
146 413 435 2571];

fprintf("原数据："); A

new=input("请输入需要映射到的新区间。输入格式示例：[0,1]\n");
new_mi=new(1);
new_mx=new(2);

[n,m]=size(A);
mi=min(A); % 求出A每列的最小值，形成行向量mi（不用for循环）
mx=max(A); % 求出A每列的最大值，形成行向量mx
mi=repmat(mi,n,1); % 将矩阵mi重复，行n次，列1次，调整到与A相同大小（之后就不用for循环了）
mx=repmat(mx,n,1); % 将矩阵mx重复，行n次，列1次，调整到与A相同大小
B=(A-mi)./(mx-mi)*(new_mx-new_mi)+new_mi; % ./的含义是将两个矩阵所有相同位置的元素相除

fprintf("\n经过最小最大规范化后："); B

Matlab也有现成的mapminmax()函数直接实现最小-最大规范化：

B1=mapminmax(A')'; % 每列规范化到[-1,1]
B2=mapminmax(A',0,1)'; % 每列规范化到[0,1]

解释一下：

• mapminmax()函数，默认按行最小最大规范化到[-1,1]。
• 由于默认是按行，如果要按列规范化，传参时需将A进行转置，将mapminmax()函数返回的结果再转置一下即可。
• B2=mapminmax(A',0,1)'，后面加上两个参数表示新区间的左右端点。

代码运行结果

输入的新区间范围为[0,1]：

原数据：
A =

          78         521         602        2863
         144        -600        -521        2245
          95        -457         468       -1283
          69         596         695        1054
         190         527         691        2051
         101         403         470        2487
         146         413         435        2571

请输入需要映射到的新区间。输入格式示例：[0,1]
[0,1]

经过最小最大规范化后：
B =

    0.0744    0.9373    0.9235    1.0000
    0.6198         0         0    0.8509
    0.2149    0.1196    0.8133         0
         0    1.0000    1.0000    0.5637
    1.0000    0.9423    0.9967    0.8041
    0.2645    0.8386    0.8150    0.9093
    0.6364    0.8470    0.7862    0.9296

2.零-均值规范化

clear;clc;

%% 数据存入A
A=[78 521 602 2863	
144 -600 -521 2245	
95 -457 468 -1283
69 596 695 1054
190 527 691 2051
101 403 470 2487
146 413 435 2571];

A_mean=mean(A); % mean求的是每列的均值
A_std=std(A); % std求的是每列的标准差
[n,m]=size(A);
B=(A-repmat(A_mean,n,1))./repmat(A_std,n,1);

fprintf("原数据："); A
fprintf("经过零均值规范化后："); B

Matlab也有现成的zscore()函数直接实现零-均值规范化：

[B,A_mean,A_std]=zscore(A); % 返回按列进行零均值规范化后的矩阵、每列均值、每列标准差
B=zscore(A); % 只返回按列进行零均值规范化后的矩阵

代码运行结果

原数据：
A =

          78         521         602        2863
         144        -600        -521        2245
          95        -457         468       -1283
          69         596         695        1054
         190         527         691        2051
         101         403         470        2487
         146         413         435        2571

经过零均值规范化后：
B =

   -0.9054    0.6359    0.4645    0.7981
    0.6047   -1.5877   -2.1932    0.3694
   -0.5164   -1.3040    0.1474   -2.0783
   -1.1113    0.7846    0.6846   -0.4569
    1.6571    0.6478    0.6752    0.2348
   -0.3791    0.4018    0.1521    0.5373
    0.6504    0.4216    0.0693    0.5956

3.小数定标规范化

clear;clc;

%% 数据存入A
A=[78 521 602 2863	
144 -600 -521 2245	
95 -457 468 -1283
69 596 695 1054
190 527 691 2051
101 403 470 2487
146 413 435 2571];

mx=max(abs(A)); % 求每列绝对值最大的数mx
len=floor(log10(mx))+1; % 求绝对值最大的数的位数len
B=A./(10.^len); % 按列将A中每个元素除以相同的10^len

fprintf("原数据："); A
fprintf("经过小数定标规范化后："); B

代码运行结果

原数据：
A =

          78         521         602        2863
         144        -600        -521        2245
          95        -457         468       -1283
          69         596         695        1054
         190         527         691        2051
         101         403         470        2487
         146         413         435        2571

经过小数定标规范化后：
B =

    0.0780    0.5210    0.6020    0.2863
    0.1440   -0.6000   -0.5210    0.2245
    0.0950   -0.4570    0.4680   -0.1283
    0.0690    0.5960    0.6950    0.1054
    0.1900    0.5270    0.6910    0.2051
    0.1010    0.4030    0.4700    0.2487
    0.1460    0.4130    0.4350    0.2571

一、规范化

常见的规范化有数据库设计的三范式。

• 1NF 是最低的规范化要求。如果关系 R 中所有属性的值域都是简单域，属性不可再分。
• 2NF 非主属性完全函数依赖于码
• 3NF 非主属性不传递依赖于任何一个候选码

二、反规范化

    数据库中的数据规范化的优点是减少了数据冗余，节约了存储空间，相应逻辑和物理的I/O 次数减少，同时加快了增、删、改的速度，但是对完全规范的数据库查询，通常需要更多的连接操作，从而影响查询速度。因此，有时为了提高某些查询或应用的性能而破坏规范规则，即反规范化（非规范化处理）。

常见的反规范化技术包括：

• （1）增加冗余列
  增加冗余列是指在多个表中具有相同的列，它常用来在查询时避免连接操作。例如：以规范化设计的理念，学生成绩表中不需要字段“姓名”，因为“姓名”字段可以通过学号查询到，但在反规范化设计中，会将“姓名”字段加入表中。这样查询一个学生的成绩时，不需要与学生表进行连接操作，便可得到对应的“姓名”。

• （2）增加派生列
  增加派生列指增加的列可以通过表中其他数据计算生成。它的作用是在查询时减少计算量，从而加快查询速度。例如：订单表中，有商品号、商品单价、采购数量，我们需要订单总价时，可以通过计算得到总价，所以规范化设计的理念是无须在订单表中设计“订单总价”字段。但反规范化则不这样考虑，由于订单总价在每次查询都需要计算，这样会占用系统大量资源，所以在此表中增加派生列“订单总价”以提高查询效率。

• （3）重新组表
  重新组表指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。

• （4）分割表
  有时对表做分割可以提高性能。表分割有两种方式。
  水平分割：根据一列或多列数据的值把数据行放到两个独立的表中。水平分割通常在下面的情况下使用。

  情况 1：表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询效率。
  情况 2：表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。
  情况 3：需要把数据存放到多个介质上。

• （5）垂直分割：把主码和一些列放到一个表，然后把主码和另外的列放到另一个表中。如果一个表中某些列常用，而另外一些列不常用，则可以采用垂直分割，另外垂直分割可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少 I/O 次数。其缺点是需要管理冗余列，查询所有数据需要连接操作。