Java-026-sql范式

关系数据库范式

数据库表设计

需求 –> API, 数据库 (数据库的表) –> 实现功能代码和操作数据!

• 应该有什么表? 用户表, 商品表, 订单表, 购物车表 –> RESTful API的资源所对应, Session表
• 每个表应该有什么的字段! 范式!
• 定义对数据库的SQL操作, 查询, 更新, 插入, 删除
• 建立Index索引, 对什么样的数据建立索引, 建立什么样的索引!

过度优化的边界: 是否真正理解了业务需求, 了解了瓶颈!

参考示例

用户表 user:

id	name	type	password	content
0	Alex	normal	123456	Here is the first
1	Alice	normal	123456	Here is thhe first user
2	Alex	vip	password	Here is the second Alex

订单表 order:

id	user_id	product_id	quantity	timestamp
0	0	15	5	203231232
1	2	15	1	324324234
2	2	1	3	213213219

第一范式 1NF

每一个字段都是不可分的数据项, 即字段的原子性.

要求

• 有主键, 且主键不能为空, 主键不能重复
• 字段必须不能再分, 字段必须是原子的

示例

用户表和订单表都包含主键 – id.

然后每一个字段都只包含单一不可分的数据内容项, 比如说, name就是只有name的意思, 不能够拆分.

一个常见的错误例子是, 比如用户表, 有一个项叫做信息项, 然后把相关的电话, 地址信息都放到这个信息项里, 这样就破坏了字段的原子性.

id	name	type	password	content	info
0	Alex	normal	123456	Here is the first	{“phone”:”13311122211”,”city”:”北京”,”email”:”xx@xx.com“}

坑:

没办法严格控制查询的粒度, 比如说, 如果只需要用户电话的时候, 就必须把所有信息内容查询出来, 然后再提取电话, 这样就造成了查询冗余, 影响性能. 同时也没办法通过使用电话这个信息, 在其他表里面查询相关信息, 例如电话的位置信息.

• 查 phone 要查询整个 info
• 查 email 要查询整个 info
• 查 city 要查询整个 info
• 而且造成了性能瓶颈
• 还有单独更新 phone/email/city 时候数据更新的问题,很容易造成数据被无意间更改

正确做法: 设计表的时候, 尽量每一个信息都用单独的字段, 比如电话字段, 地址信息字段

而如果 单独 phone/email/city 成为一列

查询仅需要

select phone from user;   
select email from user;   
select phone from city;

第二范式 2NF

在第一范式得基础上, 且每一个非主属性完全函数依赖于码.

要求

• 满足第一范例
• 表中得每一个非主键属性, 必须完全依赖于本表码, 码可以是主键, 或者多个字段组成的主键

示例

在订单表中, 一个订单由用户, 产品和订单时间确定. 那这三个属性就可以称之为码: (用户, 产品, 订单时间).

id	user_id	product_id	quantity	timestamp
0	0	15	5	20001000

这样, 如果相同用户, 相同产品, 相同订单时间就应该能够确定查询到其订单中产品数量, 而不允许有相同的用户, 相同产品, 相同订单时间, 但是订单数量不一样的数据产生.

user_id	product_id	quantity	timestamp
0	15	5	20001000
0	15	9	20001000

同一用户,同一时间,同一商品 不该存在 quantity 两种状态

如果添加其他的内容, 比如订单状态, 这个订单状态也应该完全依赖于码: (用户, 产品, 订单时间)

第三范式 3NF

满足第二范式的基础上, 除了主键外没有冗余数据

要求

• 数据没有冗余

示例

在用户表中, 主键是id, 然后包含用户名name字段, 这样在订单表中就不应该重复存储用户名, 只需要存储用户id (主键), 需要用到用户名等其他信息时候, 通过用户id (主键) 在用户表中查询.

反例

用户表

id	name	type	password	content

订单表 里有用户名

id	user_id user_name	product_id	quantity	timestamp

其他表里也有用户名 user_name

这样就导致了冗余

当你进行更新 user_name 的时候呢？

你要同时维护多张表。而且更新 user_name 要对每个出现的表检查是否更新

• 这样 问题出现的时候就会呈现指数型增长。
• 因为你不确定～ 那张表里有这个 user_name
• 要cover所有情况

正确做法就是 不要用 user_name

而应该使用 user_id

• 因为 主键是无法更改的
• 你用的时候连表就行了

目的

消除冗余数据: 一个数据只出现在同一地方, 同样保证了, 我们在维护一个数据时候, 只需要维护好一个地方的数据就好了! 不用去思考到底还有没有其他地方需要同时维护.

坑: 例如在用户表中有用户名, 在订单表中也有一份用户名, 则我们更改用户表用户名的时候, 就得同时更新订单表里的用户名!

在代码和数据的世界里, 重复是万恶之源

在代码和数据的世界里, 重复是万恶之源

在代码和数据的世界里, 重复是万恶之源

总结

范式主要是我们在设计数据库结构和字段时候需要遵循的一些规则, 用来避免出现的一些坑.

在设计或者Review数据库表的过程中, 需要对照范式来看是否出现反范式的情况.

但是既然不是硬语法规定, 那就是在实际应用中, 在需求, 性能或者特例的考量下, 可以根据实际情况进行妥协, 使用一些反范式的设计, 但是对这些特例需要有特别的关注.

一个原则永远记在心中: 重复是万恶之源, 在关系数据库里, 数据冗余是万恶之源

参考资料

• 如何解释关系数据库的第一第二第三范式
• Head First SQL (中文版)

代码链接