0 Github
1 简介
数据库设计(Database Design)是指对于一个给定的应用环境,构造最优的数据库模式,建立数据库及其应用系统,使之能够有效地存储数据,满足各种用户的应用需求(信息要求和处理要求)。在数据库领域内,常常把使用数据库的各类系统统称为数据库应用系统。 数据库设计的设计内容包括:需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库的实施和数据库的运行和维护。
2 数据库建模五部曲
![03e1ecea7346048ce9b2563e6ee158d7.png](https://img-blog.csdnimg.cn/img_convert/03e1ecea7346048ce9b2563e6ee158d7.png)
3 需求总结
◆ 课程的属性:{主标题,副标题,方向,分类,难度最新最热,时长,简介,人数,需知,收获,讲师名讲师职位,课程图片综合评分,内容实用,简洁易懂,逻辑清晰}
◆ 课程列表的属性:{章名,小节名, 说明,小节时长,章节URL,视频格式}
◆ 讲师的属性:{讲师昵称,说明,性别,省,市,职位说明,经验,积分,关注人数,粉丝人数}
◆ 问答评论属性:{类型,标题,内容,关联章节,浏览量,发布时间,用户昵称}
◆ 笔记的属性:{用户昵称,关联章节 笔记标题,笔记内容,发布时间}。
◆ 用户的属性:{用户昵称密码,说明,性别,省,市,职位,说明,经验,积分,关注人数粉丝人数}
◆评价的属性:{用户,课程主标题,内容,综合评分,内容实用,简洁易懂,逻辑清晰,发布时间}
# 4 宽表模式 - 百度百科定义 从字面意义上讲就是字段比较多的数据库表。通常是指业务主题相关的指标、维度、属性关联在一起的一张数据库表。由于把不同的内容都放在同一张表存储,宽表已经不符合三范式的模型设计规范,随之带来的主要坏处就是数据的大量冗余,与之相对应的好处就是查询性能的提高与便捷。这种宽表的设计广泛应用于数据挖掘模型训练前的数据准备,通过把相关字段放在同一张表中,可以大大提高数据挖掘模型训练过程中迭代计算时的效率问题。
◆ 课程的属性:{主标题,副标题,方向,分类难度最新,最热,时长,简介,人数,需知,收获,讲师名,讲师职位,课程图片综合评分,内容实用,简洁易懂,逻辑清晰}
- 实例
![1746caf1b27eb2508c9c975aa2b0977c.png](https://img-blog.csdnimg.cn/img_convert/1746caf1b27eb2508c9c975aa2b0977c.png)
4.1 模式存在的问题
4.1.1 更新异常
修改一行中某列的值时,同时修改了多行数据
例如当使用
![2a7c15ca31260ab62ce6af3ef56b25f9.png](https://img-blog.csdnimg.cn/img_convert/2a7c15ca31260ab62ce6af3ef56b25f9.png)
想修改其职位时,不止影响一条数据 那么,我们再加个限定条件
![46b3f43acbe1cc6de4bcc8135a408b9e.png](https://img-blog.csdnimg.cn/img_convert/46b3f43acbe1cc6de4bcc8135a408b9e.png)
就可以只修改一行数据,因此我们可以将主标题作为该数据表的唯一标识,即主键! 通过主键更新数据,虽然可以避免数据的更新异常,但也可能会造成表中的数据不一致现象,比如该实例中,讲师的职称就会产生多义.
4.1.2 插入异常
部分数据由于缺失主键信息而无法写入表中
例如,我们想新增Java开发方向的课程
![c7fa2d7d132c61618353321f72de7034.png](https://img-blog.csdnimg.cn/img_convert/c7fa2d7d132c61618353321f72de7034.png)
由于执行该语句时,PK为空,即违反了PK非空且唯一的约束条件,因此该语句无法成功.
4.1.3 删除异常
删除某一数据时不得不删除另一数据
例如,我们想删除数据库方向
![e8198efa656bd4714ce150c71194d8de.png](https://img-blog.csdnimg.cn/img_convert/e8198efa656bd4714ce150c71194d8de.png)
我们只是单纯想删除数据库方向而已,但该语句却将许多课程也删除了,这并不符合我们的预期.
4.1.4 数据冗余
相同的数据在一个表中出现了多次
那么是不是这么多问题就意味着宽表一无是处呢?存在即合理!
4.2 模式的适用场景
配合列存储的数据报表应用
由于宽表中,所有数据存在于一个表中,因此在查询时,无需多表查询,SQL执行效率较高,且存在的上述问题在报表应用中都不是大问题
既然宽表不适合我们的当前业务,那么怎么寻找合适的方法呢?
5 数据库设计范式
5.1 第一范式
表中的所有字段都是不可再分的
例如以下实例中的联系方式是一个复合属性,明显就违反了该范式,在数据库中是无法分离出来的
![a7ea056717be476a18f4b240b763084d.png](https://img-blog.csdnimg.cn/img_convert/a7ea056717be476a18f4b240b763084d.png)
我们只需对其进行简单的改动即可
![5c6b9586283d8110d88ebcb330433e30.png](https://img-blog.csdnimg.cn/img_convert/5c6b9586283d8110d88ebcb330433e30.png)
即标准的二维表.
5.2 第二范式
前提
标准的二维表,即第一范式成立
表中必须存在业务主键,并且非主键依赖于全部
业务主键
例如如下博客表实例
![e5c0e82e750582c6e777fd211476c0a7.png](https://img-blog.csdnimg.cn/img_convert/e5c0e82e750582c6e777fd211476c0a7.png)
- 使用用户字段作为PK是否可行呢? 显然一个用户会对应多个博客记录,且章节标题也能为多个用户编辑,所以单列字段PK失效
- 使用<用户,章节,标题>的复合PK 然而用户积分字段也只和用户字段依赖,并不依赖于整体的PK,所以依旧不符合第二范式
- 拆分将依赖的字段单独成表
![38cedba0f3c302c3abc075bb6bade786.png](https://img-blog.csdnimg.cn/img_convert/38cedba0f3c302c3abc075bb6bade786.png)
![3dacde9a7af9a86171f61b48a25705b4.png](https://img-blog.csdnimg.cn/img_convert/3dacde9a7af9a86171f61b48a25705b4.png)
从上面,我们也可以发现: - 若表的PK只有一个字段组成,那么它本就符合第二范式 - 若是多个字段组成,则需考量是否符合第二范式
5.3 第三范式
表中的非主键列之间不能相互依赖
依旧看看课程表
![8c67ab54b925ac2ee02be92134dc087b.png](https://img-blog.csdnimg.cn/img_convert/8c67ab54b925ac2ee02be92134dc087b.png)
首先,一个字段的PK显然符合第二范式,大部分字段也只依赖于PK,然而对于讲师职称字段其实是依赖于讲师名的,所以不符合第三范式.
- 将不与PK形成依赖关系的字段直接提出单独成表即可
![7ebd8964786af39c4c7426c29494a5ef.png](https://img-blog.csdnimg.cn/img_convert/7ebd8964786af39c4c7426c29494a5ef.png)
![561393b7e675bd6cdaa1f3668412379e.png](https://img-blog.csdnimg.cn/img_convert/561393b7e675bd6cdaa1f3668412379e.png)
6 课程实体的逻辑建模
属性
{主标题,副标题,方向,分类,难度,最新,最热,时长,简介,人数,需知,收获,讲师名讲师职位,课程图片综合评分,内容实用,简洁易懂,逻辑清晰}
我们显然可以将其拆分如下:
课程表
主标题(PK)
,副标题,方向,分类,难度,上线时间,学习人数,时长,简介,需知,收获,讲师昵称,课程图片,综合评分,内容实用,简洁易懂,逻辑清晰
讲师表
讲师名及讲师的职称
其中最新
属性即对应着上线时间计算得出,业务上可规定时间段判断是否为最新 最热
属性即可以学习人数字段排序来反映
课程方向表
课程方向名称(PK)
: 在课程表中有对应的方向字段 添加时间
课程分类表
分类名称(PK)
: 在课程表中有对应的方向字段 添加时间
课程难度表
课程难度(PK)
: 在课程表中有对应的方向字段 添加时间
7 课程列表实体的逻辑建模
属性
[章节名,小节名](联合PK)
说明,小节时长,章节URL,视频格式
其中,说明
其实只依赖于章节名
小节时长
,小节URL
,视频格式
都只依赖于小节名
违反第二范式,所以需要拆分字段
课程章节表
章节名(PK)
,说明,章节编号
课程与章节的联系表
主标题,章节名
课程小节表
小节名称(PK),小节视频url,视频格式,小节时长,小节编号
课程章节与小节的联系表
主标题,章节名,小节名
8 讲师实体的逻辑建模
属性
讲师名,密码,性别,省,市,职称,说明,经验,积分,关注数,粉丝数
讲师表
讲师名(PK)
,密码,性别,省,市,职称,说明,经验,积分,关注数,粉丝数
9 用户实体的逻辑建模
属性
用户昵称,密码,性别,省市,职位,说明,经验,积分,关注数,粉丝数
用户表V1.0
用户昵称(PK)
,密码,性别,省市,职位,说明,经验,积分,关注数,粉丝数
和讲师表基本相同,且讲师其实也是一种用户,讲师的信息就会被存储两次,造成数据的冗余.,于是就难以保持数据一致性!考虑合并!
用户表V2.0
用户昵称(PK)
,密码,性别,省市,职位,说明,经验,积分,关注数,粉丝数,讲师标识
10 问答评论实体的逻辑建模
属性
类型,标题,内容关联章节,浏览量,发布时间,用户昵称
其中标题文字是共享的,无法保持一致 同一用户在不同章节提出的问题也可能相同 因此决定采用标题+用户昵称+关联章节
作为PK
评论表
如何记录关联章节字段呢? 是不是只能用课程章节的PK来记录呢? 因此,不得不将课程章节的关联表PK加入
![012793b125a6c85ff3b8ccd74c286a41.png](https://img-blog.csdnimg.cn/img_convert/012793b125a6c85ff3b8ccd74c286a41.png)
[标题,课程主标题,课程章名,小节名称,用户呢称](PK)
父评论(被回复的问题/标题) 标题,内容,类型,浏览量,发布时间
11 笔记实体的逻辑建模
属性
用户昵称,关联章节,笔记标题,笔记内容,发布时间
和评论实体差不多,分析不再赘述
笔记表
[笔记标题,课程主标题,课程章名,小节名称,用户呢称](PK)
内容,发布时间
12 评价实体的逻辑建模
属性
用户呢称;课程主标题,内容,综合评分,内容实用,简洁易懂,逻辑清晰,发布时间
评价表
[用户呢称;课程主标题](PK)
内容,综合评分,内容实用,简洁易懂,逻辑清晰,发布时间
只有选择/购买了课程的用户才能评价!!! 需要用户与所选课程的关联关系表
用户选课表
[用户呢称;课程主标题](PK)
选课时间,累积听课时长
13 小结
![6c6dd5cb943f7429904dccae1a99c12f.png](https://img-blog.csdnimg.cn/img_convert/6c6dd5cb943f7429904dccae1a99c12f.png)
![e5c3140c7ae1ce0a3bdc1aa850970126.png](https://img-blog.csdnimg.cn/img_convert/e5c3140c7ae1ce0a3bdc1aa850970126.png)
![b70e61cb7d371b208a02cead422e8da4.png](https://img-blog.csdnimg.cn/img_convert/b70e61cb7d371b208a02cead422e8da4.png)
![4c230ccdc9ac0c696b9353f4200dc3cf.png](https://img-blog.csdnimg.cn/img_convert/4c230ccdc9ac0c696b9353f4200dc3cf.png)
14 范式化暴露的问题
如果我们想要查询出一门课程包括所有章节和小节的相关信息
![9c77ef78f0506040e4590f21fe71ee3f.png](https://img-blog.csdnimg.cn/img_convert/9c77ef78f0506040e4590f21fe71ee3f.png)
那么这些信息又是如何存储的呢,需要查询哪些表呢?如下所示
![7972d98f973b85c9182552afaf1cb1b4.png](https://img-blog.csdnimg.cn/img_convert/7972d98f973b85c9182552afaf1cb1b4.png)
我们就要关联5个表,查询效率极低!且查询课程信息的需求很大! 为了提高性能,我们还需要对表结构进行优化操作
15 反范式化设计
空间换时间的思想
15.1 课程章节表反范式化设计
![2df940f95a44b0f022219104db5996a2.png](https://img-blog.csdnimg.cn/img_convert/2df940f95a44b0f022219104db5996a2.png)
上述表存在一对多的关系
所以可以并不需要关联关系表,而是呢可以直接把课程表和课程&章节联系表合并
![dd442d7c1e10921294ec639edcf746ff.png](https://img-blog.csdnimg.cn/img_convert/dd442d7c1e10921294ec639edcf746ff.png)
成为新的课程章节表
[主标题,章节名](PK)
,说明,章节编号
虽然违反了第二范式,但是减少了一个表的查询,提高了查询性能,在频繁查询操作的系统中,这很值得!
经过反范式化后,我们只需要查询三个表即可
![ed055a4697ed9c498f39e7780d956655.png](https://img-blog.csdnimg.cn/img_convert/ed055a4697ed9c498f39e7780d956655.png)
15.2 反范式化设计小结
课程相关表数量 5 -> 3
![d7b9f82ba035ff590f4b6f445be4575b.png](https://img-blog.csdnimg.cn/img_convert/d7b9f82ba035ff590f4b6f445be4575b.png)
16 常用存储引擎
![53b04bcd09d4ca77804a1cd79a79525d.png](https://img-blog.csdnimg.cn/img_convert/53b04bcd09d4ca77804a1cd79a79525d.png)
17 InnoDB存储引擎的特点
- 事务型存储引擎支持ACID
- 数据按主键聚集存储
- 支持行级锁及MVCC
- 支持Btree和自适应Hash索引
- 支持全文和空间索引
18 根据 InnoDB特性优化后的表逻辑结构
通过数据冗余避免数据不一致
![44a413f7d28069bc78b315d95e4604cf.png](https://img-blog.csdnimg.cn/img_convert/44a413f7d28069bc78b315d95e4604cf.png)
课程章节表:{章节ID(PK),课程ID,章节名称,章节说明,章节编号}
课程小节表:{小节ID(PK),课程ID,章节ID,小节名称,小节视频url,视频格式,小节时长,小节编号}。
课程方向表:{课程方向ID(PK),课程方向名称,填加时间}
课程分类表:{课程分类ID(PK),分类名称,填加时间}
课程难度表:{课程难度ID(PK) ,课程难度,填加时间}
用户表:{用户ID(PK),用户昵称,密码,性别,省市,职位,说明,经验,积分,关注 人数,粉丝人数,讲师标识}
问答评论表:{评论ID(PK),父评论ID ,课程ID,章节ID,小节ID ,评论标题,用户 ID,内容,类型,浏览量,发布时间}
笔记表:{笔记ID(PK),课程ID,章节ID,小节ID笔记标题,用户呢称,笔记内容, 发布时间}
评价表:{评价ID(PK),用户ID,课程ID,内容综合评分,内容实用,简洁易懂,逻 辑清晰,发布时间}
用户选课表:{用户选课ID(PK),用户ID,课程ID,选课时间,累积听课时长}
19 常用的整数类型
![fa2ec113e2a76af1c78592395aa83dfb.png](https://img-blog.csdnimg.cn/img_convert/fa2ec113e2a76af1c78592395aa83dfb.png)
20 常用的浮点类型
![aaed75647d8a0c95b48d5c3b3c79885b.png](https://img-blog.csdnimg.cn/img_convert/aaed75647d8a0c95b48d5c3b3c79885b.png)
- 例如:
![cb5a536bf29b5475b0737d0eb8ff3e5b.png](https://img-blog.csdnimg.cn/img_convert/cb5a536bf29b5475b0737d0eb8ff3e5b.png)
实战实数类型的特点
- 建立测试数据库
![2f1c1330e021d46b5588b4d617a5523f.png](https://img-blog.csdnimg.cn/img_convert/2f1c1330e021d46b5588b4d617a5523f.png)
- 新建表
![551d48da637be670a4649d0d468cebf5.png](https://img-blog.csdnimg.cn/img_convert/551d48da637be670a4649d0d468cebf5.png)
- 插入数据至t表中
![48298797d49665f492f0c020b9b5273c.png](https://img-blog.csdnimg.cn/img_convert/48298797d49665f492f0c020b9b5273c.png)
![cdae2cb6295f8ab7f738b48d0b271731.png](https://img-blog.csdnimg.cn/img_convert/cdae2cb6295f8ab7f738b48d0b271731.png)
![6f194d1975aa3fb8c43232ccc3637143.png](https://img-blog.csdnimg.cn/img_convert/6f194d1975aa3fb8c43232ccc3637143.png)
- 查询和
![b42257b1a759e6fdb9f26ac157b09f51.png](https://img-blog.csdnimg.cn/img_convert/b42257b1a759e6fdb9f26ac157b09f51.png)
- 和的结果
![05a9de7f61b35011dce1e417216b80f8.png](https://img-blog.csdnimg.cn/img_convert/05a9de7f61b35011dce1e417216b80f8.png)
所以只有decimal是精确的浮点类型
21 常用的时间类型
![3d02b1c5da7d74709ddd6600a0533b88.png](https://img-blog.csdnimg.cn/img_convert/3d02b1c5da7d74709ddd6600a0533b88.png)
![f26360c620fa8a02118e9046d3f49624.png](https://img-blog.csdnimg.cn/img_convert/f26360c620fa8a02118e9046d3f49624.png)
![55f9fa148019907096c99c3a9ffdf51a.png](https://img-blog.csdnimg.cn/img_convert/55f9fa148019907096c99c3a9ffdf51a.png)
实战时间类型的特点
- 新建表
![815465fa0eedbdbe5ff14929d55cfe54.png](https://img-blog.csdnimg.cn/img_convert/815465fa0eedbdbe5ff14929d55cfe54.png)
- 插入数据
![baeb7be67592c34119a77d057d00e7e5.png](https://img-blog.csdnimg.cn/img_convert/baeb7be67592c34119a77d057d00e7e5.png)
- 查询结果
![98d23348320ae7241e6933bc4fca1c46.png](https://img-blog.csdnimg.cn/img_convert/98d23348320ae7241e6933bc4fca1c46.png)
- 由于北京时间是东八区,因此我们更改时区
![f6f6c2310428d79222b8978f8917389f.png](https://img-blog.csdnimg.cn/img_convert/f6f6c2310428d79222b8978f8917389f.png)
- 新的查询结果
![981f5c5976e09638c408da3de1af2877.png](https://img-blog.csdnimg.cn/img_convert/981f5c5976e09638c408da3de1af2877.png)
这就是timestamp具有时区性的特点
22 字符串类型的特点
![617630203584e4708a59e3ab811ee3d6.png](https://img-blog.csdnimg.cn/img_convert/617630203584e4708a59e3ab811ee3d6.png)
23 如何为数据选择合适的的数据类型
23.1 优先选择符合存储数据需求的最小数据类型
INET_ATON( '255.255.255.255' ) = 4294967295
INET_ NTOA(4294967295) ='255.255.255.255'
23.2 谨慎使用ENUM,TEXT字符串类型
23.2.1 ENUM 的迁移
数据迁移的时候,它几乎不可能被其他数据库所支持,如果 ENUM 里面是字符串,对于其他数据库来说就更郁闷了,还不能设为tinyint等类型的字段
23.2.2 ENUM 的索引
纯数字类型的不建议用枚举类型,这是因为在 ENUM 内部维护有一个隐形的索引,也是按数字排列的,容易混淆;添加枚举值也是一个问题,如果添加在最后还好,如果添加在中间什么位置的话,原来的隐藏索引将不再起作用
23.2.3 ENUM 字段 的NULL 值
ENUM 字段默认是可以插入 NULL 值的,这个就比较尴尬了,而且没有办法优化
23.2.4 插入的值
如果插入的值比ENUM设定的值大,会默认保存成接近的那个值;插入的值不能包含函数,不能传递参数
所以如果插入的值是数字型的,建议用tinyint,如果插入的值是字符型的,建议用char。如果真想用 ENUM 也是可以得,前提是要了解到 ENUM 的弊端,就可以有效规避这些问题
23.4 同财务相关的数值型数据,必需使用decimal类型。
24 为项目表们选择合适的数据类型
24.1 课程表
![50c46195b8374ee68e20e91264d21fe6.png](https://img-blog.csdnimg.cn/img_convert/50c46195b8374ee68e20e91264d21fe6.png)
![031c8a92aebbdbd5728b1b96483bb7e7.png](https://img-blog.csdnimg.cn/img_convert/031c8a92aebbdbd5728b1b96483bb7e7.png)
24.2 章节表
![8e26db4f05a0e83113bcd87c6d596c68.png](https://img-blog.csdnimg.cn/img_convert/8e26db4f05a0e83113bcd87c6d596c68.png)
24.3 小节表
![b11037cf0fddbfc9cb627b3a576114fc.png](https://img-blog.csdnimg.cn/img_convert/b11037cf0fddbfc9cb627b3a576114fc.png)
24.4 课程分类表
![72b0ed72ce19eb4916c4427b7c3e5dda.png](https://img-blog.csdnimg.cn/img_convert/72b0ed72ce19eb4916c4427b7c3e5dda.png)
24.5 课程难度表
![087563688ae4b82022315b5d403c67c2.png](https://img-blog.csdnimg.cn/img_convert/087563688ae4b82022315b5d403c67c2.png)
24.5 课程方向表
![1f743404f3572edd335f0c3f760a3cbc.png](https://img-blog.csdnimg.cn/img_convert/1f743404f3572edd335f0c3f760a3cbc.png)
24.6 用户表
![e3c2c38e77d7a2548cedffda47774378.png](https://img-blog.csdnimg.cn/img_convert/e3c2c38e77d7a2548cedffda47774378.png)
![4988d9561f3e5bd3ed74a8bd16fb3cda.png](https://img-blog.csdnimg.cn/img_convert/4988d9561f3e5bd3ed74a8bd16fb3cda.png)
24.7 问答评论表
![343e3d2312226f424502169bef887f87.png](https://img-blog.csdnimg.cn/img_convert/343e3d2312226f424502169bef887f87.png)
24.8 笔记表
![f656b5d2a9f173c04755f13ac1c18bd9.png](https://img-blog.csdnimg.cn/img_convert/f656b5d2a9f173c04755f13ac1c18bd9.png)
24.9 用户选课表
![8df0f5245c4bc4616b712b1a3fda5acc.png](https://img-blog.csdnimg.cn/img_convert/8df0f5245c4bc4616b712b1a3fda5acc.png)
30 如何为表和列选择合适的名字
- 所有数据库对像名称必须使用小写字母可选用下划线分割
- 所有数据库对像名称定义禁止使用MySQL保留关建字
- 数据库对像的命名要能做到见名识义,并且最好不要超过32个字
- 临时库表必须以tmp为前缀并以日期为后缀
- 用于备份的库,表必须以bak为前缀并以日期为后缀
- 所有存储相同数据的列名和列类型必须一致。
31 总结
工程师的必备技能
1、前奏:【业务分析】欲善其事,必三思而行; 2、***:【逻辑设计】范式化VS反范式化; 3、结束:【物理设计】存储引擎&数据类型&命名规约。
内容综述
- 数据库的逻辑设计规范
- MySQL的常用存储引擎及其选择方法
- MySQL的常用数据类型及其选择方法
- 如何为表选择适合的存储类型
- 如何为表起一个好名
参考
数据库设计
MySQL慎用 ENUM 字段