数据设计数据表是,通用的规范规则,帮助设计更优秀的数据表结构.
范式的定义是层级定义, 1NF, 2NF, 3NF…… 6NF第一,第二…第六范式.
当前级别的范式,都是在满足前一级别的基础上,在增加规范内容.
通常满足3NF范式即可!
一:字段原子性;二:主键独立性;三:减少传递依赖
结论:
1 每类实体独立一张表
2 每张表存在独立的自动增长的非业务逻辑ID作为主键
3 使用关联字段或者关联表,去表示实体间的联系
3.1 1NF,第一范式,满足原子性
字段,属性应该独立,不可再分的.就称之为字段满足原子性.
看下面的设计:
表示,老师在某个班级的那个阶段,进行授课.
老师 | 性别 | 班级 | 教室 | 授课时间 |
孔子 | 男 | PHP4 | 203 | 2016-01-01 到 2016-3-15 |
老子 | 男 | PHP4 | 203 | 2016-3-16到 2016-6-11 |
孔子 | 男 | Java3 | 201 | 2016-03-17 |
观察授课时间字段?
在数据上由两个部分组成: 1开始2结束时间
如果在应用程序中,需要仅仅需要开始的时间.授课时间的字段,就不满足逻辑上的原子性.
老师 | 性别 | 班级 | 教室 | 开始时间 | 结束时间 |
孔子 | 男 | PHP4 | 203 | 2016-01-01 | 2016-3-15 |
老子 | 男 | PHP4 | 203 | 2016-3-16 | 2016-6-11 |
孔子 | 男 | Java3 | 201 | 2016-03-17 | 2016-5-12 |
再思考:如果应用程序,就是需要授课时间段,而没有需要独立的开始时间和结束时间呢?
可见,授课时间是不需要再拆分的.
拆分后,满足逻辑上的原子性.而不拆分,也满足新的逻辑上的原子性.
可见,逻辑上的原子性,是依赖于当前的应用程序需求的.
物理结构上,关系型数据库系统的表,都满足物理的原子性.
二维表,都满足第一范式!1NF.
3.2 2NF,在满足第一范式的前提下,消除对主键的部分依赖
增加独立的主键字段!
主键:表中,记录的唯一标志.称之为主键. primary key.
主键可以由1个或多个字段构成:
如下表:在不增加字段的情况下,建立主键:
老师 | 性别 | 班级 | 教室 | 开始时间 | 结束时间 |
孔子 | 男 | PHP4 | 203 | 2016-01-01 | 2016-3-15 |
老子 | 男 | PHP4 | 203 | 2016-3-16 | 2016-6-11 |
孔子 | 男 | Java3 | 201 | 2016-03-17 | 2016-5-12 |
选择老师+班级两个字段组成联合主键.
部分依赖:在联合主键的情况下,表中的非主键字段,依赖于(A字段确定,就可以确定B字段,称之为B字段依赖于A字段)联合主键中的部分字段.
例如,性别字段,就由老师字段决定,性别字段部分依赖于主键中的老师字段.就产生了部分依赖!
消除联合主键,也就消除了部分依赖的可能性.
增加一个于业务逻辑无关的自动增长的ID字段,作为记录的主键.
ID | 老师 | 性别 | 班级 | 教室 | 开始时间 | 结束时间 |
23 | 孔子 | 男 | PHP4 | 203 | 2016-01-01 | 2016-3-15 |
25 | 老子 | 男 | PHP4 | 203 | 2016-3-16 | 2016-6-11 |
57 | 孔子 | 男 | Java3 | 201 | 2016-03-17 | 2016-5-12 |
此时,主键由ID构成.没有联合主键,就没有部分依赖!
就满足了第二范式.
3.3 3NF,第三范式,满足第二,消除对主键传递依赖
传递依赖:
字段,依赖于非主键之外的其他字段.就成为形成了对主键的传递依赖.
例如:
ID | 老师 | 性别 | 班级 | 教室 | 开始时间 | 结束时间 |
23 | 孔子 | 男 | PHP4 | 203 | 2016-01-01 | 2016-3-15 |
25 | 老子 | 男 | PHP4 | 203 | 2016-3-16 | 2016-6-11 |
57 | 孔子 | 男 | Java3 | 201 | 2016-03-17 | 2016-5-12 |
性别依赖非ID的,老师字段.性别依赖于非主键字段,产生了传递依赖.
解决思路:将同一种实体,使用一张表进行存储,解决之后,老师,班级,授课信息分别在表中存储,如果需要,使用主键字段,进行关联即可:
授课信息表:
ID | 老师ID | 班级 ID | 开始时间 | 结束时间 |
23 | 1 | 11 | 2016-01-01 | 2016-3-15 |
25 | 2 | 11 | 2016-3-16 | 2016-6-11 |
57 | 1 | 12 | 2016-03-17 | 2016-5-12 |
老师表
老师ID | 老师 | 性别 |
1 | 孔子 | 男 |
2 | 老子 | 男 |
班级表
班级ID | 班级 ID | 教室 |
11 | PHP4 | 203 |
12 | Java3 | 201 |
以上的设计就满足了第三范式.
可见,满足,每种实体,一张表,实体间的关系,使用关联字段表示,就可以满足第三范式!
3.4 为什么要满足范式?
尽可能减少数据冗余.
提升更新的便利性.
3.5 逆范式优化
为了提升某个操作的速度,而打破范式,称之为逆范式!
有种优化策略.
例如:
问题表 question
问题ID | 标题 | 发布时间 | 分类ID |
|
|
|
|
|
|
|
|
分类表: category
分类ID | 分类标题 |
|
|
|
|
下面的SQL需求:查询分类,及其分类对于的问题数量:
select c*, count(q.question_id) as question_number from category c left join question q using(category_id) group by c.category_id;
当,查询分类时,总是需要查询到分类对应的问题的数量
每当查询category,就需要连接 question.question的数据量一定会很大.
设计分类表时,强制增加一个字段,问题数量:
分类ID | 分类标题 | 问题数量 |
|
|
|
|
|
|
此时在一张表中就可以获取分类及其对应的问题数量.
效率是提升了,数据冗余了.打破了范式.就是逆范式的优化方案!
注意,打破范式,带来业务逻辑的增加.
必须要设计好再做,哪些操作会影响当前的数据设计,必须要同时更新才可以!
比如上例所述:当增加或删除问题时,需要额外增加数据逻辑,对分类表中对应的'问题数量'字段进行修改(增减),增加了业务量和数据风险.慎做!