数据模型设计

本是古典何须时尚 2022-10-12 01:24 202阅读 0赞

### 数据模型设计 ###

*  数据模型设计
 *  一、确保所有表都有主键
 *   *  总结
 *  二、避免存储冗余数据
 *   *  总结
 *  三、第3条：消除重复数据组
 *  总结
 *  四、每列只存储一个属性
 *   *  总结
 *  五、理解为什么存储计算列通常有害无益
 *   *  总结
 *  六、定义外键以确保引用完整性
 *   *  总结
 *  七、确保表间关系的合理性
 *   *  总结
 *  八、当第三范式不够时，采用更多范式
 *   *  总结
 *  九、非规范化数据仓库
 *   *  总结

# 数据模型设计 #

如果你面对的是一个糟糕的数据模型设计，想写出高效的SQL语句是不太可能的。当数据模型规范化没有使用正确的关联关系定义时，你会发现使用SQL语句从这些数据中获取有用的信息即使有可能也会是相当困难的。

本章介绍了一些基本的数据模型设计原则。如果你的数据库设计违反了本章中讨论的任何原则，应当找出问题所在并修复。如果数据库设计不在你的控制之下，至少也要能够理解你面对的问题是什么，然后再把你认为可行的方案解释给有设计权利的人。

你可以使用本章中提到的内容来解释，为什么很难或根本不可能使用SQL从数据库查询数据。即使你没办法改善设计，在SQL中也有其他变通的方法可以解决某些问题。

# 一、确保所有表都有主键 #

关系模型要求数据库系统能够区别表中的每一行，所以每张表都应该包含由一列或多列组成的主键。主键的值必须唯一且不能为空。

如果缺少主键，过滤数据时就没办法确定到底是匹配一行还是零行。然而，创建没有主键的表是合法的。事实上，仅仅将一列或多列设置为非空且唯一并不意味着数据库引擎会更有效地使用这些列。必须通过在一列或多列上定义主键的方式明确地告知数据库引擎才行。此外，在没有定义主键的表之间创建模型关系通常是不可能或不可取的。

当表缺少主键时，会出现各种问题，比如重复数据、数据不一致、查询缓慢以及统计报告中信息不准确等！以图1-1中所示的Orders表为例。

![image-20210702151806415][]

在图1-1中，从计算机的角度来看，表中的值的确都是唯一的，但是有可能它们都属于同一个人，至少第1、2和4行（John A. Smith的变体）是相同的。虽然计算机处理数据的速度比任何人的大脑都快，但是在没有预先大量编程的情况下，计算机并不善于识别相同的数据。因此，即使我们可以把Customer列定义为表的主键，也不见得会是一个很好的设计，即便它满足了唯一性要求。

那么，什么样的列才能作为主键呢？主键应具有以下特征：

*  唯一性
 *  值非空
 *  不可变（即值永不会被更改）
 *  尽可能简单（例如采用整数数据类型而不是浮点或字符类型，使用单列而不是多列）

符合上述要求的常见做法是使用自动生成的无意义数值类型作为主键。这种主键在不同关系数据库管理系统（RDBMS）软件中叫法不一样，在IBM DB2、Microsoft SQL Server和Oracle 12c中称为IDENTITY，在Microsoft Access中称为AutoNumber，在MySQL中称为AUTO\_INCREMENT，PostgreSQL中称为serial。在以往的Oracle版本中，需要使用Sequence对象作为主键，但不同的是，它是一个独立的对象而不是列的属性。DB2、SQL Server和PostgreSQL也同样支持Sequence对象。

引用完整性（RI）是关系数据库中非常重要的概念。强制引用完整性是指子表中具有非空外键的所有记录都必须在父表中找到相匹配的记录。

一个设计良好的Orders表，客户信息应该来自一个外键，此外建关联另外一张Customers表的主键。如果存在多个名为John Smith的客户，每条客户记录应该拥有自己的唯一标识，这样识别每个订单的唯一客户就容易多了。

为了维持表之间的引用完整性，主键值的任何改动都必须级联更新到关联表的相关子记录。但级联更新会导致关联表加锁，在高并发多用户数据库中可能导致严重的问题。以图1-2所示的Customers表为例，该表取自Microsoft Access 2003的示例数据库Northwind中的Customers表。

![image-20210702151925008][]

在这个示例中，我们假设有一个业务规则，文本型的主键CustomerID的值会随公司的名称变化而变化。如果某一个公司修改了名称，CustomerID的值也会按照相关的业务规则改变。这就需要级联更新关联表中的数据。如果使用无意义的数据作为主键，就可以避免这样做，而且还可以保留文本型的列显示相关业务的值。

支持文本型作为主键的一个常见观点是它可以防止引入重复数据。例如，如果把CompanyName列设置为主键，可以保证不存在重复的名称。然而，通过在Customers表的CompanyName列上创建唯一索引的方式来防止重复名称一样容易。在满足了引用完整性的同时，仍然可以使用自动生成的数值作为主键。如果同时采纳了第2条和第4条建议，避免出现我们在图1-1中提到的问题就特别奏效。另一方面，使用文本型的主键避免了在连接查询时需要事先在其他表查询与数值主键对应的文本值的开销（CompanyName，图1-2中的例子），这样通常能够简化SQL语句。

使用数值还是文本型作为主键一直都是数据库专家们激烈争论的问题。我们并不会倾向任何一方的观点，我们关注的重点是所有表都有能作为主键的唯一标识。

同时，我们建议不要使用复合主键，因为效率太低，有以下两个原因：

1）定义主键时，大多数数据库系统会同时强制创建唯一索引。唯一索引创建在多列上会对数据库系统造成额外的负担。

2）使用主键做连接查询很常见，但是在具有多个列的主键上这样做会很复杂，效率也更低。

但是，在某些情况下，使用包含多个列的主键确实是有帮助的。以一个关联产品和供应商的表为例，表包含VendorID和ProductID字段，分别关联供应商和产品表的主键。这个表可能还包含其他列，例如用来表明产品的供应商是一级供应商还是二级供应商的字段，还有产品的价格字段。

你可以添加一个自动生成的数值列作为代理主键，但是你也可以使用VendorID和ProductID两列的组合作为主键。你始终会使用其中一个字段关联到这个表，因此设置复合主键可能会比单独再添加一列作为主键更有效率。而且你会使用这两列来标识数据的唯一性，所以使用这两列作为复合主键而不是再添加一列作为主键更好。请阅读第8章深入了解复合主键的优势。

## 总结 ##

*  所有表都必须有一列（或多列）设置为主键。
 *  如果不希望非键列出现重复数据，在列上定义唯一索引以保证其完整性。
 *  键尽可能简单，且值不被更改。

# 二、避免存储冗余数据 #

存储冗余数据会导致很多问题，包括数据不一致；插入、更新或者删除时出现异常，而且还浪费磁盘空间。规范化是指按照不同的主题将信息分类，以避免存储冗余数据的过程。请注意，我们所说的冗余，并不是指一个表的主键在另外一个表作为外键重复出现就是冗余数据。我们说的冗余是指用户在不同地方输入相同数据的情况。前一种冗余用来维护表之间的关联关系是非常有必要的。

规范化最重要的目标就是最小化数据重复，无论是在同一张表里还是在整个数据库中。图1-3展示了客户订单数据库包含的几个存储冗余数据的例子。

数据不一致问题。客户Tom Frank的地址在第二条记录中，地址中数字的部分是7453，但是在第6条记录中，数字的部分却是7435。类似的数据不一致的问题可能出现在任何列中。

插入数据时异常。在这个表中，如果一条销售数据在没有录入客户数据的情况下试图先插入某个汽车的型号数据，则会出现插入异常。而且，当客户多购买一辆汽车时，这个表的设计都会重复大部分的数据。这种不必要的数据不仅浪费了磁盘、内存和网络资源，甚至还浪费数据录入员的时间。此外，重复数据大大增加了数据错误的风险，例如图1-3中地址里颠倒的错误数字。

![image-20210702152147583][]

更新数据时异常。如果销售人员结婚后更改了姓氏，则需要更新与其相关的所有数据。如果很多人同时更新这些数据，可能会造成很大的压力。此外，只有当此人的名字拼写都完全相同（意味着没有不一致的数据）并且没有其他人使用这个名字的时候，更新才会成功。

删除时异常。如果从数据库删除了某条数据，可能会丢失某条并不想删除的数据。

图1-3所示的客户销售数据在逻辑上可以分成4个表：

1）客户表（Customers，名称、地址等）

2）员工表（Employees，销售员姓名、聘用日期等）

3）汽车信息表（AutomobileModels，年份、型号等）

4）销售记录表（SalesTransactions）

这种设计允许你将客户、员工和汽车型号信息分别存储在相关表中。所有的表都包含一个唯一标识符作为主键。销售记录表使用外键关联这几张表，并存储销售的详细信息，如图1-4所示。

聪明的读者可能已经注意到了，采用此流程更正Tom Frank的地址后，消除了一个重复的客户记录数据。

通过将3张父表（Customers、AutomobileModels和Employees）中的主键与Sales-Transactions子表中的外键相关联，可以创建一个关联关系（也称为外键约束），如图1-5所示。图中所示的示例是用Microsoft Access软件中的表关系编辑器创建的。每个关系数据库都有自己不同的表关系展示方式。

![image-20210702152228463][]

你可以通过创建虚拟表（查询）的方式很容易地重现先前图1-3中的数据样式，而不会存储任何冗余数据（创建虚拟表是公用表表达式（简称CTE）最好的使用方式，我们会在第42条进一步讨论这个问题），如代码清单1-1所示。

![image-20210702152243527][]

## 总结 ##

*  数据库规范化的目标是消除冗余数据，并在处理数据时最小化资源消耗。
 *  通过消除冗余数据，避免插入、更新和删除时出现异常。
 *  通过消除冗余数据，尽量减少数据的不一致性。

# 三、第3条：消除重复数据组 #

重复的数据组出现在电子表格中是很常见的。通常，数据录入员只是简单地将这些数据导入到数据库中，并不会考虑数据库的规范化问题。图1-6中是重复数据组的例子，DrawingNumber关联最多5个Predecessor数据。表的DrawingNumber和Predecessor之间是一对多的关系。

图1-6中将单个属性Predecessor包含了重复的数据组，在ID=3的记录中Predecessor还存在重复数据，这并不是我们想要的。类似的例子比如把January、February和March（或Jan、Feb和Mar）月份当成列等。然而，重复数据组不仅限于单个属性。例如，如果你看到列名类似Quantity1、ItemDescription1、Price1、Quantity2、ItemDescription2、Price2.QuantityN、ItemDescriptionN、PriceN的时候，你就可以认定它们使用了重复数据组模式。

![image-20210702152349558][]

基于重复数据组的查询和统计报表会变得很困难。在图1-6的示例中，如果将来需要增加Predecessor的值或者减少Predecessor列的数量，都必须修改当前的设计，添加或删除列，并且还需要修改所有与此表相关的查询语句（视图）、表单和统计报表。记住一个很有用的原则：

*  列昂贵。
 *  行便宜。

如果当前的表设计需要添加或者删除列的方式才能满足将来存储相似的数据的需求，那么你就应该意识到这可能是一个问题。更好的设计是在需要的时候才添加或删除列。在这个例子中，我们创建一个Predecessors表，并把ID的值作为外键的值。为清楚起见，我们还把ID外键的名称修改为DrawingID，如图1-7所示。

![image-20210702152418878][]

![image-20210702152502887][]

UNION查询要求每个SELECT语句的列具有相同的数据类型，并且保持相同的顺序。这意味着在第一个SELECT查询之后就不再需要包括如AS Drawing ID或AS Predecessor之类的写法了：UNION查询仅从第一个SELECT语句中获取列名。

每个SELECT语句中的WHERE子句可以包含不同的条件表达式。取决于数据，我们可能还需要排除空字符串（ZLS）和其他不可打印的字符，例如空字符（’ '）。

UNION查询可以在结尾使用单个ORDER BY子句。还可以指定排序的顺序，如ORDER BY 1，2。这类似于代码清单1-2中的ORDER BY DrawingID，Predecessor。

# 总结 #

*  数据库规范化的目标是消除重复的数据组，并尽可能减少表结构的修改。
 *  通过删除重复的数据组，可以使用唯一索引来防止出现意外的重复数据，并大大简化查询语句。
 *  删除重复的数据组使设计更加灵活，因为添加新的数据组只需要加一条记录，而不用修改表设计增加更多的列。

# 四、每列只存储一个属性 #

在关系术语中，关系（表）应该只表示一个主题或行为。属性（列）只包含一个（通常称为“原子”数据）与该表的主题相关的数据。属性同样也可以作为外键引用其他表的属性，并且外键还提供了与其他表中的一些元组（行）之间的关联关系。

在单个列中存储多个属性的值是不明智的，因为在执行搜索和聚合时很难隔离属性值。原则上讲，应该将关键的属性单独存储在一列中。如表1-1所示，有多个列都存储了多个属性的例子（顺便提一下，示例中的地址是真实存在的地址，但并不是对应作者的实际地 址）。

![image-20210702152605304][]

这样的表存在以下几个问题：

*  按姓氏搜索即使可能也是非常困难的。如果表中不止4条记录，搜索姓氏包含Smith的人，使用LIKE语句加通配符的方式搜索可能返回Smithson或Blacksmith。
 *  你可以按名字搜索，但是必须使用低效率的LIKE语句或先截取名字字符串然后查询。以通配符结尾的LIKE语句虽然可以高效地执行，但是由于名字前面可能还包含称谓（如Mr.），必须在LIKE语句的前面也加上通配符才能找到你想要的数据，但这会导致数据扫描。
 *  按照街道名称、城市、州、省或邮政编码来搜索会很麻烦。
 *  尝试对数据进行分组时（可能合并其他表的章数或页数），会发现很难按照州、省、邮政编码或国家进行分组。

当数据库里的数据是从外部数据源（如电子表格）导入进来的，你就很可能看到类似的数据。但是一般在产品数据库环境中很少见到这种糟糕的表设计。

一种更好的解决方案是创建一个类似于代码清单1-3所示的表。

![image-20210702152705711][]

请注意，我们把街道号字段设置为字符数据类型，因为街道号通常也包含字母和其他字符。例如，一些街道号包含½。在法国，街道号数字后面通常跟着bis字样。同样的情况也出现在邮政编码中，美国的邮政编码都是数字，但是在加拿大和英国，除了数字之外还包括字母和空格。

采用上面建议的表设计，现在数据可以被拆分成每列一个属性，如表1-2所示。

![image-20210702152728174][]

现在，对任何一个或多个单独的属性进行搜索或分组操作都很容易，因为每列只有一个属性。

如果需要重新组合属性，例如查询邮寄地址，在SQL中很容易实现，可以使用字符连接的方式还原完整的地址。如代码清单1-4所示。

![image-20210702152813687][]

我们先前说过，代码清单1-3可能是正确的设计之一，但你可能会感到奇怪，为什么我们建议把街道号与街道地址分开存储。事实上，在大多数应用中，不区分街道号码和街道名称也能工作得很好。你必须根据应用程序的需求来做决定。对于用于土地勘测的数据库，区分街道号和街道名称（可能是“街道”“大道”或“大街”）是至关重要的。在其他一些应用中，分离电话号码的国家代码、区域代码和本地号码可能是很重要的。在分析属性的时候，你需要弄清楚哪部分是重要的，以便能够更细粒度地拆分。

很明显，将属性拆分成单独的列后，按数据的某个部分进行搜索或分组会变得更加容易。同样在报表或打印清单时，重新组合这些属性也会变得很简单。

## 总结 ##

*  正确的表设计是为每个属性分配单独的列，当列包含多个属性时，搜索和分组即使有可能做，也会是极其困难的。
 *  对于某些应用程序，有过滤列中的某部分数据（如地址或电话号码）的需求，这可能会决定列的粒度级别。
 *  当需要重新把属性组合成报表或打印清单时，使用连接。

# 五、理解为什么存储计算列通常有害无益 #

有时候可能会存储计算结果，特别是当计算依赖于其他相关表中的数据时。以代码清单为例。

![image-20210702152925565][]

从表面上看，Order表中加上OrderTotal（可能是Order\_Details表中的Quantity \* Price的计算结果）似乎很合理，因为没有必要在每次需要订单和金额数据时获取相关的行并计算它的结果。这种计算结果的字段可能在数据仓库中行得通，但是在主动数据库中可能会造成比较大的性能影响（具体请阅读第9条）。你可能会发现很难维护数据的完整性，因为必须确保在每次更新、插入或删除Order\_Details行的任何数据时，都必须确保其值被重新计算。

好消息是，许多现代数据库系统都提供了一种维护此类字段的方法，在服务器上执行代码进行计算。让计算结果字段值持续更新的最简单做法是在包含计算列的表上创建触发器。触发器是一段代码，在表插入、更新或删除数据时运行。在代码清单1-5的例子中，需要在Order\_Details表上创建一个用来计算OrderTotal值的触发器。但触发器会消耗大量资源且很容易写错（具体请阅读第13条）。

某些数据库系统提供了一种在创建表时定义计算列的方法，可能比触发器更好，因为将计算列的定义作为表定义的一部分，这样就可以避免编写复杂的触发器代码。某些数据库系统，特别是在最新的版本中，都已经支持这样定义计算列。例如，Microsoft SQL Server提供了AS关键字，后跟一个定义所需计算的表达式。如果计算使用的列来自同一张表，可以简单地将其他列中的表达式作为计算列的定义。如果计算依赖于其他相关表的数据时，某些数据库系统可以通过编写函数的方式来做计算，在创建或者修改表结构时，定义列的AS子句中调用此函数就可以了。代码清单1-6展示了Microsoft SQL Server中创建表和函数的例子。请注意，因为该函数依赖于另外一张表中的数据，因此这个数据是非确定性的，所以不能在计算列上创建索引。

![image-20210702152947862][]

实际上这是一个非常糟糕的做法。因为这个函数是非确定性的，所以这个列不能像表中的其他列那样可以被存储（PERSISTED）。你不能在这种列上创建索引，而且在使用这些列时会造成大量的服务器开销，因为服务器必须为每条记录调用函数进行计算。还不如在你需要的时候使用子查询按OrderID分组然后计算更有效率。

在IBM DB2中有一个类似的功能，关键字是GENERATED。但是，DB2绝对不允许在需要执行查询的函数上创建计算列，因为这样的函数是非确定性的。不过，你也可以使用确定性的函数或表达式定义列。代码清单1-7展示了如何在Order\_Details表中使用计算数量乘以单价的表达式创建一个总价字段。

![image-20210702153007252][]

![image-20210702153019735][]

我们刚花了很长的篇幅示范如何使用计算列，在这一点上，你可能会奇怪为什么本条的标题是“理解为什么存储计算列通常有害无益”。有害无益的原因是：如果这个表用于大型在线数据录入系统，创建计算列可能会对服务器造成巨大的负载，从而影响服务器的响应时间。

如果你使用的是IBM DB2、Microsoft SQL Server或Oracle，还可以在计算列上定义索引，这通常有助于提高基于计算列查询的效率。注意你不能使用代码清单1-6的例子在SQL Server中为计算列创建索引（其他数据库也不行），因为它依赖于数据库中的另外一张表，是不确定性的（请阅读第17条）。

SQL Server中，你还必须做另外一件事，在表达式前面使用PERSISTED关键字。而对于DB2，在表达式上创建索引后就会自动存储。

代码清单1-7的情况，每当更新、插入或删除Order\_Details表中的数据时，被调用函数的值都可能发生变化，值发生变化就会产生额外的服务器开销。终端前录入订单的人可能要经历难以接受的服务器响应时间，因为必须先执行函数来计算和存储索引的值。在代码清单1-6或代码清单1-8中，每次从Orders表中查询该列都会产生额外的服务器开销，因此使用SELECT语句查询包含此计算列并返回较多的数据时，响应时间都可能是令人难以接受的。

## 总结 ##

*  许多数据库系统允许你在创建表时定义计算列，但应该注意性能影响，特别是在使用非确定性表达式或函数的时候。
 *  你还可以像定义普通列一样定义计算列，然后使用触发器来维护，但是编写触发器的代码可能会很复杂。
 *  计算列会对数据库系统产生额外的开销，只有当利大于弊的时候才考虑使用它。
 *  大多数情况下，你希望在计算列上创建一个索引，以消耗更多的存储空间和较慢的更新作为交换，获得一些便利性。
 *  当不能使用索引时，使用视图来做计算通常可以作为在表里创建计算列的替代方法

# 六、定义外键以确保引用完整性 #

正确地设计数据库时，在许多表中都会包含引用相关父表主键的外键。例如，销售订单数据库中的订单表应该包含CustomerID或CustomerNumber列，引用Customers表的相关主键，这样才能识别每个特定订单的客户。

![image-20210702153119295][]

![image-20210702153127496][]

1）当你使用设计器创建新视图或存储过程时，数据库的图形查询设计器知道如何正确地构造JOIN子句。

2）当在“多”端插入或更新数据，或在“一”端更新或删除数据时，数据库系统知道如何强制执行数据完整性约束。

第二点是最重要的，因为你必须确保数据的完整性，例如，不能创建无效或缺少CustomerID的订单。如果你可以修改Customers表的CustomerID值，则需要确保值被更新（使用ON UPDATE CASCADE）到相关订单记录中。如果用户尝试删除已被Orders表引用的Customers记录，要么确保客户记录不被删除，要么同时删除Orders表中的所有相关记录（使用ON DELETE CASCADE）。

要在数据库系统中使用这个重要的功能，需要在使用CREATE TABLE创建“多”端表

![image-20210702153208423][]

![image-20210702153223295][]

![image-20210702153228463][]

在某些数据库系统（特别是Microsoft Access）中，定义引用完整性约束的外键列上会自动创建索引，因此在执行连接查询时性能会有所提升。对于那些不在外键上自动创建索引的数据库系统（例如DB2），最佳实践是也创建索引以优化约束检查。

## 总结 ##

*  外键明显有助于保证相关表之间的数据完整性，确保子表的记录能在父表找到对应的记录。
 *  如果表中存在违反约束的数据，向表中添加FOREIGN KEY约束将失败。
 *  在某些数据库系统中，定义FOREIGN KEY约束会自动创建索引，这样可以提高连接查询的性能。在其他一些数据库系统中，创建索引来覆盖FOREIGN KEY约束必须小心。即使没有索引，一些数据库系统优化器也会特别对待这些列，以提供更好的查询效率。

# 七、确保表间关系的合理性 #

理论上讲，只要两个表中的每组列具有相同的数据类型，就可以为它们创建任何关系。但可以做并不代表就应该做。请阅读图1-9中的有关销售订单数据库的模型图。

表面上看似乎还挺合理。有几张表，每张表都只包含一个主题。我们关注其中三张表：Employees、Customers和Vendors表。如果仔细观察这三张表，会发现表之间有很多相似的字段。大多数情况下，这不会有任何问题，因为三张表中的数据通常是不相同的。

但如果公司的供应商或员工恰巧也是公司的客户，那么这个数据模型就违反了之前我们在第2条讨论的重复数据原则。有些人可能会创建一个单独的Contacts表来解决这个难题，用来存储所有类型的联系人。但是仍然存在不少问题。其中一个问题是，EmployeeID、CustomerID和VendorID的值现在都来自同一个主键ContactID，这样我们就没办法识别这个ID了，例如事实上一个真真正正的供应商碰巧也可能是客户。

![image-20210702153337352][]

某些人可能通过在Customers、Vendors和Employees与Contacts表之间建立一对一关系来解决这个问题，这样每个实体就可以保留自己特有的数据，例如客户不需要ManagerID和VendWebPage字段就可以把它们分开。然而，这意味着使用此数据库结构的应用程序会复杂得多，因为必须有额外的逻辑来验证实体是否存在。如果有的话，是否还需要特定的领域对象来填充数据。毕竟，如果应用程序不事先做重复检查就盲目地插入新记录的话，这些表就没有任何存在的意义。但可以理解的是，并不是所有的公司都愿意为额外的程序复杂性花费更多的时间和金钱。通常情况下，销售产品的公司也不会存在既是客户又是供应商或员工的情况，因此针对这种罕见的情况，偶尔的重复比起简化数据库结构付出的代价要小得多。

让我们考虑这样一个场景：为销售员分配销售区域，然后把这些区域的客户映射到该销售员。一种做法是在Customers表中的CustZipCode列与Employees表中的EmpZipCode列之间创建关联关系。两列拥有相同的数据类型和内容。但是与其在表之间创建关系，还不如在Employees和Customers的邮政编码列上使用连接查询的方式来查找销售员负责的客户。

虽然在Customers表中创建外键EmployeeID来关联客户和销售员会更简单，但实际上会造成更多的问题。其中一个问题是，假设客户搬到另外一个销售区域会怎样？数据录入员可能正确地更新了客户的地址，但可能没有意识到或忘记更新对应的销售员，从而引入新的问题。

最好是创建一个名为SalesTerritory的表，并定义外键EmployeeID，表中的数据表示分配给销售员的邮政编码（TerrZIP字 段）。SalesTerritory表中的每个邮政编码都是唯一的，因为你不会将邮政编码分配给多个销售员。然后从TerrZIP创建与Customers表之间的关系应该是可行的，这样就可以找到销售员管辖区域内的客户了。

相反，如果按照其他条件而不是销售区域的方式为客户分配销售员的话，在Customers表中创建EmployeeID外键可能会是个更好的选择，更能反映客户与销售员之间分配的自由性。这种方式即使在销售区域为默认分配的情况下仍然可以用，但客户可以自行要求分配其他销售员。与前面提到的例子一样，这种方式必定需要额外的编程才能最小化数据输入错误。

当公司需要列出所有已销售产品，并提供每种产品及其全部属性的详细信息时，也会遇到类似的问题。例如对于一个销售木材的公司来说，有一个包含长度、宽度、高度和木材类型的产品表算是比较合理的。毕竟这是一家销售木材的公司。但是对于销售各种商品的零售商店来说，在表里面添加几个很少使用的列看起来不是好主意。我们也不想为每个产品类别单独创建一张表来存储各类别相关的数据。面对这种问题，有些人可能会倾向于创建一个属性列，将产品的属性转换为XML或者JSON文档数据存储在这个属性列里面。如果没有相关业务规则需要在关系表里面拆分产品的属性，这样做倒是没有什么问题。但是如果需要按照属性来做查询的话，创建一个ProductAttributes表，然后将Products表中相关产品的属性列转换成数据行，就可以实现。代码清单1-12展示了一种可行的表设计。

![image-20210702153429671][]

虽然将属性列转换为数据行看起来能解决这个问题，但是现在查询相关产品及其属性的操作会变得更加复杂，特别是多个属性交叉查询的时候。

顺便提一下，上面关于属性的问题表明设计者需要有能力区分什么是结构化数据和半结构化数据。在关系模型中，在添加数据之前，所有的数据必须先结构化，然后整理成列并规定数据类型。这与XML或JSON文档数据之类的半结构数据恰好相反，文档数据不一定需要有相同的结构，即使是在记录级别也是如此。如果在关系建模时遇到困难，可以先思考一下处理的是否是半结构化数据，是否真的有必要在关系模型中拆分它。现在SQL标准已经支持在SQL中使用XML和JSON数据，为你提供了更多的选择，但这些内容超出了本书讨论的范畴。

根据以上所述，业务是衡量数据建模是否正确的标准，并且还需要考虑应用程序的设计。通常这有点难度，人们更倾向于使用应用程序来驱动数据模型设计，而实际应该是恰好相反的。在现实项目中，选择不同的数据模型可能导致应用程序设计的重大变化。这些变化可能会影响应用程序的开发成本与上线时间。

## 总结 ##

*  再三斟酌，为了简化关系模型而合并包含相似字段的表是否真的有意义。
 *  只要对应列数据类型匹配（或可以隐式地强制转换），就可以在两个表之间创建连接，但只有当列都属于同一个业务领域时，关系才是有效的。所以，最理想的连接是两端都具有相同的数据类型和业务领域。
 *  在建模之前，检查你处理的数据是否是结构化数据。如果是半结构化的，则要做特殊的处理。
 *  明确数据模型的目标通常有助于判断给定的设计是否由于简化关系模型和使用此数据模型应用程序的设计导致了复杂性或异常的增加。

# 八、当第三范式不够时，采用更多范式 #

有一个普遍的说法，第三范式能满足大多数的应用程序。许多数据库从业者都听过或者引用过“第三范式就足够了”或“规范化直到难以实现，反规范化直到解决问题”。这些俗语的问题是，它们都暗示着更高的范式需要更多的修改才能实现，但实际上，对于大多数数据库模型，满足第三范式的实体很可能也已经满足了更高的范式。事实上，大多数数据库中的许多示例表都已经满足了第五甚至第六范式，尽管人们还称之为第三范式。因此，我们需要关注的是已经遵循第三范式但是违反了更高的范式的情况。这种情况一般很少见，但确实存在，如果真的遇到了，很容易设计错误，从而产生异常的数据，即使表看似已经满足了第三范式。

判断一个设计遵循第三范式但可能违反更高范式的警告标志是，看一个表是否与其他多个表关联，特别是当表参与了多个多对多的关系时。另外一个判断方法是，如果表包含复合键就有可能违反较高的范式。如果使用的不是自然键而是代理键就要额外注意，后面我们会详细讲解。

简单提一下，前三个范式（也称为BoyceCodd范式）关注关系中属性之间的功能依赖。功能依赖的是指属性依赖于关系中的键。例如，存储电话号码“466.315.0072”的列可以说在功能上依赖于存储姓名“Douglas J. Steele”的列，指明这个电话号码是属于这个人的，其他属性不会影响电话号码与所属者之间的关系。如果电话号码依赖于其他非键属性，那么肯定是录入了错误的数据。

对于第四范式，我们关注的是多值依赖，是两个彼此独立的属性同时依赖关系中同一个键的情况。然后这两个属性存在多种数据组合。这是违反第四范式的典型例子。以表1-3销售员的销售产品表为例。

![image-20210702153547230][]

这个表并非表示每个制造商仅生产两个产品，而是为了说明销售员必须销售制造商产的所有商品。所以，如果Sheila决定开始销售Ace，我们就需要新插入两条数据，一个是Ace的Dicer产品，另外一个是Ace的Whomper产品。如果更新表数据不当，可能会导致异常的数据。所以，为了避免出现这种情况，我们可以按图1-10所示将表进行拆分。

![image-20210702153557045][]

在这个模型里，我们只想找出销售员可能销售的所有产品，然后找出关联的制造商和对应的产品，最后通过连接SalespeopleManufacturers和ManufacturerProducts表的方式找出销售员销售的产品就可以获得与表1-3同样的结果。需要特别注意的是，业务规则规定销售员必须销售制造商生产的所有产品。但在现实生活中，销售员只可能销售制造商生产的部分产品。在这种业务规则下，表1-3中的数据就不算违反第四范式了。这就是为什么说高的范式往往很罕见。大多数业务规则已经让数据模型满足了更高的范式。

第五范式要求候选键可推导出所有连接依赖。以表1-4所示的非规范化的办公室、设备和医生数据为例。

![image-20210702153614623][]

在这个数据模型中，医生（Doctor）工作需要设备（Equipment），我们需要为医生分配有特定设备的办公室（office）。我们假设医生都接受过相关设备的培训，所以把医生分配到他不能使用设备的办公室是没有意义的。但并非所有的医生都接受了同样的培训，有些可能学习的是相对较新的设备，或学习的专业化程度不同，所以并不是每个人都拥有相同的技能。

表中有办公室和设备。虽然有重复但还是相对独立的。具有特定设备的办公司与医生培训过的相关设备没有任何直接关系。可以创建具有6个表的数据模型，如图1-11所示。

![image-20210702153631526][]

注意，有3个基础表Doctors、Equipment和Offices，然后每一对关系之间会有一个连接表：Doctors和Equipment表之间是DoctorEquipment表，Offices和Equipment表之间是OfficeEquipment表，Doctors和Offices之间是DoctorSchedule表。所以，如果增加新的办公室或者为现有的办公室增加新的设备，或改变医生的培训，这些变化都是独立的，不会对它们之间的关系产生任何异常数据。但是，DoctorSchedule表可能会产生异常的数据，可能创建了缺少相应设备的培训的医生或者缺少相应设备的办公室的医生和办公室数据组。但这也存在问题，违反了第五范式。为了修正这一点，我们需要按照图1-12所示修改数据模型。

注意，DoctorSchedule表有两个外键使用的是同一个列EquipmentID。这两个FOREIGN KEY共同约束只有拥有相同设备的医生和办公室才能组合在一起，这样就不用额外的编程来解决，而且还防止了异常的数据。值得注意的是，我们并没有修改表的结构，只是改变了它们之间的关系。

![image-20210702153646597][]

同时值得注意的是，如果不在DoctorSchedule表中添加EquipmentID列的话，那么图1-11所示的模型就已经满足第五范式了。如果仅是将医生分配到办公室，而不管分配医生使用的设备的话，图1-11所示的模型也是可行的。

在这个例子中，另一个需要注意的是，我们使用了复合键。如果在连接表Office-Equipment和DoctorTraining中创建了代理键，则会混淆EquipmentID，就无法实现图1-12中的模型。因此，如果默认使用的是代理键，则必须格外留意模型中是否隐含着某些关键信息。注意任何参与多对多关系的外键，并分析它们是否会对关系产生任何影响。

无损分解是一种用来分析是否违反较高范式的方法。当你有个很大的表，应该可以把其中的某些列分离出来作为单独的表，然后在这个单独的表上执行SELECT DISTINCT，最后再使用LEFT OUTER JOIN与原来的表连接，观察是否能够获得与原表相同的数据。如果连接后分离的表没有任何数据丢失，则原表有可能违反了一些范式，需要进一步检查是否存在异常的数据。表1-5说明了如何分解表1-3中的表。

![image-20210702153703311][]

如果回过头来看前面我们用来说明违反第四和第五范式的例子，你会发现，如果从表1-3中查询一行，SalespeopleManufacturers和ManufacturerProducts表之间的连接就是“有损的”，因为表1-5中的连接结果和修改后的表1-4不匹配。在这种情况下，修改后的表1-3就不再违反第四范式了。类似地，如果EquipmentID不是DoctorSchedule表中的列，就不会有数据丢失，也就不会违反第五范式了。请注意，以上分析是假设表中有足够的数据，才能正确地判断是否存在数据丢失。

## 总结 ##

*  大多数数据模型已经满足了较高的范式。因此，只需要注意某些明显违反高级范式的情况，例如包含复合键的表或者参与了多个多对多关系的表。
 *  第四范式只有在某些特殊情况下才会违反，例如实体上的两个不相关属性的所有可能组合依赖于这个实体。
 *  第五范式要求候选键可推导出所有连接依赖，意味着你应该能够基于各个属性来约束候选键的有效值。这种情况只发生在复合键上。
 *  第六范式是将表的关系减少到只存在一个非关键属性，这样会导致表的数量膨胀，但是可以避免出现空值的列。
 *  无损分解是检测表是否违反较高范式的一个有效工具。

# 九、非规范化数据仓库 #

作为开发人员，我们逐渐意识到数据库规范化的重要性。规范化的表通常比非规范化的表更小且占用的空间更少。数据被拆分成多个小表，小到足以存在缓存中，性能通常也更好。由于相关数据集中在一个位置，更新与插入数据变得更加高效。

由于不存在重复数据，所以很少使用复杂的GROUP BY或DISTINCT查询。

但是，这些关键字也在使用，因为一般的应用程序都是写入密集型的，也就是说写入操作多于读取操作。对于数据仓库，情况则恰好相反：在读取数据时几乎没有写入操作，即使有也远少于读取操作。完全规范化的表有一个问题是：规范化后的数据意味着需要在表之间做连接。连接越多，查询优化器就越难找到最佳的查询执行计划，从而影响查询的性能。

非规范化的数据库在高负荷读取操作下表现很好，因为数据集中在少数几张表里面，几乎很少甚至不需要做表连接，所以查询会很快。包含所有数据的单表也可以使用索引来提高效率。如果在列上正确地创建索引，那么使用索引来过滤和排序数据会很快，而无须读取整个表。此外，因为写入不频繁，所以不必担心太多索引会导致写入性能下降。如果需要，你可以为表中的每列创建索引，以提高搜索和排序的性能。

为了高效地反规范化，你必须充分了解数据并知道它是如何被查询的。

复制表中的标识字段以避免表连接是最简单的非规范化方法。例如，一个规范化的数据库，可能在Customers表中包含用于关联客户经理的外键EmployeeID。如果发票报表里面也需要包含客户经理，那么就需要连接三个表：Invoices、Customers和Employees表。然而，在Invoices表中复制EmployeeID列也可以达到同样的效果。现在，只需要连接Invoices和Employees表就可以了。当然，如果还需要Customers表中的数据，这样做就没什么意义了。

可以进一步非规范化。例如，如果数据仓库中的大多数查询都是按客户的名称搜索发票信息的，那么在Invoices表中添加CustomerID的同时再加上客户名称字段并对其创建索引可能会是个好办法。这样的确违反了规范化原则，因为一个表里面包含了多个主题（发票和客户），并且客户名称会在多条数据中重复出现。但数据仓库的主要目的是简单快捷地查询信息。避免连接查询获取客户名称可以节省大量宝贵的资源。

另一种常见的方式是在其他表中添加说明字段。这不仅能提高查询的性能，还可以保留历史记录。完全规范化的模型只会保留当前最新的状态。例如Customers表保存当前客户的地址。如果这个客户搬家，这个地址就会更新，那么就没办法重新打印客户之前的发票，除非预先保留了客户地址的历史记录。然而，如果在开具发票时将客户的地址在Invoices表也保存一份的话，事情就变得简单多了。

还有一种常见的非规范化做法是存储计算或推导的结果。在Invoices表中创建一个总金额的列，而不是对InvoiceDetails表中的相关数据分别进行汇总。这样不仅可以减少表查询的数量，而且还可以避免多余的重复计算。存储计算结果的另外一个好处是，当计算结果有多种方式时，如果计算结果已经存储在表里面，那么之后所有的查询都会得到相同的结果。

另外一种方法可能要使用重复数据组。常见的需求例如比较每月的销售额，一条记录里面存储所有12个月的数据大大减少了查询的数据量。

请记住，对数据仓库中的数据进行切片或切块有很多不同的方法。数据仓库专家Ralph Kimball提出了三个主要的方法：向下钻取、交叉探查和时间处理。他将“事实表”形容为“企业的度量基础”和“大多数数据仓库查询的最终目标”，但同时他也指出：“如果没有优先级较高的业务需求，没有经过严格的质量保证和多维度的数据约束及分组，事实表也没有多大的用处。”

他描述了如下三种类型的事实表。

1）事务事实表：对应单个时间点上的事实。

2）周期快照事实表：记录固定时间间隔或到某个固定结束时间的活动，例如周期财务报表。

3）累积快照事实表：记录任意开始和结束时间的业务过程，例如订单流程、索赔流程、服务调用流程或大学招生流程。

Kimball提出了另外一个重要的概念，叫作缓慢变化维。正如他所说的，存储在事实表中的大多数基础度量包括与日期维度相关的时间戳和外键，但是比基于活动的时间戳更多的是时间效应。关联到事实表的所有其他维度，包括客户、产品、服务、条款、位置和员工等基础实体也会受到时间变化的影响。有时修改后的描述只是纠正了数据中的错误，但是它也可以表示特定维度成员的描述在某个时间点的真实变化，例如客户或产品。因为这些变化比事实表度量的频率低得多，所以它们被称为缓慢变化维（SCD）。了解这些概念对于设计高效的数据仓库至关重要。

如果你决定对数据进行非规范化操作，请完整地记录你的非规范化过程。详细描述非规范化背后的逻辑和步骤。将来，如果你的团队需要对这些数据进行规范化，那么这份完整准确的记录将能够帮到他们。

## 总结 ##

*  想清楚要复制的数据及原因。
 *  计划如何保持数据同步。
 *  使用非规范化字段重构查询

[image-20210702151806415]: /images/20221005/e4d9fc0fb37b406ba1880f864c41d1ea.png
[image-20210702151925008]: /images/20221005/a13aa0b459cb41ddb7d7f569b4f0bdb6.png
[image-20210702152147583]: /images/20221005/21deec13559a42cba844c80898247df1.png
[image-20210702152228463]: /images/20221005/b4da9f479b9a488ea407b439c03a8b86.png
[image-20210702152243527]: /images/20221005/25a0b3d63c67409188e3bcc74373aaf0.png
[image-20210702152349558]: /images/20221005/5d0409aa99084a48a36e3a10c966e199.png
[image-20210702152418878]: /images/20221005/27b6712a0cbb48e590d8a80d04fb585e.png
[image-20210702152502887]: /images/20221005/f27130f74abf405f905b3f369d238cfa.png
[image-20210702152605304]: /images/20221005/489cc8a746c342a9b2ac986061dc9c87.png
[image-20210702152705711]: /images/20221005/ec0984b70fde48d881b45e7b24adc0be.png
[image-20210702152728174]: /images/20221005/8ca41d6e7f6a4e469d39454f99f79511.png
[image-20210702152813687]: /images/20221005/e0b18def20cd4d058cea6dea9ef6a9b3.png
[image-20210702152925565]: /images/20221005/bbc2f603b4994bbeabc76983e3929b80.png
[image-20210702152947862]: /images/20221005/d29b2f1f272745eaabcce82baf6f4b9c.png
[image-20210702153007252]: /images/20221005/e067d3b9d5004c75a457bef4d6273017.png
[image-20210702153019735]: /images/20221005/b28dd7843af54be49c2d8439beb19b98.png
[image-20210702153119295]: /images/20221005/43b1db2d3ad0411298f63164e9bd1012.png
[image-20210702153127496]: /images/20221005/367be0b7bc8d4e79b83d2a249c1be77a.png
[image-20210702153208423]: /images/20221005/0f850932f35840b7a9edf88e5ca86bc5.png
[image-20210702153223295]: /images/20221005/213ae2285c824b88bb345419768e69aa.png
[image-20210702153228463]: /images/20221005/7132217c003347faa73f5809c8720e70.png
[image-20210702153337352]: /images/20221005/5a0fad9bb5264d498f8d72bd9771bb06.png
[image-20210702153429671]: /images/20221005/e7d2f56f622c4446bbe6034adc25b5f6.png
[image-20210702153547230]: /images/20221005/038027f5e8f9483a9118accc1c723f5e.png
[image-20210702153557045]: /images/20221005/2900d41535be4b81af669d7c9ed2efca.png
[image-20210702153614623]: /images/20221005/538c7ac7430b47c0b6fcf53f8e0e0d26.png
[image-20210702153631526]: /images/20221005/740eb49026824d13b81613203163074d.png
[image-20210702153646597]: /images/20221005/27372dec14ea49b48ba4d087e986b269.png
[image-20210702153703311]: /images/20221005/8119017cab784c56aacfe80ef72dc34c.png