数据库设计范式

第一范式
 表中的所有字段都是不可再分的。（将复合属性进行拆分）

第二范式
 表中必须存在业务主键，并且非主键依赖于全部业务主键。业务主键：那些可以唯一标识出每一行业务数据的列，或是列的组合。
 如果表的业务主键只有一个列组成，那么该表原生就符合第二范式。
 如果表的业务主键有多个列组成，那么需要把那些非主键依赖于全部业务主键的列从表中拆分出来。

第三范式
 表中的非主键列之间不能相互依赖。

范式化设计存在的问题

如何获取出一门课程包括所有章节和小节的信息？

1# 表关联多，查询性能越差
21. 需要查询的信息分别位于三个不同的表。
32. 并且需要经过2个关联表。
43. 如果需要查询目标数据，就需要用到5个表。

反范式化设计

 主要思路是拿空间换时间，或多或少会违反范式化的设计原则，存在一定程度上的数据冗余。

课程章节

数据结构设计流程

11. 业务分析（抽象原始数据）
22. 逻辑设计
3	范式化设计
4	反范式化设计
53. 物理设计
6	存储引擎
7	数据类型
8	对象命名
94. 建立库表

业务分析

课程的属性

1主标题、副标题、方向、分类、难度、最新、最热、时长、简介、人数、需知、收获、讲师名、讲师职位、课程图片、综合评分、内容实用、简洁易懂、逻辑清晰 

课程列表的属性

1章名、小节名、说明、小节时长、章节URL、视频格式

讲师的属性

1讲师昵称、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数

问答评论属性

1类型、标题、内容、关联章节、浏览量、发布时间、用户昵称

笔记的属性

1用户昵称、关联章节、笔记标题、笔记内容、发布时间

用户的属性

1用户昵称、密码、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数

评价属性

1用户、课程主标题、内容、综合评分、内容使用、简洁易懂、逻辑清晰、发布时间

逻辑设计

宽表模式
 把一个对象的所有属性，全部存储在一个表中。如果对象的属性很多，表的列就会比较多。

 1# 宽表模式的缺点
 2
 3数据冗余：相同的数据在一个表中出现了多次。(讲师的名字会出现多次)
 4* 占用更多的存储空间
 5* 数据维护时，需要对数据进行多次维护，如何讲师改名需要对多行数据进行修改。
 6
 7数据更新异常：修改一行中某列的值时，同时修改了多行数据。
 8	update 课程表 set  职位 = ‘Mysql 架构师’ where 讲师名='Jack' 
 9	update 课程表 set  职位 = ‘Mysql 架构师’ where 讲师名='Jack' and 主标题='JavaCore' (虽然没有了数据异常，但是多行记录中，讲师名为 Jack 的行的对应的职位会发现不一致，有的是高级DBA，有的是Mysql架构师)
10
11数据插入异常：部分数据由于确实主键信息而无法写入表中。
12	insert into 课程表（方向） values ('后端开发')
13
14数据删除异常：删除某一数据时，不得不删除另一数据。 （把所有方向是数据库的课程也全部删除了）
15	delete from 课程表 where 方向= '数据库'
16
17# 宽表模式的应用场景
18* 配合列存储的数据报表应用（查询时候不需要关联多个表，SQL执行效率相对高）

逻辑建模：课程对象

 1课程的属性
 2	{主标题、副标题、方向、分类、难度、最新、最热、时长、简介、人数、需知、收获、讲师昵称、讲师职位、课程图片、综合评分、内容实用、简洁易懂、逻辑清晰 }
 3
 4课程表
 5	{`主标题`、副标题、方向、分类、难度、上线时间、学习人数、时长、简介、学习人数、需知、收获、讲师昵称、课程图片、综合评分、内容实用、简洁易懂、逻辑清晰 }
 6
 7讲师表
 8	{`讲师昵称`、讲师职位}
 9
10课程方向表
11	{`课程方向名称`，添加事件}
12
13课程分类表
14	{`分类名称`，填加时间}
15
16课程难度表
17	{`课程难度`，填加时间}

逻辑建模：课程列表对象
 联合主键=> 不满足第二范式，需要拆分，说明只依赖于章名，其他的依赖于小节名

 1课程列表的属性 
 2	{章节名、小节名、说明、小节时长、章节URL、视频格式}
 3
 4课程章表
 5	{`课程章名`，说明，章节编号}
 6
 7课程表和章节表的关联表（中间表）
 8	{课程主标题，课程章名}
 9
10课程小节表
11	｛`小节名称`、小节时长、小节视频url、视频格式、小节编号｝
12
13课程表、章节表、小节表的关联表（中间表）
14	｛课程主标题、课程章名、小节名｝

逻辑建模：用户对象（讲师对象 + 用户对象）
 讲师本身也可以是用户，用户也可以是讲师，避免数据冗余（通过增加一列 讲师标识）。

 1# 讲师表
 2讲师的属性
 3	｛讲师昵称、密码、性别、省、市、职位、经验、积分、关注人数、粉丝人数｝
 4讲师表
 5	｛`讲师昵称`、密码、性别、省、市、职位、经验、积分、关注人数、粉丝人数｝
 6
 7# 用户表
 8用户的属性
 9	{用户昵称、密码、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数}
10用户表
11	{`用户昵称`、密码、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数}
12
13# 最终用户表 （合并之后的）
14最终用户表
15	{`用户昵称`、密码、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数、讲师标识}

逻辑建模：问答评论对象

1问答评论的属性
2	{类型、标题、内容、关联章节、浏览量、发布时间、用户昵称}
3
4问答评论表
5	{`标题、课程主标题、课程章名、小节名称、用户昵称`、父评论标题、内容、类型、浏览量、发布时间}

逻辑建模：笔记对象

1笔记的属性
2	{用户昵称、关联章节、笔记标题、笔记内容、发布时间}
3
4笔记表
5	{`笔记标题、课程主标题、课程章名、小节名称、用户昵称`、笔记内容、发布时间}

逻辑建模：评价对象

1评价对象的属性
2	{用户、课程主标题、内容、综合评分、内容使用、简洁易懂、逻辑清晰、发布时间}
3
4评价表
5	{`用户、课程主标题`、内容、综合评分、内容使用、简洁易懂、逻辑清晰、发布时间}
6
7用户选课表（用户和所选课程的关系）
8	{`用户昵称、课程主标题`、选课时间、累积听课时长}

反范式化设计
 如何获取出一门课程包括所有章节和小节的信息？

1# 表关联多，查询性能越差
21. 需要查询的信息分别位于三个不同的表。
32. 并且需要经过2个关联表。
43. 如果需要查询目标数据，就需要用到5个表。

 课程表、章节表

 章节表、小节表

经过反范式化设计之后
 主要针对经常需要访问的 课程表、章节表、小节表 进行了优化，提高查询性能。

 1课程表
 2	{`主标题`、副标题、方向、分类、难度、上线时间、学习人数、时长、简介、学习人数、需知、收获、讲师昵称、课程图片、综合评分、内容实用、简洁易懂、逻辑清晰 }
 3
 4课程章表
 5	{`课程主标题、章节名称`、章节说明、章节编号}
 6
 7课程小节表
 8	{`课程主标题`、课程章名、小节名称、小节视频url、视频格式、小节时长、小节编号}
 9
10# 其他没有发生变化的表
11
12最终用户表
13	{`用户昵称`、密码、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数、讲师标识}
14
15
16问答评论表
17	{`标题、课程主标题、课程章名、小节名称、用户昵称`、父评论标题、内容、类型、浏览量、发布时间}
18
19笔记表
20	{`笔记标题、课程主标题、课程章名、小节名称、用户昵称`、笔记内容、发布时间}
21
22评价表
23	{`用户、课程主标题`、内容、综合评分、内容使用、简洁易懂、逻辑清晰、发布时间}
24
25用户选课表（用户和所选课程的关系）
26	{`用户昵称、课程主标题`、选课时间、累积听课时长}
27
28课程方向表
29	{`课程方向名称`，添加事件}
30
31课程分类表
32	{`分类名称`，填加时间}
33
34课程难度表
35	{`课程难度`，填加时间}
36
37讲师表
38	{`讲师昵称`、讲师职位}

 获取出一门课程包括所有章节和小节的信息只需要查询：课程表、课程章节表、课程小节表。

物理设计

存储引擎选择

InnoDB 存储引擎的特点

1* 事务型存储引擎支持 ACID
2* 数据按主键聚集存储（通常采用自增ID作为主键：之前的业务主键可以建立唯一索引来保证一致性）。
3* 支持行级锁及MVCC（多版本并发控制，避免读写操作的互相阻塞）
4* 支持Btree和自适应Hash索引
5* 支持全文索引(mysql5.6之后)和空间索引(mysql5.7之后)

根据 Innodb 特性优化后的表逻辑结构
 自增 ID 可以保证数据的逻辑存储顺序（后加入的数据一定排在队尾），如果使用之前的业务主键(主标题)，则无法保证数据的逻辑存储顺序。
 为了保证 主标题 的唯一性，可以基于 主标题 建立唯一索引。

 1课程表
 2	{`课程ID`、主标题、副标题、方向、分类、难度、上线时间、学习人数、时长、简介、学习人数、需知、收获、讲师昵称、课程图片、综合评分、内容实用、简洁易懂、逻辑清晰 }
 3
 4课程章表
 5	{`课程ID`、课程ID、章节名称、章节说明、章节编号}
 6
 7课程小节表
 8	{`小节ID`、课程ID、章节ID、小节名称、小节视频url、视频格式、小节时长、小节编号}
 9
10课程方向表
11	{`课程方向ID`、课程方向名称，添加事件}
12
13课程分类表
14	{`课程分类ID`、分类名称，填加时间}
15
16课程难度表
17	{`课程难度ID`、课程难度，填加时间}
18
19用户表
20	{`课程ID`、用户昵称、密码、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数、讲师标识}
21
22问答评论表
23	{`评论ID`、父评论ID、课程ID、章节ID、小节ID、评论标题、内容、类型、浏览量、发布时间}
24
25笔记表
26	{`笔记ID`、课程ID、章节ID、小节ID、笔记标题、用户昵称、笔记内容、发布时间}
27
28评价表
29	{`评价ID`、用户ID、课程ID、内容、综合评分、内容使用、简洁易懂、逻辑清晰、发布时间}
30
31用户选课表（用户和所选课程的关系）
32	{`用户选课ID`、用户ID、课程ID、选课时间、累积听课时长}

为表中的列选择合适的数据类型

常用整数类型

常用的浮点类型

常用的时间类型

常用的字符串类型

如何为数据选择合适的数据类型

1* 优先选择符合存储数据需求的最小数据类型
2	3147483647  可以选择使用 Bigint 也可以选用 Unsigned int（显然无符号int 所占用的存储空间更小，存储空间更加节约）
3	将字符串转换成整数：INET_ACTION(255.255.255.255) = 4294967295   (字符串需要15个字节保存IP地址，而INT类型则只需要4个字节即可)
4	将整数类型转换为字符串：INET_NTOA(4294967295) = '255.255.255.255'
5
6* 谨慎使用ENUM，TEXT字符串类型（内存临时表不支持TEXT类型，磁盘临时表支持TEXT类型）
7
8* 财务相关的数值型数据，必须使用decimal类型（浮点运算时，不会丢失精度）

为数据选择合适的数据类型

课程表

课程章节

课程小节

课程分类

课程难度

课程方向

用户表

问答评论表

笔记表

问答评论表

用户选课表

如何为表和列选择合适的名字

 1* 所有数据库对象名称必须使用小写字母可选用下划线分割。
 2
 3* 所有数据库对象名称定义进制使用MySQL保留关键字。
 4
 5* 数据库对象的命名要做到见名识意，并且最好不要超过32个字。
 6
 7* 临时库、表必须以tmp为前缀以日期为后缀。
 8
 9* 用于备份库、表，表必须以bak为前缀并以日期为后缀。
10
11* 所有存储相同数据的列名和列类型必须一致。（有利于关联查询时候利用关联列上的索引，从而使得查询更加高效）