前言
本章使用的测试数据库为GreatSQL8.0.25版本
(Wed Aug 3 16:17:03 2022)[root@GreatSQL][(none)]>select version();
+-----------+
| version() |
+-----------+
| 8.0.25-16 |
+-----------+
1 row in set (0.00 sec)
实战案例
假设有超市系统中的进货单表进行刨析<字段名称要设置为英文,为让读者更好理解这里翻译为中文>
单号 | 供应商编号 | 供应商名称 | 仓库 | 条码 | 名称 | 属性 | 数量 | 进货价格 | 进货金额 |
---|---|---|---|---|---|---|---|---|---|
100001 | 1 | 食品厂 | 仓库 | 0001 | 方便面 | 6包/袋 | 200 | 25 | 5000 |
100001 | 1 | 食品厂 | 仓库 | 0002 | 棒棒糖 | 10支/盒 | 400 | 9.9 | 3960 |
100002 | 2 | 服装厂 | 卖场 | 0003 | 西服 | 套 | 5 | 2000 | 10000 |
100003 | 1 | 食品厂 | 卖场 | 0002 | 棒棒糖 | 10支/盒 | 200 | 15 | 3000 |
可以看到这个表的字段很多,表里的数据量也是很大的,大量重复导致表变得庞大,效率极低,那我们要如何改造?
在实际的工作场景中,这种由于数据表结构设计不合理,从而导致数据重复的现象并不少见,往往是系统虽然能够运行,承载能力却很差,稍微有点流量,就会出现内存不足,或者是CPU使用率飙升的情况,甚至会导致整个项目失败。
迭代第一次:考虑1NF
第一范式的要求就是:所有字段都是基本数据字段,不可进一步拆分。这里需要确认,所有列中,每个字段只包含一种数据。
我们把这个表中的‘属性’字段,拆分成‘规格’和‘单位’,如下所示:
单号 | 供应商编号 | 供应商名称 | 仓库 | 条码 | 名称 | 规格 | 单位 | 数量 | 进货价格 | 进货金额 |
---|---|---|---|---|---|---|---|---|---|---|
100001 | 1 | 食品厂 | 仓库 | 0001 | 方便面 | 6包 | 袋 | 200 | 25 | 5000 |
100001 | 1 | 食品厂 | 仓库 | 0002 | 棒棒糖 | 10支 | 盒 | 400 | 9.9 | 3960 |
100002 | 2 | 服装厂 | 卖场 | 0003 | 西服 | NULL | 套 | 5 | 2000 | 10000 |
100003 | 1 | 食品厂 | 卖场 | 0002 | 棒棒糖 | 10支 | 盒 | 200 | 15 | 3000 |
迭代第二次:考虑2NF
第二范式的要求是,在满足第一范式的基础上,还要满足数据表里的每一条数据记录,都是唯一可标识的,而且所有字段,都必须完全依赖主键,不能只依赖主键的一部分。
第一步就是要确定这个表的主键,通过观察发现,字段‘单号’和‘条码’可以唯一标识每一条记录,可以作为主键。
此外,字段‘供应商编号’‘供应商名称’‘仓库’只依赖于‘单号’,不完全依赖于主键,所以,我们可以把这三个字段拆分出去,再加上它们依赖于字段‘单号’,所以形成一个新的表进货单头表
。剩下的字段会组成新的表,我们叫他进货单明细表
。
<字段名称要设置为英文,为让读者更好理解这里翻译为中文>
进货单头表:
单号 | 供应商编号 | 供应商名称 | 仓库 |
---|---|---|---|
100001 | 1 | 食品厂 | 仓库 |
100002 | 2 | 服装厂 | 卖场 |
100003 | 1 | 食品厂 | 卖场 |
进货单明细表:
单号 | 条码 | 数量 | 进货价格 | 进货金额 |
---|---|---|---|---|
100001 | 0001 | 200 | 25 | 5000 |
100001 | 0002 | 400 | 9.9 | 3960 |
100002 | 0003 | 5 | 2000 | 10000 |
100003 | 0002 | 200 | 15 | 3000 |
商品信息表:
条码 | 名称 | 规格 | 单位 |
---|---|---|---|
0001 | 方便面 | 6包 | 袋 |
0002 | 棒棒糖 | 10支 | 盒 |
0003 | 西服 | NULL | 套 |
现在,我们再来分析一下拆分后的3个表,保证这3个表都满足第二范式的要求。
第三步,在‘商品信息表’中,字段‘条码’是可能有存在重复
的,比如,用户门店可能有散装称重的商品,会从在条形码共用的情况,所以,所有字段都不能唯一标识表里的数据记录,这时候我们必须要给这个表加上一个自增字段
我们就可以把进货单明细表里面的字段‘条码’都替换成‘自增字段’这样就得到了新的表如下:
<字段名称要设置为英文,为让读者更好理解这里翻译为中文>
进货单头表:
单号 | 供应商编号 | 供应商名称 | 仓库 |
---|---|---|---|
100001 | 1 | 食品厂 | 仓库 |
100002 | 2 | 服装厂 | 卖场 |
100003 | 1 | 食品厂 | 卖场 |
(新)进货单明细表:
单号 | 自增字段 | 数量 | 进货价格 | 进货金额 |
---|---|---|---|---|
100001 | 1 | 200 | 25 | 5000 |
100001 | 2 | 400 | 9.9 | 3960 |
100002 | 3 | 5 | 2000 | 10000 |
100003 | 2 | 200 | 15 | 3000 |
(新)商品信息表:
自增字段 | 条码 | 名称 | 规格 | 单位 |
---|---|---|---|---|
1 | 0001 | 方便面 | 6包 | 袋 |
2 | 0002 | 棒棒糖 | 10支 | 盒 |
3 | 0003 | 西服 | NULL | 套 |
迭代第三次:考虑3NF
我们的进货单头表,可能还有数据冗余,因为‘供应商名称’字段依赖于‘供应商编号’那么这个表就要按照第三范式进行拆分了。
供货商表:
供应商编号 | 供应商名称 |
---|---|
1 | 食品厂 |
2 | 服装厂 |
进货单头表:
单号 | 供应商编号 | 仓库 |
---|---|---|
100001 | 1 | 仓库 |
100002 | 2 | 卖场 |
100003 | 1 | 卖场 |
这样这两个表都满足第三范式的要求了。
反范式化:业务优先的原则
在进货单明细表中,’数量‘‘进货价格’‘进货金额’这三个字段,可以通过任意两个字段都可以算出第三个字段来,这就存在冗余字段,但是如果严格按照第三范式的话,我们应该进一步优化,删除一个字段,保留另外两个,这样就没有冗余数据了。
可是要这样做的话,需要了解实际工作中的业务优先原则
业务优先原则:
一切需求按照业务需求为主,技术服务业务,完全的按照理论不一定就是最优的,还要根据实际的情况来决定。
如果我们把其中一个字段取消了,是会影响业务的,因为如果使用计算的话,可能会存在比较大的误差,这样日积月累,就会导致大的偏差,影响系统可靠性。
所以本着业务优先的原则,不影响系统可靠性的前提下,可以保留这三个字段。