本文详述了SQL数据库设计的过程，从理解需求、概念建模、逻辑建模到实体建模，涵盖主键、外键、数据库规范化等方面。通过实例介绍了如何处理多对多关系、数据冗余及数据库规范化的重要性，旨在帮助读者掌握如何创建结构良好的数据库。
目录
十三章·上 ：设计数据库 Designing Databases
1. Understand the requirements 理解需求
2. Build a conceptional model 概念建模
4. Build a physical model 实体建模
8. 外键约束 Foreign Key Constraints
9.1. 第一范式 First Normal Form, 1NF
9.3. 第二范式 Second Normal Form, 2NF
9.4. 第三范式 Third Normal Form, 3NF
10.2. 不要对什么都建模 Don't Model the Universe
11. 正向搭建数据库 Forward Engineering a Model
12. 使用数据库同步模型 Synchronizing a Model with a Database
13. 反向搭建数据库 Reverse Engineering a Database
1、航班订票系统 Project Flight Booking System
1.1. 解答：概念模型 Solution Conceptual Model
1.2. 解答：逻辑模型 Solution Logical Model
2、视频租赁应用 Project Video Rental Application
2.1. 解答：概念模型 Solution Conceptual Model
2.2 解答：逻辑模型 Solution Logical Model
十三章·上 ：设计数据库 Designing Databases
1. 介绍 Introduction
之前都是对已有数据库进行查询，这一章学习如何设计和创建数据库（以及表格）。
设计一个结构良好的数据库是需要耗费不少时间和心力的，但这是十分必要的，设计良好的数据库可以快速地查询到想要的数据并且有很好的扩展性（很容易满足新的业务需求），相反，一个设计糟糕的数据库可能需要大量维护且查询又慢又麻烦，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更易繁忙，原来交由数据库处理的关系约束现更多在数据库使用程序中完成。
9.1. 第一范式 First Normal Form, 1NF
第一范式：
Each cell should have a single value and we cannot have repeated columns.
每个单元格都应该是单一值并且不能有重复的列
courses 里的 tags 标签列就不符合第一范式。tags 列用逗号隔开多个标签，不是单一值。若将 tags 分割成多列，每个标签一列呢？问题是我们不知道到底有多少标签，每次出现新标签就要改动表结构，这样的设计很糟糕。这也正是范式1要求没有重复列的原因（没有重复列是这个意思？我还以为重复列是指在多表出现相同列（如姓名列）的情况）
所以我们另外单独创建一个 tags 表，设置两个字段：
- tag_id TINYINT 如果标签是终端用户设定的，那数量就可能会迅速增长，但这里假定标签是管理员设定的，最多可能五六十个，那 TINYINT 足够了
- name VARCHAR(50)
9.2 链接表 link tables
尝试建立 courses 和 tags 之间的联系，发现两者是多对多关系（MySQL里只有一对一和一对多，没有多对多），这说明两者的关系需要进一步细化，我们添加一个 course_tags 表来专门描述两者间的关系，记录每一对课程和标签的组合，这个中间表或者说链表（link table）同时是 courses 和 tags 的子表，与这两个父表均为一对多的关系，建立两条一对多连线后 MySql 自动给 course_tags 表增加了两个外键 course_id 和 tag_id（注意去掉自动添加的表前缀），两者构成了 course_tags 表 的联合主键
courses 和 tags 之间的链表 course_tags
通过 course_tags 细化 courses 和 tags 的关系 与 之前通过 enrollments 表细化 students 和 courses 的关系一样，都是通过建立链表细化多对多关系，这是很常用的一种方法，有时链表只包含引用的两个外键，如 course_tags 表，有时链表还包含其它信息，如 enrollments 表还包含注册时间和注册费用
至此，删除掉 courses 里的 tags 列，我们的数据库就符合第一范式了，所有列都是单一值也没有诸如tag1，tag2这样的重复列，所有标签都保存在独立的 tags 表里拥有唯一记录。如果像之前那样标签以逗号分隔保存在 courses 表中，同一个标签如 "frontend" 会多次出现，如果要将这个标签改名为 "front-end" 就会多出很多不必要的锁定操作，修改标签却要锁定 courses 表里的记录，这本身就很不合理，tags 表才该是唯一储存标签的地方，而tags 里的标签条目才是修改标签时唯一应该被锁定的条目
9.3. 第二范式 Second Normal Form, 2NF
第二范式的人话解释：
Every table should describe one entity, and every column in that table should describe that entity.
每个表都应该是单一功能的/应该表示一个实体类型，这个表的所有字段都是用来描述这个实体的
以 courses 表为例，course_id、title、price 都完全是属于课程的属性，放在 courses 表里没问题，但注册时间 enrollment_date 放在 courses 表里就不合适，因为一个课程可以被多个学生注册所以有多个注册时间，同样的注册时间也不应该是 students 表的属性，因为一个学生可以注册多门课所以可以有多个注册时间，注册时间应该是属于“注册事件”的属性，所以应该另外建个 enrollments 表，放在该表里。
同理，对于订单表 orders 来说，order_id 和 date 应该是其中的属性，但 customer 就不是，虽然每个订单确实有对应的顾客，但顾客信息可能在不同订单里重复，这会占用多余的储存空间并使得修改变得困难，应该单独建一个顾客表来储存顾客信息，订单表里用顾客id而非顾客名来引用顾客表，当然，顾客id还是会重复，但4字节的数字比字符串占用的空间小多了，这已经是让重复最小化了
总之，第一范式是要求单一值和无重复列，这里第二范式是要求表中所有列都只能是完全描述该表所代表的实体的属性，不属于该实体的、在记录中可重复的属性（如订单表里的顾客信息），应该另外放在描述相应实体的表里（顾客表）
以我们这个模型为例，courses 里的 instructors 虽然是单一值符合第一范式却不符合第二范式，因为老师不是完全属于课程的属性，老师在不同课程中可能重复。所以，另外建立 instructrors 表作为父表，包含 instructor_id 和 name 字段，其中 instructor_id 为主键，一对多链接 courses 表后自动引进 courses 表作为外键，删除原先的 instructor 列。还有注意设置外键约束，UPDATE 设置为 CASCADE，DELETE 设置为 NO ACTION，也就是 instructor_id 会随着 instructors 表更改，但不允许在某教师有课程的情况下删除该教师的信息
至此，我们的数据库已符合第二范式。
courses 的查询表/资料表 instructors
补充：第二范式的维基百科
第二范式（2NF）是数据库正规化所使用的正规形式。规则是要求资料表里的所有资料都要和该资料表的键（主键与候选键）有 完全依赖关系：每个非键属性必须独立于任意一个候选键的任意一部分属性。如果有哪些资料只和一个键的 一部分有关的话，就得把它们 独立出来变成另一个 资料表。(查询表)
9.4. 第三范式 Third Normal Form, 3NF
第三范式的人话解释：
A column in a table should not be derived from other columns.
一个表中的字段不应该是由表中其他字段推导而来
例如，假设 invoices 发票表里现在有三个字段：发票额、支付额 和 余额，第三个可以由前两个相减得到所以不符合 3NF，每次前两者更新第三个就要随之更新，假设没有这样做，出现了 100，40，80 这样不一致的数据，就不知道到底该相信哪个了，余额到底是 80 还是 100-40=60？
同理，如果表里已经有 first_name 和 last_name 就不该有 full_name，因为第三者总是可以由前两者合并得到
不管是 余额balance 还是 全名fullname，都是一种冗余，应该删除
补充：第三范式的维基百科
第三范式（3NF）是数据库正规化所使用的正规形式，要求所有非主键属性都只和候选键有相关性，也就是说非主键属性之间应该是独立无关的。
如果再对第三范式做进一步加强就成了BC正规化，强调的重点在于“资料间的关系是奠基在主键上、以整个主键为考量、而且除了主键之外不考虑其他因素”。
总结
第三范式和前两范式一样，都是为了减少数据重复和冗余，增强数据的一致性和完整性（data integrity）
感觉三大范式可以用三个关键词总结：单一值、单一功能、独立
10. 我的实用建议
10.1 My Pragmatic Advice
除非需要考试，不然没必要记忆和死板套用三大范式，实际工作中只需要专注于减少数据的重复性即可，比如发现一个 name 字段下出现的是一些重复的名字而不是重复的外键（如某种id），那就说明设计还不够归一化，具体违反哪条范式并不重要，关键是专注于避免重复性
例子
假设一个顾客表里每条都是一个顾客信息，有名字年龄生日性别还有收货地址，如果想让一个顾客可以有多条收货地址应该怎么办？
如果仍然把收货地址放在这个顾客表，就要为了保存一个顾客的多条地址而将这个顾客的所有信息复制多条，这是一种没必要的重复和冗余
我们先从概念和逻辑模型上思考，这里有两个关键实体，顾客 和 地址，它们是一对多关系，然后再细化为实体模型，应该建立两张表，顾客表保存顾客其他信息，地址表（实际上是顾客地址关系表）只保存顾客id和地址两个字段，这样就将重复性降到了最低
小结
总之，一定要先从概念和逻辑模型去考虑实体和关系，再逐步细化，过程中专注于避免数据的重复冗余以及保证数据的一致性和完整性，一定不要一上来就建表，这样几乎总是得到糟糕由混乱的数据库设计
注意
上面的例子以一个顾客有多个收货地址为前提，但如果一个顾客只有一个收货地址，那用一张表就足够了，用两张表是没必要的，所以关键是理解业务需求，总是按照业务需求来设计，这也引入了下一节内容：不要对全宇宙建模！
10.2. 不要对什么都建模 Don't Model the Universe
设计数据库时总是考虑当前的业务需求，不要试图包罗万象，总有开发人员会考虑各种未来可能出现的需求，实际上大部分那些需求都从未发生，反而使得数据库增加了很多没必要的复杂性，增加了查询的难度并拖慢了执行效率
Mosh之前的公司曾有个人设计了一个过于一般化但也过于复杂难懂的数据库，企图满足所有未来可能的需求，但结果是没人能懂他的模型，而且执行增删改查异常麻烦且速度极低，最后成了一个没人敢碰的烂摊子
建立复杂模型不是本事，能够将复杂的模型不断简化让其尽可能地优美简单易懂又能满足目前的需求，这才是本事，如果还能有不错的拓展性以满足未来可能的新特性就更好了
总之，尽可能保持简洁，简洁才是终极哲学（Simplicity is the ultimate sophistication），无论你对未来的预测有多好，总会有意料之外的需求出现，总有一天你会写脚本改数据库甚至进行数据迁移，这是避免不了的，当前只需考虑如何最好地满足目前的需求就好了，不要企图对全宇宙建模
11. 正向搭建数据库 Forward Engineering a Model
通过模型正向搭建数据库：workbench 菜单的 Database 选项 → Forward Engineer 正向搭建数据库
依据向导保持默认不断点下一步就好了，不要更改，除非你知道你在做什么
有一步可以选择 除了创建数据库中的表 是否还要创建 储存程序、触发器、事务和用户对象，而且表格可以筛选到底要创建哪些表
最后一步会展示对应的SQL代码，里面有创建 school 数据库（schema?）以及各表的SQL代码，之后会详细讲。可以选择保存代码为文件（以保存到仓库中）或者复制到剪贴板然后到 workbench 查询窗口里以脚本方式运行，这里我们直接运行，返回 local instance 链接刷新界面就可以看到新的 school 数据库和里面的6张表了
12. 使用数据库同步模型 Synchronizing a Model with a Database
之后可能会修改数据库结构，比如更改某些表中字段的数据类型或增加字段之类，如果只是自己一个人用的一个本地数据库，可以直接打开对应表的设计模式并点击更改即可，但如果是在团队中工作通常不是这样。
在中大型团队中，我们通常有多个服务器来模拟各种环境，其中有：
1. 生产环境（production environment）：用户真正访问应用和数据库的地方
2. 模拟环境（staging environment）：与生产环境十分接近
3. 测试环境（testing environment）：存粹用来做测试的
4. 开发环境（development environment）
每次需要对数据库做修改时我们需要复制相同的修改到不同的环境以保持数据的一致性
所以不能是在设计模式中直接点击修改，相反，是在之前的实体模型（EER Diagram）中修改并使用菜单中的 Database → Synchronize Model，其中有一步可以选择链接，这里我们选择本地连接 local_instance，但如果是在团队中可能需要选择测试环境、模拟环境甚至开发环境的链接以对相应环境中的数据库执行更改，MySQL会自动检测到需要修改的是 school 数据库并提示要修改的表，例如我们想在 enrollments 中加上一个 coupons 折扣券 字段，会提示将影响的表除了 enrollments 还有 courses 等表，因为这些表与要修改的表是相互关联的，之后的 SQL 的语句会先暂时删除相关外键以消除这些联系，对目标表做出相应更改（增加 coupons 字段）后再重建这些联系，同样的，我们可以把这些代码保存起来并上传到仓库，就可以在不同环境执行相同修改以保持一致性
13. 反向搭建数据库 Reverse Engineering a Database
如果要修改没有实体模型的数据库，第一次可以先逆向工程（Reverse Engineering）建立模型，之后每次就可以在该模型上修改了
例如，我们要修改 sql_store ，应如下操作：
- 关闭当前 school 数据库的 Model，不然之后的逆向工程结果会添加到当前模型上，最好是每个数据库都有一个单独的模型，除非数据库间相互关联否者不要在一个模型中处理多个数据库
- Database → Reverse Engineer，可以选择目标数据库，如上说所，除非数据库相互关联，否者最好一次只逆向工程一个数据库，让每个数据库都有一个单独的模型。
- 同样，可以筛选要哪些表
在反向搭建出的模型中，可以更好的看清和理解数据库的结构设计，可以修改表结构，还可以发现问题，如在 sql_store 数据库的模型中，可以发现有一个 order_items_notes 表并未与任何表相联，这样里面的 order_id 就可能输入无效值，相反如果是建立了链接的表，MySQL会自动验证数据的一致性/完整性/有效性（integrity），只允许子表中添加父表中存在的id值
小结
第一次修改无模型的数据库可以使用MySQL自带的逆向工程，之后就可以用这个模型查看表结构、检查问题和进行修改
第十三章·下： 数据库设计实战
1、 航班订票系统 Project Flight Booking System
通过一张机票上的信息理解航班订票业务需求并建立数据库模型
1.1. 解答：概念模型 Solution Conceptual Model
主要建立实体、实体里的字段、实体间的关系，不用确定具体关系类型和字段类型等细节，主要用于和业务方交流
注意只根据机票信息决定需要的字段，满足当下需求就好，未来有新需求时再修改增加新的字段
1.2. 解答：逻辑模型 Solution Logical Model
与概念模型相比，逻辑模型主要做了如下细化调整：
- 细化关系，尤其是多对多关系，通常要另外添加链表变成两个多对一关系，但是注意 flights 和 airports 的关系很特殊，一个机场可对应多个航班但一个航班只能对应起飞和降落两个机场，是多对二的关系，或者说是两个多对一关系，如果还是通过一个链表来替换这个多对多关系，则不能防止一个航班出现多于两个机场，最好的办法是将 fights 中的机场区分为起飞机场id和降落机场id两个字段，分别建立外键引用airports（两个多对一连线？）
- 调整字段，name 这样的字段要拆分成 first_name 和 last_name 这样的更小组成成分，而重复性的字段常常要另外单独建表（查询表/资料表 lookup table）再以外键形式在原表中引用，但是 airports 里的 city 和 state 比较特殊，因为考虑到 city 和 state 与 airport经常一起查询，合并在一张表上能提高查询速度，而且机场数量并不多，重复性的问题并不严重，反而如果另外单独再建cities表和states表会使得数据过于碎片化，所以这里进行“反归一化”（denormalize），在 airports 表中保留 city 和 state 的原始字段，用一定的重复性来换取查询便利和效率
- 确认数据类型，注意有的所谓的 number 其实不需做计算且包含符号，所以应该用字符串类型
另外注意用词和表达要向业务方咨询，确保用词准确表达方式与业务规范相一致，这很重要
还有注意调整字段时，可以将 flights 里的 duration 和 distance 改为 duration_in_minutes和 distance_in_miles，这样更明确，看的人不用去猜单位是什么
实体模型就不展示了，从逻辑模型到实体模型只是具体DBMS技术上的调整和实现，没必要反复讲
2、视频租赁应用 Project Video Rental Application
为视屏租赁应用Vidly建立数据库
2.1. 解答：概念模型 Solution Conceptual Model
这一步还是一样，为了建立概念模型，根据业务需求文档确定大概的实体、实体属性、实体间关系
注意这里将顾客和电影的多对多关系细化为一个rentals链表（link table），变成可操作的两个一对多关系，这个方法之前也反复用到，如将学生和课程之间的关系细化为 enrollments 表 以及 乘客和航班的关系 细化为 tickets 表
2.2 解答：逻辑模型 Solution Logical Model
如之前一样，在逻辑模型里，我们要确定数据储存方式，所以要进一步细化具体的实体间关系类型和字段的数据类型，也会为了减少数据重复性和提高数据一致性对表结构和数据库结构进行一些调整修改
关系类型具体化和字段数据类型确定：
和之前差不多，只是要注意 coupon 和 rental 的关系比较特殊，是多对零或一（注意箭头的不同），因为一个 rental 可能有一个 coupon 也可能没有 coupon
字段调整：
将名字拆分为姓和名，将租赁天数拆分为借电影日期和还电影日期（后两个才能提供足够信息计算各月收入等）
设计调整：
之前的 users-permissions 用户-权限设计并不好，虽然业务文档确实提到了这两个实体，但仔细分析发现实际上用户只有两类 管理员和店员 而对应的权限唯一的区别也只是是否能修改电影列表，在这一业务情形下，没必要有一个完整的权限表将所有权限列出来，只需要有一个roles岗位表将用户分为两类即可，实际的权限可以通过if条件语句来根据用户是管理员还是店员来决定是否禁止其修改电影列表，这样的设计更精简，减少了每次增加一个用户就要挨个分配10个权限的重复性，也防止了给相同职位不同权限这样的错误的发生，增强了一致性
思想
没必要列出10个权限然后依次分配，以用户表-权限表的方式设计模型过于一般化，提供了业务并不需要的过高的控制等级，这种多余的复杂化和冗余会一直跟随系统一直造成不必要的麻烦。如果你有100元预算只想找个能歇脚能睡觉的地方，那一个500元的豪华宾馆多出的功能如高质量的网络漂亮的海景奢华的床铺等等对你来说都是没必要的，你不会多花400元买你不需要的功能，开发软件也一样， 所有功能和复杂性都是有成本的，都会有人买单，不要把公司的钱浪费在不需要的地方，要尽可能用最精简的方式满足当前的业务需求。
扫一扫
914
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
