6.MySQL数据库设计规范

思考：数据表在设计过程中，都知道是由字段组成，在设计过程中有没有什么规范需要遵循呢？

引入：数据表的设计逻辑是针对数据分类，即一类数据应该存放到一张表中，但是因为关系的存在以及数据的访问便捷性，我们需要对表的设计遵循一定的规范。

1. 范式（Normal Format）：是指按照离散数学的数理逻辑规范数据表的设计，解决的问题是数据的存储于优化，凡是通过关系能够查到的数据，坚决不重复存储，最终==解决数据的冗余问题==。设计范式的层级有6层，每一层都是递进关系，在数据库设计的时候借鉴了前三种

• 第一范式（1NF）
• 第二范式（2NF）
• 第三范式（3NF）

2. 表关系：是指在设计表与表之间关系来规范数据的时候应该遵循的方式，一共分为三种关系

• 一对一关系
• 一对多关系
• 多对多关系

总结

1. 在数据表设计的时候不是随心所欲，为了保证数据的规范和磁盘空间的利用率，需要遵循一定规范
2. 设计范式主要用来保证磁盘空间的利用和数据的颗粒度
3. 表关系是用来提升数据的关联性和操作的效率

一、关系型数据库设计范式

思考：在进行表设计的时候，字段是存放数据的最小单位，那么字段到底如何保证颗粒度呢？

引入：字段设计的很重要的逻辑就是尽可能让数据的颗粒度最小，使得数据在访问时不需要额外的再对数据进行加工，这个就是需要遵循的第一范式

1. 第一范式【掌握】

定义：第一范式（1NF），在设计表存储数据的时候, 如果表中设计的字段存储的数据,在取出来使用之前还需要额外的处理（拆分）就不符合1NF，第一范式就是处理数据颗粒度大的问题

1. 案例：设计一张学生选修课成绩表

学生	性别	课程	教室	成绩	学习时间
张三	男	PHP	101	100	2月1日,2月28日
李四	女	Java	102	90	3月1日,3月31日
张三	男	Java	102	95	3月1日,3月31日

2. 以上表设计是一种非常常见的数据，但是如果想要知道学生上课的开始时间和结束时间，那就意味着这个学习时间取出之后需要再进行拆分，因此就不符合1NF。要保证数据取出来就可以直接使用，就需要将学习时间进行拆分

学生	性别	课程	教室	成绩	开始时间	结束时间
张三	男	PHP	101	100	2月1日	2月28日
李四	女	Java	102	90	3月1日	3月31日
张三	男	Java	102	95	3月1日	3月31日

总结

1. 要满足1NF就是要保证数据在实际使用的时候不用对字段数据进行二次拆分
2. 1NF的核心就是数据要有原子性（不可拆分）

---

思考：以上数据表的设计中满足了原子性（1NF），但是学生在某个课程中应该只有一个考试成绩，也就是说学生对应课程的成绩应该是有唯一性的，那么以上数据表该怎么设计呢？

引入：要解决以上问题，其实很简单，就是学生姓名和课程名字应该是唯一的，那么只要增加一个复合主键即可。

学生（P）	性别	课程（P）	教室	成绩	开始时间	结束时间
张三	男	PHP	101	100	2月1日	2月28日
李四	女	Java	102	90	3月1日	3月31日
张三	男	Java	102	95	3月1日	3月31日

2. 第二范式【掌握】

定义：第二范式（2NF），在数据表设计的过程中，如果有复合主键(多字段主键), 且表中有字段并不是由整个主键来确定, 而是依赖主键中的某个字段（主键的部分）： 存在字段依赖主键的部分的问题, 称之为部分依赖：第二范式就是要解决表设计不允许出现部分依赖

1. 以上表中性别有学生决定，而不受课程影响；同时教室由课程决定，而不受学生影响。此时形成了字段依赖部分主键的情况，因此会存在部分依赖问题，也就不满足第二范式。
2. 解决方案：就是让字段不会存储依赖部分主键的问题。因此需要做的就是增加一个逻辑主键字段：性别一来学生，但学生不是主键；教室依赖的课程也不是主键；

ID（P）	学生	性别	课程	教室	成绩	开始时间	结束时间
1	张三	男	PHP	101	100	2月1日	2月28日
2	李四	女	Java	102	90	3月1日	3月31日
3	张三	男	Java	102	95	3月1日	3月31日

3. 以上虽然解决了依赖问题，但是学生和课程又再次不具备唯一性了，所以应该增加符合唯一键：unique(学生,课程)

ID（P）	学生（U）	性别	课程（U）	教室	成绩	开始时间	结束时间
1	张三	男	PHP	101	100	2月1日	2月28日
2	李四	女	Java	102	90	3月1日	3月31日
3	张三	男	Java	102	95	3月1日	3月31日

总结

1. 第二范式就是解决字段部分依赖主键的问题，也就是主键为复合主键
2. 在实际开发中几乎不用复合主键，因此可以完美避免违背第二范式

---

思考：上述表虽然满足了1NF和2NF，但是总感觉怪怪的，理论上讲性别逻辑主键除外，实际业务主键还是学生和课程，这个表应该是学生与课程对应的成绩，为什么会出现性别和教室呢？

引入：之所以出现了上述矛盾，原因就是我们讲数据都糅杂到一张表中，而且出现了性别依赖学生，而学生依赖ID，形成了字段性别依赖非主键字段学生的问题，也就是触发了3NF问题。

3. 第三范式【掌握】

定义：第三范式（3NF），理论上讲，应该一张表中的所有字段都应该直接依赖主键（逻辑主键： 代表的是业务主键）， 如果表设计中存在一个字段,，并不直接依赖主键,而是通过某个非主键字段依赖，最终实现依赖主键： 把这种不是直接依赖主键,而是依赖非主键字段的依赖关系称之为传递依赖。第三范式就是要解决传递依赖的问题

1. 第三范式的解决方案：如果某个表中有字段依赖非主键字段，而被依赖字段依赖主键，我们就应该讲这种非主键依赖关系进行分离，单独形成一张表。

学生表

Stu_id（P）	姓名	性别
1	张三	男
2	李四	女

课程表

Class_id（P）	课程	教室	开始时间	结束时间
1	PHP	101	2月1日	2月28日
2	Java	102	3月1日	3月31日

2. 此时，虽然性别依然依赖姓名而不是Stu_id，教室依赖课程而不是Class_id，那是因为Stu_id和Class_id代表逻辑主键，而不是实际业务主键，学生表的实际主键应该是姓名，课程表的实际主键应该是课程
3. 新学生选修课成绩表的设计，就应该是取得对应学生表和课程表的ID

ID（P）	学生ID	课程ID	成绩
1	1	1	100
2	2	2	90
3	1	2	95

总结

1. 第三范式是不允许传递依赖：即有字段依赖非主键字段
2. 消除传递依赖的方案就是将相关数据对应创建一张表

---

思考：以上表的设计的很明显的效果就是没有任何数据重复，的确满足了设计范式所要达到的取消数据冗余。这个时候就是想获取学生选修课的成绩，而且这个表需要经常被查询，要怎么办呢？

引入：设计范式的目标是为了尽可能多的让数据不存在冗余，即减轻数据库的压力，不考虑数据查询的效果的。以上设计要进行学生考试成绩的查询，可以使用后续要学习的连表操作。但是如果是经常查询，那么要纠结一下查询效率和数据冗余的代价问题了。

4. 逆规范化【了解】

定义：逆规范化就是在考虑查询效率和数据冗余的时候，为了提升查询效率而选择牺牲磁盘空间，适当的增加数据冗余。

1. 学生选修课成绩经常查询，意味着去其他表的操作非常多，但是每张表只需要取一个字段而已，因此可以让学生和课程直接存放到学生选修课表中

IDP	学生	课程	成绩
1	张三	PHP	100
2	李四	Java	90
3	张三	Java	95

总结

1. 逆规范化是一种不符合设计范式的设计方式
2. 逆规范化存在的价值是用来衡量查询效率与数据冗余之间的代价问题

---

二、表关系【掌握】

思考：一张表中，可能会出现很多的字段，而这些字段在查询的时候，可能只有部分字段会经常用到，这个时候每次查询都需要去组织SQL查询字段，有没有一种比较好的方式呢？

引入：在实际开发中，我们是建议使用字段查询来精确获取需要的数据的，这样能够保证资源的充分利用，也提升工作效率。但是实际上，如果是表中数据存在部分常用，而部分不常用的情况，我们就要考虑使用表关系来进行维护了。

1. 一对一关系【了解】

定义：一对一关系，即在设计表的时候，表中有很多字段，但是存在部分常用，部分不常用的情况，那么可以考虑将常用字段放到一张表中，不常用字段放到另外一张表，而不常用表的主键字段与常用表的主键字段一致。即一张表中的记录与另外一张表中有且只有一条记录相匹配。

1. 定义一张表，包含多字段，有常用字段和不常用字段

id(P)	姓名	性别	年龄	学号	籍贯	政治面貌	家庭住址	血型
1	张三	男	22	0001	北京	群众	朝阳路	O
2	李四	女	21	0002	深圳	团员	深南大道	A

2. 其中发现姓名、性别、学号和年龄是每次必查的，而籍贯、政治面貌、家庭住址和血型基本不查。那他们存放到一张表中就会影响到查询的效率，所以可以考虑将两类数据分离到两张表，而且共用常用字段表的主键即可

学生常用表

id（P）	姓名	性别	年龄	学号
1	张三	男	22	0001
2	李四	女	21	0002

学生附表

id（P）	籍贯	政治面貌	家庭住址	血型
1	北京	群众	朝阳路	O
2	深圳	团员	深南大道	A

3. 这样设计后，常用字段表就可以用来频繁查询，而不会受一些不用字段的影响；而如果要使用不常用信息，同样可以使用常用表的主键ID作为主键

总结

1. 一对一关系的设计原则就是在附表（从表）中主键与主表中主键保持一致
2. 实现方式就是在进行数据写入的时候，同时写入（或者在主表写入后拿到主键ID，从表主动写入对应ID）

---

思考：一个老师只能上一个学科的课，但是一个学科却可以有多个老师上课，这样的表应该如何设计呢？

引入：老师与学科的关系非常明显，属于多对一的关系，反过来也就是一对多的关系。在关系型数据库中，这种一对多/多对一的关系，实际情况非常多。

2. 一对多关系【掌握】

定义：一对多/多对一，就是意味着有一张表中，要保留与另外一张表中数据的关系。关系细节就是一张表中的一条记录对应另外一张表中的多条记录。

1. 设计学科表和老师表

老师表

老师ID（P）	姓名	年龄	性别
1	张三	35	男
2	李四	34	女
3	王五	30	男

学科表

学科ID（P）	名字	课时长度
1	PHP	600
2	Java	800

2. 以上两张表没有任何关系，所以就意味着不能通过关系来实现彼此之间的关联查询。讲师与学科是多对一的关系，多对一关系的设计核心就是在多关系表（讲师）中增加一个字段指向一关系表（学科）的主键。

学科表不变

学科ID（P）	名字	课时长度
1	PHP	600
2	Java	800

讲师表修改

老师ID（P）	姓名	年龄	性别	学科ID
1	张三	35	男	1
2	李四	34	女	1
3	王五	30	男	2

3. 在建立了关系后，就可以通过讲师表中的学科ID来确定几个关系数据

• 可以在讲师表中判定出哪些讲师属于哪个学科
• 可以统计出学科各自有多少讲师

总结：多对一/一对多是关系设计时很常见的一种关系，设计的原则就是在多关系表中增加一个字段指向一关系表的主键（限定关系为外键）

---

思考：大学里一个老师会教很多学生，一个学生会听多个老师的课，那么彼此之间的关系在表中该如何体现呢？

引入：学生与老师之间是一种明显的多对多关系，这个在关系型数据库中也非常常见，设计实现上就会相对前面的关系麻烦一点。

3. 多对多关系【掌握】

定义：多对多关系，指一张表中的一条记录在另外一张表中有多条记录匹配，同时反过来之后也是一样的关系。

1. 定义学生表和老师表

老师表

老师ID（P）	姓名	年龄	性别
1	张三	35	男
2	李四	34	女
3	王五	30	男

学生表

学生ID（P）	姓名	年龄	性别
1	小明	15	男
2	小红	14	女
3	小萌	14	女

2. 以上两张表是独立的表，没有任何关系。如果想建立明确的关系，希望通过表能够确定老师教过哪些学生，学生听过哪些老师的课就比较麻烦了。

• 小明听过张三、李四和王五的课，和老师是一对多关系，那么应该在多表中（老师）增加字段记录学生ID

• 但是张三教过小明、小红和小萌，此时老师与学生是一对多关系，那么应该在多表（学生）增加字段记录老师ID

3. 设计思路：两张表都需要增加字段来记录另外一张表的主键，而且还需要多个字段维护，不合理。此时应该增加一张中间表，来维护彼此之间的关系，同时利用中间表形成与老师表和学生表的多个多对一关系：中间表要实现的就是完成学生与老师的对应关系

中间表

ID（P）	学生ID	老师ID
1	1	1
2	1	2
3	1	3
4	2	1
5	2	2
6	2	3
7	3	1
8	3	3

总结

1. 多对多关系在二维数据表中没有办法直接对应实现（可以实现，但是不满足1NF）
2. 多对多关系的实现方式，就是将两张多表的对应关系通过第三方表维护，从而形成数据表与中间表之间形成多个一对多的关系