数据库设计范式

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

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

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

范式化设计存在的问题

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

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

反范式化设计

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

课程章节

数据结构设计流程

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

业务分析

课程的属性

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

课程列表的属性

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

讲师的属性

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

问答评论属性

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

笔记的属性

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

用户的属性

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

评价属性

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

逻辑设计

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

| 主标题 | 副标题 | 讲师名 | 职位 | 综合评分 |
| - | - | - | - | - |
| Mysql入门指南 | 基础知识 | Jack | 高级DBA | 10 |
| MySql开发设计 | 核心知识 | Jack | 高级DBA | 10 |

# 宽表模式的缺点

数据冗余：相同的数据在一个表中出现了多次。(讲师的名字会出现多次)
* 占用更多的存储空间
* 数据维护时，需要对数据进行多次维护，如何讲师改名需要对多行数据进行修改。

数据更新异常：修改一行中某列的值时，同时修改了多行数据。
	update 课程表 set  职位 = ‘Mysql 架构师’ where 讲师名='Jack' 
	update 课程表 set  职位 = ‘Mysql 架构师’ where 讲师名='Jack' and 主标题='JavaCore' (虽然没有了数据异常，但是多行记录中，讲师名为 Jack 的行的对应的职位会发现不一致，有的是高级DBA，有的是Mysql架构师)

数据插入异常：部分数据由于确实主键信息而无法写入表中。
	insert into 课程表（方向） values ('后端开发')

数据删除异常：删除某一数据时，不得不删除另一数据。 （把所有方向是数据库的课程也全部删除了）
	delete from 课程表 where 方向= '数据库'

# 宽表模式的应用场景
* 配合列存储的数据报表应用（查询时候不需要关联多个表，SQL执行效率相对高）

逻辑建模：课程对象

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

课程表
	{`主标题`、副标题、方向、分类、难度、上线时间、学习人数、时长、简介、学习人数、需知、收获、讲师昵称、课程图片、综合评分、内容实用、简洁易懂、逻辑清晰 }

讲师表
	{`讲师昵称`、讲师职位}

课程方向表
	{`课程方向名称`，添加事件}

课程分类表
	{`分类名称`，填加时间}

课程难度表
	{`课程难度`，填加时间}

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

课程列表的属性 
	{章节名、小节名、说明、小节时长、章节URL、视频格式}

课程章表
	{`课程章名`，说明，章节编号}

课程表和章节表的关联表（中间表）
	{课程主标题，课程章名}

课程小节表
	｛`小节名称`、小节时长、小节视频url、视频格式、小节编号｝

课程表、章节表、小节表的关联表（中间表）
	｛课程主标题、课程章名、小节名｝

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

# 讲师表
讲师的属性
	｛讲师昵称、密码、性别、省、市、职位、经验、积分、关注人数、粉丝人数｝
讲师表
	｛`讲师昵称`、密码、性别、省、市、职位、经验、积分、关注人数、粉丝人数｝

# 用户表
用户的属性
	{用户昵称、密码、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数}
用户表
	{`用户昵称`、密码、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数}

# 最终用户表 （合并之后的）
最终用户表
	{`用户昵称`、密码、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数、讲师标识}

逻辑建模：问答评论对象

问答评论的属性
	{类型、标题、内容、关联章节、浏览量、发布时间、用户昵称}

问答评论表
	{`标题、课程主标题、课程章名、小节名称、用户昵称`、父评论标题、内容、类型、浏览量、发布时间}

逻辑建模：笔记对象

笔记的属性
	{用户昵称、关联章节、笔记标题、笔记内容、发布时间}

笔记表
	{`笔记标题、课程主标题、课程章名、小节名称、用户昵称`、笔记内容、发布时间}

逻辑建模：评价对象

评价对象的属性
	{用户、课程主标题、内容、综合评分、内容使用、简洁易懂、逻辑清晰、发布时间}

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

用户选课表（用户和所选课程的关系）
	{`用户昵称、课程主标题`、选课时间、累积听课时长}

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

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

 课程表、章节表

 章节表、小节表

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

课程表
	{`主标题`、副标题、方向、分类、难度、上线时间、学习人数、时长、简介、学习人数、需知、收获、讲师昵称、课程图片、综合评分、内容实用、简洁易懂、逻辑清晰 }

课程章表
	{`课程主标题、章节名称`、章节说明、章节编号}

课程小节表
	{`课程主标题`、课程章名、小节名称、小节视频url、视频格式、小节时长、小节编号}

# 其他没有发生变化的表

最终用户表
	{`用户昵称`、密码、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数、讲师标识}


问答评论表
	{`标题、课程主标题、课程章名、小节名称、用户昵称`、父评论标题、内容、类型、浏览量、发布时间}

笔记表
	{`笔记标题、课程主标题、课程章名、小节名称、用户昵称`、笔记内容、发布时间}

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

用户选课表（用户和所选课程的关系）
	{`用户昵称、课程主标题`、选课时间、累积听课时长}

课程方向表
	{`课程方向名称`，添加事件}

课程分类表
	{`分类名称`，填加时间}

课程难度表
	{`课程难度`，填加时间}

讲师表
	{`讲师昵称`、讲师职位}

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

物理设计

存储引擎选择

| 引擎名称 | 事务 | 说明 |
| - | - | - |
| MYISAM | N | MySQL5.6 之前的默认引擎，最常用的非事务型存储引擎 |
| CSV | N | CSV 格式存储的非事务型存储引擎 |
| Archive | N | 只允许查询和新增数据而不允许修改的非事务型存储引擎 |
| Memory | N | 是一种易失性非事务型存储引擎 |
| INNODB | Y | 最常用的事务型存储引擎，MySQL5.6 之后默认采用的存储引擎 |

InnoDB 存储引擎的特点

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

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

课程表
	{`课程ID`、主标题、副标题、方向、分类、难度、上线时间、学习人数、时长、简介、学习人数、需知、收获、讲师昵称、课程图片、综合评分、内容实用、简洁易懂、逻辑清晰 }

课程章表
	{`课程ID`、课程ID、章节名称、章节说明、章节编号}

课程小节表
	{`小节ID`、课程ID、章节ID、小节名称、小节视频url、视频格式、小节时长、小节编号}

课程方向表
	{`课程方向ID`、课程方向名称，添加事件}

课程分类表
	{`课程分类ID`、分类名称，填加时间}

课程难度表
	{`课程难度ID`、课程难度，填加时间}

用户表
	{`课程ID`、用户昵称、密码、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数、讲师标识}

问答评论表
	{`评论ID`、父评论ID、课程ID、章节ID、小节ID、评论标题、内容、类型、浏览量、发布时间}

笔记表
	{`笔记ID`、课程ID、章节ID、小节ID、笔记标题、用户昵称、笔记内容、发布时间}

评价表
	{`评价ID`、用户ID、课程ID、内容、综合评分、内容使用、简洁易懂、逻辑清晰、发布时间}

用户选课表（用户和所选课程的关系）
	{`用户选课ID`、用户ID、课程ID、选课时间、累积听课时长}

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

常用整数类型

常用的浮点类型

常用的时间类型

常用的字符串类型

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

* 优先选择符合存储数据需求的最小数据类型
	3147483647  可以选择使用 Bigint 也可以选用 Unsigned int（显然无符号int 所占用的存储空间更小，存储空间更加节约）
	将字符串转换成整数：INET_ACTION(255.255.255.255) = 4294967295   (字符串需要15个字节保存IP地址，而INT类型则只需要4个字节即可)
	将整数类型转换为字符串：INET_NTOA(4294967295) = '255.255.255.255'

* 谨慎使用ENUM，TEXT字符串类型（内存临时表不支持TEXT类型，磁盘临时表支持TEXT类型）

* 财务相关的数值型数据，必须使用decimal类型（浮点运算时，不会丢失精度）

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

课程表

课程章节

课程小节

课程分类

课程难度

课程方向

用户表

问答评论表

笔记表
| 列名 | 数据类型 |
| --- | --- |
| 笔记(PK) | int unsigned |
| 用户ID | int unsigned |
| 课程ID | int unsigned |
| 章节ID | int unsigned |
| 小节ID | int unsigned |
| 笔记标题 | varchar(50) |
| 评论内容 | text |
| 发布时间 | datetime |

问答评论表

用户选课表
| 列名 | 数据类型 |
| --- | --- |
| 选课(PK) | int unsigned |
| 用户ID | int unsigned |
| 课程ID | int unsigned |
| 选课时间 | datetime |
| 累积听课时间 | time |

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

* 所有数据库对象名称必须使用小写字母可选用下划线分割。

* 所有数据库对象名称定义进制使用MySQL保留关键字。

* 数据库对象的命名要做到见名识意，并且最好不要超过32个字。

* 临时库、表必须以tmp为前缀以日期为后缀。

* 用于备份库、表，表必须以bak为前缀并以日期为后缀。

* 所有存储相同数据的列名和列类型必须一致。（有利于关联查询时候利用关联列上的索引，从而使得查询更加高效）