关系型数据库的范式与反范式如何灵活运用

客户编号	客户类型	产品名称	产品数量	产品类型	产品价格
客户1	会员	产品1	1	零食	11
客户1	会员	产品2	2	粮油	12
客户1	会员	产品3	3	水果	13
客户2	非会员	产品1	1	零食	11
客户2	非会员	产品2	2	粮油	12
客户2	非会员	产品3	3	水果	13

把购物信息表分解为三个表：

产品类型表（产品类型，产品名称）

客户信息表（客户编号，客户类型）

产品信息表（产品名称，产品类型，产品价格，产品数量）

产品类型表：产品类型，产品名称。

产品类型	产品名称
零食	产品1
粮油	产品2
水果	产品3

客户信息表：客户编号，客户类型。

客户编号	客户类型
客户1	会员
客户2	非会员

产品信息表：产品名称，产品类型，产品价格，产品数量。

产品名称	产品类型	产品价格	产品数量
产品1	零食	11	1
产品2	粮油	12	2
产品3	水果	13	3

三，第三范式

1，描述

第三范式是在第二范式的基础上对数据库设计进行规范，要求是数据表中不存在非关键字段对任一后选关键字段的传递函数依赖。所谓传递函数依赖，指的是如果存在A决定B，B决定C的决定关系，则C传递函数依赖于A（表中其他列值必须唯一依赖于主键）。

2，例子

因此，满足第三范式的数据库表应该不存在依赖关系，假定员工信息表（员工编号，姓名，年龄，所在部门，部门电话），使用员工编号作为管用信息的主键，那么久存在决定关系，员工编号就决定了姓名，年龄，所在部门，部门电话这些字段。

员工信息表（员工编号，姓名，年龄，所在部门，部门电话）

员工编号	姓名	年龄	所在部门	部门电话
员工1	员工1	1	部门1	电话1
员工2	员工2	2	部门2	电话2

从上面关系可以看出，在表中有一个主键，数据表的设计符合第二范式，但不符合第三范式的要求，因为存在决定关系；员工编号就决定了所在部门，所在部门又决定了所在部门的电话；那么久存在了传递函数依赖关系，即员工编号决定部门电话，那么也会出现不满足第二范式时的数据冗余和更新，插入，删除异常的情况，为了满足第三范式的要求，必须把员工信息表插入分成如下两个数据表；

员工表（员工编号，姓名，年龄，所在部门）

员工编号	姓名	年龄	所在部门
员工1	员工1	1	部门1
员工2	员工2	2	部门2

部门表（部门名称，部门电话）

部门名称	部门电话
部门1	电话1
部门2	电话2

四，BCNF范式

1，描述

除了上面三种范式以外，还有一种经常使用的鲍依斯-科得范式(BCNF)。

它说明，建立在第三范式的基础上，如果数据库表中不存在任何字段对任一后选关键字点的传递函数依赖，那么就符合BCNF范式，BCNF的条件有：

1)所有非主属性对每一个候选键都是完全函数依赖；

2)所有主属性对每一个不包含它的候选键，也是完全函数依赖；

3)没有任何属性完全函数依赖于非候选键的任何一组属性。

解决第三范式的问题：数据冗余度大；插入操作复杂；删除操作复杂；修改操作复杂。

2，例子

假设仓库管理关系表(仓库ID,存储物品ID,管理员ID,数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：

(仓库ID,存储物品ID)→(管理员ID,数量) (管理员ID,存储物品ID)→(仓库ID,数量)

所以，(仓库ID,存储物品ID)和(管理员ID,存储物品ID)都是仓库管理关系表的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：

(仓库ID)→(管理员ID) (管理员ID)→(仓库ID)

即存在关键字段决定关键字段的情况，所以其不符合BCNF范式。

仓库管理关系表(仓库ID,存储物品ID,管理员ID,数量)

仓库ID	存储物品ID	管理员ID	数量
仓库A	物品A	管理员A	1
仓库A	物品B	管理员A	2
仓库B	物品A	管理员B	1
仓库B	物品B	管理员B	2

把仓库管理关系表分解为二个关系表：

仓库管理表：(仓库ID,管理员ID)；

仓库表：(仓库ID,存储物品ID,数量)。

仓库管理表：(仓库ID,管理员ID)；

仓库ID	管理员ID
仓库A	管理员A
仓库B	管理员B

仓库表：(仓库ID,存储物品ID,数量)。

仓库ID	存储物品ID	数量
仓库A	物品A	1
仓库A	物品B	2
仓库B	物品A	1
仓库B	物品B	2

五，第四范式

1，描述

设关系R（X，Y，Z），其中X，Y，Z是成对的、不相交属性的集合。

符合第三范式和BCNF之后，消去其中不是函数依赖的非平多值依赖。就是多对多

2，例子

一个用户有多个表空间,多个表

用户资源表1（用户名,表空间,表名称）

用户名	表空间	表名称
Sys	tablespace1	table1
Sys	tablespace1	table2

用户资源表2（用户名,名称,种类）

用户名	名称	种类
Sys	table1	table
Sys	table1	table
Sys	tablespace1	tablespace
Sys	tablespace1	tablespace

六，第五范式

1，描述

消除了第四范式中的连接依赖。

2，例子

我们在上述例子上进行修改，分成表空间的表和用户拥有表的表，我们可以回想一下，oracle貌似就是这么做的。

表空间的表（用户名,表空间）

用户名	表空间
Sys	tablespace1
Sys	tablespace2

用户拥有表（用户名,表名称）

用户名	表名称
Sys	table1
Sys	table2

七，完全函数依赖

1，描述

完全函数依赖：在一个关系中，若某个非主属性数据项依赖于全部关键字称之为完全函数依赖。如果非主属性B函数依赖于构成某个候选关键字的一组主属性A，而且A的任何一个真子集不能被B函数依赖，则称B完全函数依赖于A；反之，若B函数能依赖于A的真子集，则称B部分函数依赖于A。

2，例子

成绩表（学号，课程号，成绩）关系中，

完全函数依赖：（学号，课程号）→ 成绩，学号－\→ 成绩，课程号－\→ 成绩，所以（学号，课程号）→ 成绩是完全函数依赖

八，总结

不能说规范化程度越高的关系模式就越好，在设计数据库模式结构时，必须对现实世界的实际情况和用户需求做进一步分析，确定一个合适的、能够反映现实世界的模式。范式就是在数据库表设计时移除数据冗余的过程。随着范式级别的提升，数据冗余越来越少，但数据库的效率也越来越低。一般情况下，数据库设计满足第三范式即可。上面的规范化步骤可以在其中任何一步终止。规范化的过程可概括如下：

（1）取原始的1NF关系投影，消去非主属性对键的部门函数依赖，从而产生一组2NF关系。

（2）取2NF关系的投影，消去非主属性对键的传递函数依赖，产生一组3NF关系。

（3）取这些3NF的投影，消去决定因素不是键的函数依赖。产生一组BCNF关系。

（4）取这些BCNF关系的投影，消去其中不是函数依赖的非平多值依赖，产生一组4NF关系。

（5）取这些4NF的投影，消除了4NF中的连接依赖，产生一组5NF关系。

合理使用范式和反范式

范式的目的是为了减少数据冗余，但实际做开发中，往往需要部分数据冗余来提高我们的检索（索引，排序）的效率，所以需要混合使用来满足我们的实际开发，约定大于规范，空间换时间。

一，范式和反范式的优缺点

范式和反范式的优缺点
	优点	缺点
范式	1，更新通常比反范式更快； 2，当数据较好的范式化后，很少或者么有重复数据； 3，范式化的数据比较小，可以放在内存中，操作比较快。	通常需要进行关联
反范式	1，所有数据都在同一张表中，可以避免关联； 2，可以设计有效的索引；	表格内的冗余较多，删除数据时会造成表有些有用信息丢失

二，案例

1，社交软件的朋友圈社交软件的朋友圈的点赞，评论，转发数量。

可以在主表加对应的 num1,num2,num3分别表示点赞，评论，转发的数量。在相应的操作后可以用触发器对对应的数量字段进行更新，就避免了多表count。

范式设计

动态表          动态ID     用户ID       动态内容  

点赞表          点赞ID     动态ID       用户ID

评论表          评论ID     评论内容     动态ID      用户ID


SELECT   

dt.用户ID , dt.动态内容 ,  dz.点赞数量 , pl.评论数量

FROM    ‘动态表’  dt   

LEFT JOIN 
(SELECT   动态ID , count(动态ID) 点赞数量 FROM '点赞表' GROUP BY 动态ID) dz 
ON dt.动态ID  =  dz.动态ID

LEFT JOIN 
(SELECT   动态ID , count(动态ID) 评论数量 FROM '评论表' GROUP BY 动态ID) pl   
ON  pl.动态ID =  dz.动态ID

反范式设计

动态表       动态ID     用户ID        动态内容     点赞数量   评论数量

点赞表       点赞ID     动态ID        用户ID
   
评论表       评论ID     评论内容      动态ID       用户ID


SELECT   

dt.用户ID , dt.动态内容 ,  dt.点赞数量 , dt.评论数量

FROM    ‘动态表’  dt

2，商城软件的订单信息查询

范式设计

用户信息表         用户ID      姓名     电话       地址       年龄

订单表             订单ID      用户ID   下单时间   支付类型   订单状态

订单详情表         订单详情ID  商品ID   数量       价格

商品表             商品ID      商品名称   



SELECT    

user.用户名称 , user.地址 , user.电话 , dd.订单ID , SUM(sp.价格  *  sp.商品数量)订单价格

FROM   '订单表 '  dd

JOIN  '用户信息表'  user  ON dd.用户ID = user.用户ID

JOIN  '订单商品表'  sp ON sp . 订单ID = dd. 订单ID

GROUP BY   user .用户名，user.电话，user.地址，dd.订单ID

反范式设计

用户信息表   用户ID  姓名    电话   地址  年龄

订单表       订单ID  用户ID  姓名   地址  电话  下单时间  订单价格  支付类型 订单状态  

订单详情表   详情ID  商品ID  数量   价格

商品表       商品ID  商品名称   



SELECT   
  
dd.用户名称 , dd.地址 , dd.电话 , dd.订单ID , dd.订单价格

FROM   '订单表 '  dd