范式设计

第一大范式:

  • 数据库表中的所有字段都只具有单一属性;

  • 单一属性的列是由基本数据类型所构成的;

  • 设计出来的表都是简单的二维表。

反例:

id name-age
1 张三-23

正例:

id name age
1 张三 23

第二大范式:

要求表中只具有一个业务主键,也就是说符合第二范式的表不能存在非主键列只对部分主键的依赖关系。

反例:

产品表ID 产品名称
2 娃娃
3 飞机
4 java入门
订单表ID(主键) 订单时间 产品ID
1 2018-12-12 3
1 2018-12-12 4

正例:

产品表ID 产品名称
2 娃娃
3 飞机
4 java入门
订单表ID 订单时间
1 2018-12-12
订单-商品中间表ID 订单ID 产品ID
1 1 3
2 1 4

第三大范式:

指每一个非主属性既不部分依赖于也不传递依赖于业务主键,也就是在第二范式的基础上消除了非主键对主键的传递依赖。

反例:

订单表ID(主键) 订单时间 客户编号 客户姓名
1 2018-12-12 1 张三
2 2018-12-12 2 李四

客户编号和订单编号管理关联,客户姓名和订单编号管理关联,都没有问题。

但客户编号和客户姓名关联就很冗余了,应该把客户姓名这列删除,只放到客户表中。

数据库设计案例分析:

按要求设计一个电子商务网站的数据库结构,网站只销售图书类产品,需要具备的功能有:

  • 用户登陆;
  • 商品展示;
  • 供应商管理;
  • 用户管理;
  • 商品管理;
  • 订单销售。
用户登陆及用户管理:
  • 用户必须注册并登陆系统才能进行网上交易,用户名用来作为用户信息的业务主键;
  • 同一时间一个用户只能在一个地方登陆。
用户名 密码 手机号 姓名 注册时间 在线状态 出生日期
  • 只有一个业务主键,一定是符合第二范式;
  • 没有属性和业务主键存在传递依赖的关系,符合第三范式。
商品展示及商品管理功能:

反例:

商品信息表:

ID 商品名称 分类名称 出版社名称 图书价格 图书表述 作者

一本书可以属于多个分类,所以图书分类放在这个表中不合理,不符合第二大范式。

正例:

商品信息表:

ID 商品名称 出版社名称 图书价格 图书表述 作者

分类信息表:

分类名称 分类描述

商品分类对应关系表:

商品名称 分类名称
供应商管理功能:

供应商信息表:

出版社名称 地址 电话 联系人 银行账号
在线销售功能:

反例:

订单表:

订单编号 (pk) 下单用户名 下单日期 订单金额 订单商品分类 订单商品名(pk) 订单商品单价 订单商品数量 支付金额 物流单号
  • 有多个业务主键,不符合第二范式;
  • 订单商品单价,订单数量,订单金额存在传递依赖关系,不符合第三范式。

正例:

订单表:

订单编号 下单用户名 下单日期 支付金额 物流单号

订单商品关联表:

订单编号 订单商品分类 订单商品名 订单商品数量
订单编号 商品分类**ID** 订单商品数量

表汇总:

![image-20201228201531657](2020-12-27 01-范式设计.assets/image-20201228201531657.png)

编写SQL查询出每一个用户的订单总金额:

SELECT
a.单用户名,
sum(d.商品价格 * b.商品数量)
FROM
订单表 a
JOIN 订单分类关联表 b ON a.订单编号 = b.订单编号
JOIN 商品分类关联表 c ON c.商品分类ID = b.商品分类ID
JOIN 商品信息表 d ON d.商品名称 = c.商品名称
GROUP BY
a.下单用户名

一共4张表关联。

编写SQL查询出下单用户和订单详情:

SELECT
a.订单编号,
e.用户名,
e.手机号,
d.商品名称,
c.商品数量,
d.商品价格
FROM
订单表 a
JOIN 订单分类关联表 b ON a.订单编号 = b.订单编号
JOIN 商品分类关联表 c ON c.商品分类ID = b.商品分类ID
JOIN 商品信息表 d ON d.商品名称 = c.商品名称
JOIN 用户信息表 e ON e.用户名 = a.下单用户

一共5张表关联。

问题:大量的表关联非常影响查询的性能,完全符合范式化的设计有时并不能得到良好得SQL查询性能。

范式化设计优缺点:

优点:
  • 可以尽量得减少数据冗余;
  • 范式化的更新操作比反范式化更快;
  • 范式化的表通常比反范式化的表更小。
缺点:
  • 对于查询需要对多个表进行关联;
  • 更难进行索引优化。