05MySQL数据库设计规范

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

学习目标：了解数据库设计的基础规范，掌握范式在数据库设计上的指导意义，运用范式科学设计好的数据库

概念

范式：Normal Format，符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度

• 范式是离散数学里的概念

• 范式目标是在满足组织和存储的前提下使数据结构冗余最小化

• 范式级别越高，表的级别就越标准

• 目前数据库应用到的范式有以下几层

  • 第一范式：1NF
  • 第二范式：2NF
  • 第三范式：3NF
  • 逆规范化

示例

1、一张员工表

工号	姓名	部门	入职时间
0001	杨戬	武装部	0001-01-01
0002	李白	书院部	1500-12-12

2、每个员工都是与部门挂钩的，但是部门不可能很多，所以上述表中会有很多数据重复，此时应该将部门单独维护出来，减少数据冗余

部门编号	部门名称
1	武装部
2	书院部

工号	姓名	部门编号	入职时间
0001	杨戬	1	0001-01-01
0002	李白	2	1500-12-12

N个1和N个武装部占用的磁盘空间肯定是不一样的

小结

1、范式是一种数学理论，在关系型数据库上用来减少数据冗余

2、满足的范式越多，越符合高标准表设计

3、范式一共有6层，但是数据库的设计通常只要求满足3层即可

1、第一范式1NF

目标：了解第一范式的原理，掌握第一范式的实际应用

概念

第一范式：1NF，数据字段设计时必须满足原子性

• 1NF要求字段数据是不需要拆分就可以直接应用
• 如果数据使用的时候需要进行拆分那么就违背1NF

步骤

1、设计的字段是否在使用的时候还需要再拆分？

2、将数据拆分到最小单位（使用），然后设计成字段

3、满足1NF

示例

1、设计一张学生选修课成绩表

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

当前表的学习时间在使用的时候肯定是基于开始时间和结束时间的，而这种设计就会存在使用时的数据拆分，不满足原子性也就是1NF

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是满足数据表设计的最基础规范

2、第二范式2NF

目标：了解第二范式的原理，掌握第二范式的实际应用

概念：

第二范式：2NF，字段设计不能存在部分依赖

• 部分依赖：首先表存在复合主键，其次有的字段不是依赖整个主键，而只是依赖主键中的一部分
• 部分依赖解决：让所有非主属性都依赖一个候选关键字
  • 最简单方式：取消复合主键（一般选用逻辑主键替代，但是本质依然是复合主键做主），所有非主属性都依赖主属性（逻辑主键）
  • 正确方式：将部分依赖关系独立成表

步骤

1、表中是否存在复合主键？

2、其他字段是否存在依赖主键中的一部分？

3、如果存在部分依赖，将部分依赖的关系独立拆分成表

4、满足2NF

示例

1、学生成绩表中学生和课程应该是决定性关系，因此属于主属性（主键）

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

• 成绩是由学生和课程决定的，是完全依赖主属性
• 性别只依赖学生（部分依赖）
• 教室、开始时间和结束时间依赖课程（部分依赖）

2、解决方案：将学生信息维护到一张表，课程信息维护到一张表，成绩表取两个表的主属性即可

学生表

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

• Stu_id是姓名的代指属性（逻辑主键，本质主键是姓名）
• 性别只依赖主属性

课程表

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

• Class_id是课程的代指属性（逻辑主键）
• 教室、开始时间和结束时间都依赖课程（主属性）

成绩表

Stu_id（P）	Class_id（P）	成绩
1	1	100
2	2	90
1	2	95

• Stu_id和Class_id共同组成主属性（复合主键）
• 成绩依赖Stu_id和Class_id本身，不存在部分依赖

小结

1、2NF是在满足1NF的前提之上的

2、2NF的目标是取消表中存在的部分依赖

• 主属性（主键）为复合主键才有可能存在
• 解决方案就是让部分依赖存在的关系独立成表（学生表和课程表），不存在部分依赖关系的独立成表（学生成绩表）

3、2NF可以实现很大程度的数据冗余减少

3、第三范式3NF

目标：了解第三范式的原理，掌握第三范式的实际应用

概念

第三范式：3NF，字段设计不能存在传递依赖

• 传递依赖：字段某个非主属性不直接依赖主属性，而是通过依赖某个其他非主属性而传递到主属性之上
• 传递依赖解决：让依赖非主属性的字段与依赖字段独立成表

步骤

1、确定表中的所有字段都是依赖主属性的

2、如果存在不直接依赖主属性，而是通过依赖其他属性产生依赖的，形成独立的表

3、满足3NF

示例

1、学生表：包括所在系信息

学号（P）	姓名	专业编号	专业名字
1	张三	0001001	软件工程
2	李四	0001002	土木工程

• 姓名和专业编号都依赖于学号（为学号提供信息支持）
• 专业名字依赖专业编号（为编号提供信息支持）
• 专业名字间接依赖学号：传递依赖
• 随着学生增加，专业名字会出现大量数据冗余

2、解决方案：将存储传递依赖部分的字段（非主属性）独立成表，然后在需要使用相关信息的时候，引入即可

专业表

专业编号（P）	专业名字
0001001	软件工程
0001002	土木工程

• 即使有更多的信息为专业提供支持也不存在传递关系

学生表

学号（P）	姓名	专业编号
1	张三	0001001
2	李四	0001002

• 姓名和专业编号都依赖学号（为学号提供信息支持）
• 没有其他字段是通过非主属性（专业编号）来依赖主属性的：没有传递依赖
• 学生再多，专业名字信息只需要维护一次，减少数据冗余

小结

1、3NF目的是为了解决非主属性对主属性的传递依赖

2、让有关联关系的表独立成表就可以消除传递依赖，满足3NF

4、逆规范化

目标：了解逆规范化的概念，掌握逆规范化的应用

概念

逆规范化：为了提升数据查询的效率而刻意违背范式的规则

• 逆规范化的目标是为了提升数据访问效率
• 所谓逆规范化就是减少表之间的关联查询（效率降低），刻意增加数据冗余

步骤

1、表中部分数据来源于其他表（通常只需要其他表的某个简单数据）

2、当前表会被高频次查询

3、数据表数据量很大

4、考虑使用逆规范化

示例

1、学生成绩表需要经常查询，而且数据量很大，但是：

• 成绩表中只有学号，显示的时候需要学生姓名（去学生表中连表查询）
• 成表表中只有课程号，显示的时候需要显示课程名（去课程表中连表查询）
• 逆规范化：将学生姓名和课程名在表中冗余维护（不满足2NF）

学号（P）	学生姓名	课程号（P）	课程名字	成绩
1	张三	1	PHP	100
1	张三	2	Java	90

• 学生姓名部分依赖学号（主属性）：不满足2NF
• 学生姓名和课程名字会有大量数据冗余存在（不满足2NF导致）

小结

1、逆规范化只有在数据量大，查询效率低下的时候为了提升查询效率而牺牲磁盘空间的一种做法

2、逆规范化后数据表的设计必然是不完全符合范式要求的（2NF/3NF）

5、总结

1、范式是关系型数据库设计借鉴用来减少数据冗余的

• 1NF：数据字段的原子性，增强数据的可用性
• 2NF：取消字段的部分依赖，建立数据的关联性，减少数据冗余
• 3NF：取消字段的传递依赖，将相关实体独立划分，减少数据冗余
• 逆规范化：为了提升数据访问效率，刻意增加数据冗余（磁盘空间利用率与访问效率的矛盾）

2、在进行数据表设计的时候，需要严格遵循范式规范

• 基于规范设计数据表
• 在设计表中深入认知范式规范
• 熟练的基于业务设计数据表

二、表关系

学习目标：了解MySQL中表设计关系，理解关系设计给数据库带来的方便，掌握表关系的应用实现复杂数据库设计

• 一对一关系
• 一对多关系（多对一）
• 多对多关系

概念

表关系：一个表代表一个实体，实体之间都有关联关系的

• 根据范式的要求来设计表关系，减少数据冗余
• 根据实际需求来设计表关系，提升访问效率

示例

设计一个简单新闻管理系统的数据库

• 新闻信息表：id、标题、内容、发布时间、作者id（作者表主属性）、分类id（分类表主属性）、阅读量、推荐数

• 作者表：id、作者名字、作者来源id（来源表）

• 来源表：id、来源名字、来源描述

• 分类表：id、分类名字、分类级别（父分类id）

• 评论表：id、评论人id（评论表）、评论时间、评论内容（不回复）

小结

1、表关系是体现数据实际联系的方式

2、表关系的设计好坏直接关联数据维护的准确性、有效性

3、良好的数据库设计有助于后期程序开发

1、一对一关系

目标：了解一对一关系的处理方式，掌握一对一关系的实体设计

概念

一对一关系：一张表中的一条记录与另外一张表中有且仅有一条记录有关系

• 一对一关系通常是用来将一张原本就是一体的表拆分成两张表
  • 频繁使用部分：常用字段
  • 不常使用部分：生僻字段
  • 使用相同的主键对应
• 一对一关系设计较多使用在优化方面

步骤

1、一张表的数据字段较多且数据量较大

2、表中有部分字段使用频次较高，而另一部分较少使用

3、将常用字段和不常用字段拆分成两张表，使用同样的主键对应

示例

1、学生信息表

学号（P）	姓名	性别	年龄	身高	体重	籍贯	政治面貌
1	张飞	男	20	178	160	蜀	农民
2	武则天	女	21	168	110	唐	党员

• 以上数据表信息字段较多
• 姓名、性别、年龄属于常用字段，频繁查询

2、一对一关系设计

• 将常用字段取出，与学号组合成一张常用表
• 将不常用字段取出，与学号组合成一张不常用表
• 表与表数据对应关系：基于学号（唯一）是一对一关系

常用表

学号（P）	姓名	性别	年龄
1	张飞	男	20
2	武则天	女	21

不常用表

学号（P）	身高	体重	籍贯	政治面貌
1	178	160	蜀	农民
2	168	110	唐	党员

小结

1、一对一关系的核心是两张表中记录匹配有且仅有一条匹配

2、一对一关系常用来进行分表，实现优化操作

3、因为一对一关系表通常有相同信息作为匹配条件，所以查询方式也比较方便

• 连表操作：利用共有信息进行匹配，一并查出一条完整信息
• 多次查询：利用共有信息进行多表查询，利用程序组合成一条完整信息

2、一对多关系

目标：了解一对多关系的原理，掌握一对多关系的实体设计

概念

一对多关系：也叫多对一关系，一张表中的一条记录与另外一张表的多条记录对应，反过来另外一张表的多条记录只能对应当前表的一条记录

• 一对多关系是实体中非常常见的一种关系，实体设计时也应用非常多
• 一对多关系的核心解决方案是如何让记录能够正确匹配到另外表中的数据
  • 一表设计：一表记录在另外一张表中有多条记录，所以无法记录多个字段（违背1NF）
  • 多表设计：多表记录在另外一张表中只有一条记录，可以设置字段记录对应的主属性（通常主键）

步骤

1、确定实体间的关系为一对多（多对一）关系

2、在多表中增加一个字段记录一表中对应的主属性

示例

1、老师与学科间的关系：一个老师只能教一个学科，但是一个学科有多个老师教授，学科与老师形成的关系就是一对多（反过来老师与学科的关系就是多对一关系）

老师表（多表）

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

学科表（一表）

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

• 以上两个实体没有体现彼此之间的关联关系
• 实际上讲师与学科肯定是有关联的

2、在多表（讲师）中增加字段维护一表（学科）的关系型，形成多对一关系

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

• 基于新的讲师表与学科表产生了关联关系（多对一）
• 基于讲师表可以知道讲师所属学科
• 基于学科ID可以统计出不同学科的讲师数量

小结

1、一对多关系设计是将实体的关系在表结构层进行强制关联（没有关系程序层也可以控制，但是会非常麻烦）

• 便于连表操作
• 便于数据分析统计（数据库层）

2、一对多关系的核心在于分析出表与表之间的关系

3、多对多关系

目标：了解多对多关系的处理方式，掌握多对多关系的实体设计

概念

多对多关系：一张表中的一条记录对应另外一个表中多条记录，反过来一样

• 多对多关系在实体中是最常见的关系
• 多对多关系是无法在自身表中维护对应表关系的（违背1NF），需要通过第三方表来实现将多对多关系变成多个多对一关系
  • 设计一个中间表：记录两张表之间的对应关系（主属性）
  • 中间表与其他表都是多对一的关系

步骤

1、确定实体间的关系为多对多关系

2、设计中间表，记录两张表的对应关系

示例

1、老师与学生之间的关系：一个老师会教授多个学生，一个学生也会听多个老师的课，所以实体关系是多对多关系

老师表

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

学生表

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

• 以上实体没有从结构上体现表之间的关系

2、设计一个中间表：老师与学生关系表，将老师与学生的对应关系对应上（多对一）

中间表

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

• 中间表与老师表的对应关系是多对一：通过老师ID可以找到每一个上过课的老师
• 中间表与学生表的对应关系是多对一：通过学生ID可以找到每一个听过课的学生
• 老师找学生：老师表–》中间表（找出老师对应的学生ID）–》学生表（找出学生ID对应的学生信息）
• 学生找老师：学生表–》中间表（找出学生对应的老师ID）–》老师表（找出老师ID对应的老师信息）

小结

1、多对多关系在表上不能直接维护（字段设计违背1NF）

2、多对多关系是将关系抽离形成中间关系表，形成多个多对一的关系

3、多对多关系是否建立主要看业务上是否存在数据要求，如果不存在数据需求，那么就没必要刻意设计

4、总结

1、表关系的设计是要遵循范式规范作为前提

2、表关系的设计是根据实体关系以及业务需求进行设计

• 一对一关系：主要在于优化访问效率、传输效率
• 一对多关系：在于如何让实体间的联系在结构中体现（后期可以使用外键进行相关约束保证数据的有效性）
• 多对多关系：与一对多关系一样，清晰明了的体现实体间的结构联系

3、在设计数据库的时候，要严格使用表关系来进行实体关联设计

• 基于表关系来实现实体间的关联控制
• 在设计和应用表的时候提炼对表关系的认知
• 能够熟练的基于业务控制数据库的关系