Appearance
数据库设计
实际应用中，需求分析阶段需要做什么？
在《数据库设计步骤》一节我们介绍了设计数据库的基本步骤，下面将详细介绍需求分析阶段。
需求分析阶段的重点是调查、收集并分析客户业务的数据需求、处理需求、安全性与完整性需求。
常用的需求调研方法有在客户的公司跟班实习、组织召开调查会、邀请专人介绍、设计调查表并请用户填写和查阅与业务相关的数据记录等。
常用的需求分析方法有调查客户的公司组织情况，各部门的业务需求情况、协助客户分析系统的各种业务需求和确定新系统的边界。
无论数据库的大小和复杂程度如何，在进行数据库的系统分析时，都可以参考下列基本步骤：
- 收集信息
- 标识实体
- 标识每个实体需要存储的详细信息
- 标识实体之间的关系
1. 收集信息
创建数据库之前，必须充分理解数据库需要完成的任务和功能。简单来说，就是要了解数据库中需要存储哪些信息（数据），实现哪些功能。
下面以酒店管理系统为例，我们需要了解酒店管理系统的具体功能，以及在后台数据库中保存的数据，如以下需求：
- 酒店为客人准备充足的客房，后台数据库需要存放每间客房的信息，如客房号、客房类型、价格等。
- 客人在酒店入住时要办理入住手续，后台数据库需要存放客人的相关信息，如客人姓名、身份证号等。
2. 标识实体
在收集需求信息后，必须标识数据库要管理的关键对象或实体。实体可以是有形的事物，如人或产品，也可以是无形的事物，如商业交易、公司部门或发薪周期。
在系统中标识这些实体以后，与它们相关的实体就会条理清楚。以酒店管理系统为例，我们需要标识出系统中的主要实体。
- 客房：单人间、标准间、三人间、豪华间和总统套房。
- 客人：入住酒店客人的个人信息。
注意：实体一般是名词，一个实体只描述一件事情，不能重复出现含义相同的实体。
数据库中每个不同的实体都拥有一个与之相对应的表，按照以上的酒店管理系统需求，在酒店管理系统数据库中，会对应至少两张表，分别是客房表和客人表。
3. 标识每个实体需要存储的详细信息
将数据库中的主要实体标识为表的候选实体以后，就要标识每个实体存储的详细信息，也称为该实体的属性，这些属性将组成表中的列。简单的说，就是需要细分出每个实体中包含的子成员信息。
下面以酒店管理系统为例，逐步分解每个实体的子成员信息，如下：
- 客房：客房号、客房类型、客房状态、客房描述、床位数、入住人数、价格
- 客人：客人姓名、身份证号、客人编号、入住日期、结账日期、押金、总金额
在进行实体属性分解时，含义相同的成员信息不能重复出现，如联系方式和电话等。
每个实体对应一张表，实体中的每个子成员分别对应表中的每一列。例如，从上述关系可以看出客人应该包含姓名和身份证号等列。
4. 标识实体之间的关系
关系型数据库有一项非常强大的功能，即它能够关联数据库中各个项目的相关信息，不同类型的信息可以单独存储，但是如果需要，数据库引擎还可以根据需要将数据组合起来。
在设计过程中，要标识实体之间的关系，首先需要分析数据库表，确定这些表在逻辑上是如何相关的，然后添加关系列建立起表之间的连接。
以酒店管理系统为例，客人与客房有主从关系，我们需要在客房实体中标明其入住的客房号。
绘制E-R图：DBA和程序猿的必备技能
在《数据库需求分析阶段详解》一节我们讲解了如何解决客户的业务和数据处理需求。需求分析阶段过后，就进入了概要设计阶段。
在概要设计阶段，我们需要和项目团队中的其他成员及客户进行沟通，讨论数据库的设计是否满足客户的业务和数据处理需求。
数据库设计和机械、建筑等行业一样，机械行业需要机械制图，建筑行业需要施工图，而数据库设计也需要图形化的表达方式——E-R图（Entity-Relationship），也可以称为实体-关系图。
实体关系图构成要素
E-R图是设计数据库的工具之一，一般情况下，用于建立数据库的概念模型。实体、属性和关系是构成 E-R图的基本要素。
1. 实体
实体是指现实世界中客观存在并可以相互区分的对象或事物。就数据库而言，实体往往指某类事物的集合。可以是具体的人和事物，也可以是抽象的概念、联系。例如，酒店管理系统中的客房（如 1008 客房、1018 客房等）、客人（如张三、李四、王五）等。
实体一般是名词，对应表中的一行数据。例如，用户张三是一个实体，他对应于客人表中“张三”所在的一行数据，包括客人姓名、身份证号等信息。严格的说，实体用来指表中的一行特定数据。但在开发时，我们也常常把表称为一个实体。
2. 属性
属性可以理解为实体的特征。例如，”客人“这一实体的属性有入住日期、结账日期和交付的押金等。属性用来对应表中的列。
3. 关系
关系是两个或多个实体之间的关联关系。
关系可分为以下 4 种类型，例如对于实体 X 和 Y 之间的关系就必须为以下任意一种：
1）一对一
X 中的一个实体最多与 Y 中的一个实体关联，并且 Y 中的一个实体最多与 X 中的一个实体关联。
比如，每辆汽车同一时刻只能占用一个车位，同一时刻每个车位也只停放一辆汽车，那么，汽车实体与车位实体之间就是一对一的关系。一对一关系也可以表示为 1 : 1。
2）一对多
X 中的一个实体可以与 Y 中任意数量的实体关联，Y 中的一个实体最多与 X 中的一个实体关联。
比如，一间客房可以入住多位客人，但是一位客人只能入住一间客房，所以，客房实体和客人实体之间就是典型的一对多的关系。一对多关系也可以表示为 1 : N。
3）多对一
X 中的一个实体最多与 Y 中的一个实体关联，Y 中的一个实体可以与 X 中的任意数量的实体关联。
比如，客房实体和客人实体之间是典型的一对多关系，反过来说，客人实体和客房实体之间就是多对一的关系。
4）多对多
X 中的一个实体可以与 Y 中的任意数量的实体关联，反之亦然。
比如，图书馆的每本书可以借给多个读者，每个读者也可以借阅多本书，那么，图书实体和读者实体之间就是典型的多对多关系。再如，产品和订单之间也是多对多关系，每个订单中可以包含多个产品，一个产品也可以出现在多个订单中。多对多关系也可以表示为 M : N。
绘制实体关系图
E-R 图以图形的方式来表示数据库的整个逻辑结构，如下：
- 实体：用矩形表示
- 属性：用椭圆形表示
- 关系：用菱形表示
- 使用直线连接属性和实体，以及连接实体和关系
E-R（实体关系）图的绘制一般用软件辅助，画实体关系图常用的软件有：WORD、亿图图示、Visio 等，PPT 也可以绘制出美观的实体关系图。
下图为客人实体和客房实体之间的联系。
在 E-R图中，直线可以有方向（在末端有一个箭头）的来表示实体之间的关系。
下面演示如何绘制不同关系的 E-R 图。其中一些语言可能不符合实际情况，另外，为了方便大家观看，我们省略了实体中的属性，只留下了实体和关系。
1）一对一
上图表示为一位客人最多入住一间客房，一间客房最多被一位客人入住。
2）一对多
上图表示一位客人可以入住多间客房，一间客房最多被一位客人入住。
3）多对一
上图表示一位客人最多入住一间客房，一间客房可以被多位客人入住。
4）多对多
上图表示一位客人可以入住多间客房，一间客房也可以被多位客人入住。
为了画图更加方便，也可以用 1 代替箭头，用*代替直线。如多对一还可以如下：
绘制 E-R 图后，我们还需要反复与客户进行沟通，让客户提出修改意见，以确认系统中的数据处理需求是否正确完整。
数据库逻辑结构设计阶段（非常重要）
我们在概要设计阶段解决了客户的需求，并绘制了 E-R图。逻辑结构设计的任务，就是把在概要结构设计阶段建立的基本 E-R 图，按选定的关系数据模型的原则转换成相应的数据库模型图。
本节将介绍如何将 E-R图转化为关系模型和数据库模型图，下一节我们介绍如何判断数据表结构设计是否规范。
关系数据库模式
用二维表的形式表示实体与实体间关系的数据模型称为关系模型。关系数据库模式是对关系数据库结构的描述，或者说是对关系数据库框架的描述。一个关系通常对应一张表。
一般情况下，我们把关系模式表示为R(U) 或 R(A,B)。其中，R 表示关系名，U 表示属性集合，A、B 代表 U 中的属性。
将 E-R 图转换为关系模式的步骤如下。
1）把每个实体都转化为关系模式 R(A,B) 形式
以酒店管理系统为例，实体“客人”和“客房”分别可以使用关系模式表示如下。
- 客房（客房号，客房描述，客房类型，客房状态，床位数，入住人数，价格）。
- 客人（客人编号，客人姓名，身份证号，入住日期，结账日期，押金，总金额）。
2）建立实体间关系的转换
实体间的关系分成一对一、一对多、多对多三种，当两个实体各自转化为关系模式后，实体间关系的转换如下。
- 一对一的转换：把任意实体的主键放到另一个实体的关系模式中。
- 一对多的转换：把关系数量为 1 的实体的主键放到关系数量为 N 的实体关系模式中。
- 多对多的转换：把两个实体中的主键和关系的属性放到另一个关系模式中，会多生成一个关系模式。
酒店管理系统中客房与客人的关系为一对多关系，转换后的结果如下。
- 客房（客房号，客房描述，客房类型，客房状态，床位数，入住人数，价格）。
- 客人（客人编号，客人姓名，身份证号，入住日期，结账日期，押金，总金额，客房号)。
上述关系模式中含有下划线的属性代表主属性，在表中作为主键，加粗属性为外键。
数据库模型图
数据库模型图主要用来说明数据库有哪些表，表中有哪些属性以及表与表之间的关联关系。
将 E-R 图转化为数据库模型图主要执行以下 4 步：
- 在 Visio 中新建数据库模型图
- 添加实体，将 E-R图中各实体转化为对应的表
- 将各属性转化为各表对应的列，定义字段名称、数据类型等
- 添加实体之间的映射关系
添加实体之间的映射关系，具体步骤如下：
- 添加 GuestRecord（客人）实体和 Room（客房）实体。
- 为 GuestRecord 表添加外键约束列 RoomID（客房号），对应 Room 表中的 RoomID 列。
为了数据编码的兼容性，建议使用英文字段。为了直观可见，我们在英文括号内注明了对应的中文含义。下面将 E-R 图中的“客房”和“客人”两个实体转换为数据库模型图，如下图所示。
图中的 PK 表示表的主键列，FK 表示外键列。需要注意的是，表中的 ID 编号列只能用作主键或外键，否则该列没有实际含义。如客人表中的 GuestID 列，客房表中添加的 RoomID 列。
在数据库模型图中也可以很好的体现实体之间的映射关系。比如，客房和客人之间是一对多关系，对于一对多关系的两个实体，一般会各自转换为一张表，并且后者对应的表引用前者对应的表，即客人（GuestRecord）表中的客房号来自客房（Room）表中的客房号，它们之间应建立主键、外键关系，如上图所示。
一般来说，一对多关系是一个表中的主键对应另一个表中可重复字段，主键的值是不能重复的，而关联的字段是可以重复的，这样就会存在一个值对应一个值或者一个值对应多个值。在一对一关系中，一般是一个主键对应一个不可重复的字段，显然只能一个值对应一个值。
多对多映射关系也是比较常见的。要表示多对多关系，除了将多对多关系中的两个实体各自转换为表外，一般还会创建第三个表，称为连接表。它将多对多关系划分为两个一对多关系，并且将这两个表的主键都插入到第三个表中。
例如，订单表和产品表有多对多关系，这种关系通常通过与“订单明细”表建立两个一对多关系来定义。一个订单可以有多个产品，每个产品可以出现在多个订单中。关于这一点可以在以后的数据库设计实例中慢慢理解。
结合实例，彻底搞懂数据库设计的三大范式
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中，这种规则就是范式。范式是符合某一种级别的关系模式的集合。关系型数据库中的关系必须满足一定的要求，即满足不同的范式。
目前关系型数据库有六种范式，分别为：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、第四范式（4NF）、第五范式（5NF）和第六范式（6NF）。要求最低的范式是第一范式。第二范式在第一范式的基础上又进一步的添加了要求，其余范式依次类推。
一般说来，数据库只需满足第三范式就行了，而通常我们用的最多的就是第一范式、第二范式、第三范式，也就是接下来要讲的“三大范式”。
1）第一范式
第一范式（1NF）用来确保每列的原子性，要求每列（或者每个属性值）都是不可再分的最小数据单元（也称为最小的原子单元）。
例如，客人住宿信息表 （姓名, 客人编号, 地址, 客房号, 客房描述, 客房类型, 客房状态, 床位数, 入住人数, 价格）。
其中，“地址”列还可以细分为国家、省、市、区等，甚至有的程序还把“姓名”列也拆分为“姓”和“名”等。如果业务需求中不需要拆分“地址”和“姓名”列，则该数据表符合第一范式，如果需要将“地址”列拆分，则下列写法符合第一范式：
客人住宿信息表（姓名, 客人编号, 国家, 省, 市, 区, 门牌号, 客房号, 客房描述, 客房类型, 客房状态, 床位数, 入住人数, 价格）。
2）第二范式
第二范式（2NF）在第一范式的基础上更进一层，要求表中的每列都和主键相关，即要求实体的唯一性。如果一个表满足第一范式，并且除了主键以外的其他列全部都依赖于该主键，那么该表满足第二范式。
客人住宿信息表中的数据主要用来描述客人住宿信息，所以该表主键为（客人编号，客房号）:
- “姓名”列、“地址”列➡“客人编号”列。
- “客房描述”列、 “客房类型”列、“客房状态”列、“床位数”列、“入住人数”列、“价格”列➡“客房号”列。
其中，“➡”符号代表依赖。以上各列没有全部依赖于主键（客人编号，客房号），只是部分依赖于主键，不符合第二范式。
使用第二范式后，客人住宿信息表可以分解成以下两个表：
- 客人信息表（客人编号，姓名，地址，客房号，入住时间，结账日期，押金，总金额），主键为“客人编号”列，其他列都全部依赖于主键列。
- 客房信息表（客房号，客房描述，客房类型，客房状态，床位数，入住人数，价格），主键为“客房号”列，其他列都全部依赖于主键列。
3）第三范式
第三范式（3NF）在第二范式的基础上更进一层，第三范式是确保每列都和主键列直接相关，而不是间接相关，即限制列的冗余性。如果一个关系满足第二范式，并且除了主键以外的其他列都依赖于主键列，列和列之间不存在相互依赖关系，则满足第三范式。
为了更好的理解第三范式，这里我们需要了解传递依赖。假设A、B 和 C 是关系 R 的三个属性，如果 A➡B 且 B➡C，则从这些函数依赖中，可以得出 A➡C。如上所述，依赖 A➡C 称之为传递依赖。
以第二范式中的客房信息表为例，初看该表时没有问题，满足第三范式，每列都和主键列“客房号”相关，再细看会发现：
- "床位数” 列、“价格”列➡“客房类型”列。
- “客房类型”列➡“客房号”列。
- “床位数”列、“价格”列➡“客房号”列
为了满足第三范式，应该去掉“床位数”列，“价格”列和“客房类型”列，将客房信息表分解为如下两个表。
- 客房表（客房号，客房描述，客房类型编号，客房状态，入住人数）
- 客房类型表（客房类型编号，客房类型名称，床位数，价格）
主键与外键在多表中的重复出现不属于数据冗余，非键字段的重复出现才是数据冗余。在客房表中客房状态存在冗余，需要进行规范化，规范化以后的表如下：
- 客房表（客房号，客房描述，客房类型编号，客房状态编号，入住人数）。
- 客房状态表（客房状态编号，客房状态名称）
最后，满足三大范式的 E-R 图如下所示：
满足三大范式的数据库模型图如下所示：
4）反范式化
不满足范式的数据库设计，就是反范式化。
我们需要知道对于项目的最终用户来说，用户关心的是方便，清晰的数据结果。所以在设计数据库时，设计人员和客户在数据库的设计规范化和性能之间会有一定的矛盾。
上面我们通过三大范式将客房表分解出两个表，为了满足客户的需求，最终可能需要通过三个或四个表之间的连接查询，来得到客户需要的数据结果，插入数据同样如此，对于客户输入的数据，我们需要分开插入到三个或四个不同的表中。
由此可以看出，为了满足三大范式，我们的数据操作性能会受到相应的影响。
所以，在实际的数据库设计中，既要考虑三大范式，避免数据的冗余和各种数据操作异常，又要考虑数据访问性能。为了减少表连接，提高数据库的访问性能，也可以允许适当的数据冗余列，这也许就是最合适的数据库设计方案。
比如，有一张存放商品的基本表，数据表中包括“单价”、“数量”“金额”等字段。“金额”这个字段就说明该表的设计不满足第三范式，因为“金额”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。
与第三范式中介绍的冗余相比，前面介绍的冗余属于低级冗余，我们反对低级冗余，但这里的冗余为高级冗余，目的是提高数据的处理速度，增加“金额”列后，可以提高查询统计的速度，这是以空间换取时间的做法。
注意：不要轻易违反数据库设计的规范化原则，如果处理不好，可能会适得其反，使应用程序运行速度更慢。
优缺点
最后我们来总结一下范式化和反范式化的优缺点。
1）范式化
优点如下：
- 减少数据冗余
- 范式化后的表中只有很少的重复数据，更新时只需要更新较少的数据，所以范式化的更新操作比反范式化更快
- 范式化的表通常比反范式化更小
缺点如下：
- 范式化的表在查询时经常需要很多的关联，这回导致性能降低
- 增加了索引优化的难度
2）反范式化
优点如下：
- 可以减少表的关联
- 可以更好的进行索引优化
缺点如下：
- 数据表存在数据冗余及数据维护异常
- 对数据的修改需要更多的成本
不使用三大范式会对数据库造成什么影响？
我们在《数据库设计的三大范式》一节讲解了三大范式，本节主要介绍不使用三大范式会对设计数据库有什么影响，会出现什么问题。
这里可以将三大范式理解为：设计数据库时需要遵循的规则，可以有效的帮助我们建立冗余小且结构合理的数据库。
在概要设计阶段，同一个项目，10 个设计人员可能设计出 10 种不同的 E-R 图。不同的角度可以标识出不同的实体，实体又包括不同的属性，自然就设计出了不同的 E-R图。那么怎样审核这些设计图呢？如何评审出最优的设计方案呢？答案就藏在后面的内容里。
下面以某酒店的客人住宿信息表为例来介绍，该表用于保存酒店提供住宿的客房信息，如表 1 所示。
表 1 客人住宿信息表
| 客人编号 | 姓名 | 地址 | 客房号 | ... | 客房描述 | 客房类型 | 客房状态 | 床位数 | 价格 | 入住人数 |
|---|---|---|---|---|---|---|---|---|---|---|
| C1001 | 张三 | Addr1 | 1001 | ... | A栋1层 | 单人间 | 入住 | 1 | 128.00 | 1 |
| C1002 | 李四 | Addr2 | 2002 | ... | B栋2层 | 标准间 | 入住 | 2 | 158.00 | 2 |
| C1003 | 王五 | Addr3 | 2002 | ... | B栋2层 | 标准间 | 入住 | 2 | 168.00 | 2 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| C8006 | A1 | Addrm | 8006 | ... | C栋3层 | 总统套房 | 入住 | 3 | 1080.00 | 3 |
| C8008 | A2 | Addrn | 8008 | ... | C栋3层 | 总统套房 | 空闲 | 3 | 1080.00 | 0 |
从用户的角度来说，将所有信息放在一个表中很方便，因为这样查询数据库可能会比较容易，但是上述表具有下列问题。
1. 信息重复（冗余）
上表中“客房类型”“客房状态”和“床位数”列中有许多重复的信息，如“标准间”“入住”等。信息重复会造成存储空间的浪费及一些其他的问题。比如，不小心输入了“标准间”和“标间”或“总统套房”和“总统套”，那么它们在数据库中将表示四种不同的客房类型。
2. 更新异常
冗余信息不仅浪费存储空间，还会增加更新的难度。如果需要将“客房类型”修改为“标间”而不是“标准间”，则需要修改所有包含该值的行。如果由于某种原因，没有更新所有行，那么数据库中会出现两种客房类型，一个是“标准间”，另一个是“标间”，这种情况被称为更新异常。
3. 插入异常(无法表示某些信息)
从表 1 中我们会发现 2002 和 2003 客房的居住价格分别是 168 元和 158 元。尽管这两间客房都是标准间类型，但它们的“价格”出现了不同，这样就造成了同一个酒店相同类型的客房价格不同，这种问题被称为插入异常。
4. 删除异常（丢失有用的信息）
在某些情况下，当删除一行时，可能会丢失有用的信息。例如，如果删除客房类型为“1001”的行，就会丢失客房类型为“单人间”的账户的信息，该表只剩下两种客房类型，即“标准间”和“总统套房”。当查询有哪些客房类型时，将会误以为只有“标准间”和“总统套房”两种客房类型，这种情况被称为删除异常。