设计一个结构良好的数据库是需要耗费不少时间和心力的，但这是十分必要的，设计良好的数据库可以快速地查询到想要的数据并且有很好的扩展性（很容易满足新的业务需求），相反，一个设计糟糕的数据库可能需要大量维护且查询又慢又麻烦，Mosh之前的一家公司的数据库就做得很糟糕，有些储存程序有上千行代码而且有些查询执行时间长达数分钟，所以，拥有设计良好的数据库是非常重要的。

这一章将系统性地逐步讲解如何设计一个结构良好的数据库

2. 数据建模 Data Modelling?

这一节讲数据建模，即为想要储存进数据库的数据建立模型的过程，其中包含4步：

1. Understand the requirements 理解需求

第1步是理解和分析商业/业务需求，遗憾是很多程序员跳过了这一步就急着去设计数据库里的表和列了，实际上，这一步是最关键的一步，你对问题理解的越透彻，你才越容易找到最合适的解决方案，设计数据库也一样。所以，在动手创建表和列之前，要先完整了解你的业务需求，包括和产品经理、行业专家、从业人员甚至终端用户深入交流以及收集查阅与该问题领域相关的表、文件、应用程序、数据库，以及其他相关的任何信息或资料

2. Build a conceptional model 概念建模

当收集并理解了所有相关信息后，下一步就是为业务创建一个概念性的模型。这一步包括找出/识别/确认（identify）业务中的实体/事物/概念（entities/things/concepts）以及它们之间的关系。概念模型只是这些概念的一个图形化表达，用来与利益相关方交流和达成共识

3. Build a logical model 逻辑建模

创建好概念模型后，转而创建数据模型（data model）或数据结构（data structure for storing data），即逻辑建模。这一步创建的是不依赖于具体数据库技术的抽象的数据模型，主要是确认所需要的表和列以及大体的数据类型

4. Build a physical model 实体建模

实体建模指的是将逻辑模型在具体某种DBMS上加以实现的过程，相比于逻辑模型，实体模型会确定更多细节，包括各表主键的设定，各列在某一DBMS下特定的具体的数据类型，是否有默认值，是否可为空，还包括储存过程和触发器等对象的创建。总之，实体模型是在某一特定DBMS下对数据模型非常具体的实现

以上就是数据建模的流程

3. 概念模型 Conceptual Models?

案例

想要建一个销售在线课程的网站，用户可以注册一项或多项课程，课程可以有诸如 "frontend（前端）" "backend（后端）" 这样的标签

对于一个线上课程网站来说，重要的概念/实体有哪些？很容易想到有学生（student）和课程（course）

我们需要一种将实体及其关系可视化的方法，一种是实体关系图（Entity Relationship, ER），一种是统一建模语言（Unified Modeling Language，UML），这里我们用实体关系图（ER），使用的工具是?http://draw.io

步骤如下：

建立学生实体并确定相关属性，如姓名、电子邮件、注册时间
建立课程实体并确定相关属性，如课程名、价格、老师、标签
建立两个实体间的关系，暂时先用多对多连线（概念模型里只是画好连线，逻辑建模时再考虑连线的类型），加上 enrolls 标签表示两者间的关系是“学生→注册 →课程”

注意

建模是个迭代过程，不可能第一次就建立完美模型，需要在理解需求和模型设计之间不断反复，多次调整。比如这里的学生属性，可以先确定个大概，之后可以根据需要再进行增删修改

小结

概念模型主要是从很高的视角来总览业务需求，识别业务中的实体/事物/概念以及他们彼此间的关系，通常这些实体包括人、事件、地点等

这一步暂不考虑数据类型和具体的DBMS这样的技术细节，只是从概念上总揽全局，目的是和业务人员交流，保持理解一致，避免鸡同鸭讲

4. 逻辑模型 Logical Models

案例

接前面线上课程网站的例子，对概念模型逻辑化的过程如下：

1. 细化实体间关系：

考虑学生和课程的关系，首先这是一种多对多关系（通常意味着需要进一步细化），其次了解到业务上有如下需求：

需要记录学生注册特定课程的日期
课程价格是变化的，需要记录学生注册某门课程时的特定价格

这些属性相对于学生和课程而言都是一对多关系，不管放在学生还是课程身上都不合适，所以，应该为学生和课程之间的关系，即?注册课程的事件?本身另外设立一个实体 enrollmemt，上面的注册日期和注册价格都应该是这个 enrollment 注册事件的属性

2. 调整字段并大体确定字段的数据类型：

姓名（name）最好拆分为姓和名 (first_name 和 last_name)，同理，地址应该拆分为省、市、街道等等小的部分，这样方便查询。注意课程里的 tags 标签字段不是一个好的设计，之后讲归一化时再来处理
这里的数据类型只需确定个大概即可，如：是 string，float 而非 VARCHAR, DECIMAL。等到下一步实体模型里再来确定某个DBMS下的具体数据类型

逻辑模型是在概念模型的基础上，在不依赖特定数据库系统的前提下确定数据结构，包括细化实体间的关系（常常要为关系创造新的实体），调整字段设置，确定大体的数据类型。总之，逻辑模型会基本确立数据库中的表、列以及表间关系。

5. 实体模型 Physical Models?

实体模型就是逻辑模型在具体DBMS的实现，这里我们用MySQL实现前面线上课程网站的逻辑模型

在?Workbench-file-new model?新建数据库模型，右键 edit 修改数据库名字为 school

上方用 add diagram 作 EER 图，这里 EER 表示 Enhanced Entity Relationship 增强型实体关系图。为三个实体创建三张表，设定表名、字段、具体的数据类型、是否可为空（即是否为必须字段？），是否有默认值（主键设定之后再讲）。有几个注意点：

表名：
之前逻辑模型里表名用单数，但这里表名用复数。这只是一种惯例，单复数都行，关键是要保持一致。
如果团队有相关惯例就去遵守它，即便那不够理想，也别去破环惯例，否则沟通和维护成本会大大增加，你需要不断去想该用单数还是复数
字段名：
以 enrollments 表为例，注册事件的属性应该是 date日期和 price价格而非 enrollment_date注册日期和 enrollement_price注册价格，不要将表名前缀加上字段上造成不必要的麻烦，保持精简（keep things simple）
数据类型：
数据类型要根据业务需要来，例如，和业务人员确认后发现课程价格最高是999美元，所以 price价格就可以设定为 DECIMAL(5,2)，之后如果需求变了了也可以随时更改，不要一上来就设定DECIMAL(9,2)，浪费磁盘，注意尽可能节省空间（keep things small）

小结

实体模型是逻辑模型在特定DBMS上的实现，主要是一些技术上的细化，包括确定字段具体数据类型和性质（能否为空等），设置主键等

6. 主键 Primary Keys?

主键就是能唯一标识表中每条记录的字段

设定 students 表的主键：

不管是 first_name 还是 last_name 都不能唯一标识每条记录，它们两个合起来作为联合主键也不行，因为两个人全名相同也是可能的（都叫 Tom Smith）。Email 也不适合作主键，首先太长了，之后需要作为外键复制到其他表会很浪费资源，而且 Email 也可能改变。

总之主键要短，可唯一标识记录，且永不改变。我们增加一个 student_id 作为主键，类型设为 INT（最大可表示2亿，一般足够了，但记得总是根据具体的需求决定），设为主键后自动变为不可为空，另外还要设定 AI（Auto Incremental）自动递增，这样会方便许多，不要担心主键唯一性的问题，最后我们把主键拖到表的第一列让表的结构看起来更清晰

设定 courses 表的主键：

增加一个 course_id 作为主键，其它和 student_id 一样

7. 外键 Foreign Keys?

注意 enrollments 表的特殊性，它可以说是 students 和 courses 的衍生表，先要有学生和课程，才能有学生注册课程这一事件，后者表述的是前两者的关系，学生和课程是因，注册课程这一事件是果

MySQL里可以通过一对一或一对多两种连线表达这种先后关系/因果关系并自动建立外键，其中学生和课程被称作父表或主键表，注册事件被称作子表或外键表，外键是子表里对父表主键的引用

几个细节：

连线时记不得先连主表还是子表可以看状态栏的提示
MySQL自动添加的外键会带父表前缀，没必要，建议去掉

可以看到，相对于逻辑模型，实体模型有更多实现细节，包括设置字段具体类型和性质以及根据表间关系确定主键和外键

现在，根据表间关系给 enrollments 表添加了 student_id 和 course_id 两个外键，enrollments 的主键设置有两个选择：

将这两个外键作为联合主键
另外设置一个单独的主键 enrollment_id

两种选择各有优缺点，以联合主键为例：

好处是可以避免重复的注册记录，即可以防止同一个学生重复注册同一门课程，因为主键（这里是联合主键）是唯一不可重复的，这可以防止一些不合理的数据输入
坏处是如果 enrollments 未来有新的子表，就需要复制两个字段（而不是 enrollment_id 一个字段）作为外键，这也不一定是很大的麻烦，要根据数据量以及子表是否还有子表等情况来考虑，在一定情况下可能会造成不必要冗余和麻烦

但目前来说，没有为 enrollments 建立子表的需求，永远不要为未来不知道会不会出现的需求进行设计开发，如果之后需要的话也可以通过脚本修改表结构，也不会很麻烦，所以目前的情况，用联合主键就好了。在 enrollments 表里把两个外键的黄钥匙都点亮，即成为联合主键

8. 外键约束 Foreign Key Constraints?

有外键时，需要设置约束以防止数据损坏/污染（不一致）

在 enrollements 表设计模式里，打开 Foreign Keys 标签页，可以看到两个外键，以?fk_子表_父表?的方式命名，名称后可能有数字，是MySQL为了防止外键与其他外键重名自动添加的，这里没必要，可去掉。右边 Foreign Key Options 可分别选择当父表里的主键被修改或删除（Update / Delete）时，子表里的外键如何反应，有4种选项：

CASCADE
瀑布/串联/级联，表示随着主键改变而改变，如主键某学生的 student_id 从1变成2，则该学生的所有注册课程记录的 student_id 也会全部变为2 （注意主键一般也最好是永远不要变的，这里讨论的是特殊情况）
RESTRICT / NO ACTION
两者等效，作用都是禁止更改或删除主键。如：对于有过注册记录的课程，除非先删除该课程的注册购买记录，不然不能在 courses表里删除该课程的信息
SET NULL
就是当主键更改或删除时，使得相应的外键变为空，这样的子表记录就没有对应的主键和对应的父表记录了（no parent），被称为孤儿记录（orphan record），这是垃圾数据，让我们不知道是谁注册的课程或不知道注册的是什么课程，一般不用，只在极其特殊的情况可能有用。

经验法则

通常对于 UPDATE, 设置为 CASCADE 级联，随之改变

对于 DELETE，看情况而定，可能设置为 CASCADE 随之删除也可能设置为 RESTRICT / NO ACTION 禁止删除。

不要死板，永远按照业务/商业需求来选择，这也正是为什么之前强调“理解业务需求”是最重要的一步。比如我们课程注册记录里包含购买价格信息，则应该禁止删除，否则之后想统计某课或某时间段收入信息就会缺数据，相反如果只是个用户登录并设定一系列提醒的软件，可能允许用户注销并删除所有提醒就没什么大不了的，但万一我们需要这些提醒记录来进行统计，那又应该设置为禁止删除，总之一定要根据具体业务需求来（always check with the business）

9. 数据库规范化 Normalization?

正式建立数据库前我们先要检查并确定现在的设计是最优化的（optimal），关键是没有任何冗余或重复。重复数据会占用更多空间并且使得增删查改的操作复杂化，比如，如果用户名在多处出现的话，一旦更改用户名就要到多处更改否则就会使得数据不一致，出现无效数据。

为了防止重复冗余，需要遵循数据库设计的7大规则或者说7大范式，每一条都是建立在你已经遵循了前一条的基础上。实际上，99%的数据库之需要遵循前三大范式就够了，其他几个并没有那么重要。

补充：维基百科——数据库规范化

数据库规范化，又称正规化、标准化，是数据库设计的一系列原理和技术， 以减少数据库中数据冗余，增进数据的一致性。关系模型的发明者埃德加·科德最早提出这一概念，并于1970年代初定义了第一范式、第二范式和第三范式的概念，还与Raymond F. Boyce于1974年共同定义了第三范式的改进范式——BC范式。
除外还包括针对多值依赖的第四范式，连接依赖的第五范式、DK范式和第六范式。
现在数据库设计最多满足3NF，普遍认为范式过高，虽然具有对数据关系更好的约束性，但也导致数据关系表增加而令数据库IO更易繁忙，原来交由数据库处理的关系约束现更多在数据库使用程序中完成。

10. 第一范式 First Normal Form, 1NF

第一范式：

Each cell should have a single value and we cannot have repeated columns.
每个单元格都应该是单一值并且不能有重复的列

courses 里的 tags 标签列就不符合第一范式。tags 列用逗号隔开多个标签，不是单一值。若将 tags 分割成多列，每个标签一列呢？问题是我们不知道到底有多少标签，每次出现新标签就要改动表结构，这样的设计很糟糕。这也正是范式1要求没有重复列的原因（没有重复列是这个意思？我还以为重复列是指在多表出现相同列（如姓名列）的情况）

所以我们另外单独创建一个 tags 表，设置两个字段：