数据库——关系模型设计

数据库关系模型设计

背景

目前公司内部主流数据库是关系型数据库MySQL，数据库设计是对数据进行组织化和结构化的过程，即关系模型的设计。
对于项目规模小、用户数量少的情况，处理数据库中的表结构相对轻松；目前公司的发展速度快、用户数量多、项目规模大、业务逻辑极其复杂；
相应的数据库架构、关系模型表结构越来越复杂，这时我们往往会发现我们写出来的SQL语句是很笨拙并且效率低下的。更可怕的是，由于表结构定义不合理，会导致对数据的增删改查不方便不高效；最致命的是，扩展性极差，不能应对业务的变化。

此时对我们开发人员关系模式设计能力要求提高了，我们要学习和掌握数据库的规范化流程，以指导我们更好的设计数据库的表结构，减少冗余的数据，借此可以提高数据库的存储效率，数据完整性和可扩展性。

简洁、结构明晰的表结构对数据库的设计是相当重要的。规范化的表结构设计，在以后的数据维护中，不会发生插入（insert）、删除（delete）和更新（update）时的异常。反之，数据库表结构设计不合理，不仅会给数据库的使用和维护带来各种各样的问题，而且可能存储了大量不需要的冗余信息，浪费系统资源。

关于数据库关系模型设计的问题，就是在实际项目中，我们应该构造几个关系模型，每个关系（表）由哪些属性（列）组成，不同关系之间有什么关联。

规范化

一个低级范式的关系模型通过模式分解可以转为若干个高一级别范式的关系模型的集合，这个过程就叫规范化。

为了说明方便，我们用一个订单场景，来一步一步分析规范化的过程

如下只是为了演示，关系模型规范化的过程，存在不规范的地方

create table order_info (
    order_no varchar(10) not null comment '订单编号',
    account_name varchar(10) not null comment '会员姓名',
    account_address varchar(10) not null comment '会员地址',
    product_name varchar(10) not null comment '商品',
    product_address varchar(10) not null comment '产地',
    product_num int unsigned not null default 0 comment '数量',
    product_price double not null comment '单价',
    sum_price double not null comment '总价',
    primary key(order_no,account_name,product_name)
) engine=innodb default character set=utf8;

order_no	account_name	account_address	product_name	product_address	product_num	product_price	sum_price
201907080001	小明	北京	bose降噪耳机	美国	1	2000	2000
201907080001	小明	北京	华为p20	中国	1	1000	1000
201907080002	小刚	上海	bose降噪耳机	美国	1	2000	2000
201907080003	小明	北京	iphone X	美国	1	2000	2000
201907080004	小丽	唐山	华为p20	中国	1	1000	1000

这张表一共有8个字段，分析每个字段都有重复的值出现，也就是说，存在数据冗余问题。这将潜在地造成数据操作（比如删除、更新等操作）时的异常情况，因此，需要进行规范化。

第一范式

参照范式的定义，我们发现，这张表已经满足了第一范式的要求。

事实上在当前所有的关系数据库管理系统（DBMS）中，都已经在建表的时候强制满足第一范式，即关系型数据库原生满足第一范式，1NF是所有关系型数据库的最基本要求。
从业务的角度分析可能认为这张表不满足第一方式，因为地址可以再细化。

缺点

1. 数据冗余：每一个字段都有值重复
2. 更新复杂：更新用户或商品的地址时需要更新多条记录，即数据的一致性增加了成本

第二范式

参照范式的定义，我们发现，这张表不满足第二范式的要求。

• 存在依赖部分主键
  • (order_no,account_name,product_name) -> account_address
    • (account_name) -> account_address
  • (order_no,account_name,product_name) -> product_price
    • (product_name) -> product_price

我们按照范式对该关系模式进行分解转化：

account_info（用户表）

create table account_info(
    id int unsigned auto_increment comment '主键',
    account_name varchar(10) comment '用户姓名',
    account_address varchar(10) comment '地址',
    primary key(id)
)engine=innodb default charset=utf8

id	account_name	account_address
1	小明	北京
2	小刚	上海
3	小丽	唐山

product_info（商品表）

create table product_info(
    id int unsigned auto_increment comment '主键',
    product_name varchar(10) comment '商品名称',
    product_address varchar(10) comment '产址',
    product_price double comment '单价',
    primary key(id)
)engine=innodb default charset=utf8

id	product_name	product_address	product_price
1	bose降噪耳机	美国	2000
2	华为p20	中国	1000
3	iphone X	美国	2000

order_info（订单表）

create table order_info(
    id int unsigned auto_increment comment '主键',
    order_no varchar(10) comment '订单号',
    account_id int comment '用户id',
    primary key(id)
)engine=innodb default charset=utf8

id	order_no	account_id
1	201907080001	1
2	201907080002	2
3	201907080003	3
4	201907080004	1

order_detial_info（订单商品表）

create table order_detial_info(
    id int unsigned auto_increment comment '主键',
    order_id int comment '订单id',
    product_id int comment '商品id',
    product_num int comment '商品数量',
    sum_price double comment '商品合计',
    primary key(id)
)engine=innodb default charset=utf8

id	order_id	product_id	product_num	sum_price
1	1	1	1	2000
2	1	2	1	1000
3	2	1	1	2000
4	3	1	1	2000
5	4	3	1	1000

从第一范式到第二范式，一个订单关系表衍生了多个关系表。

第三范式

参照范式的定义，我们发现，存在传递依赖
order_detial_info

• id -> product_id
• id -> product_num
• id -> sum_price
• (product_id,product_num) -> sum_price

order_info（订单表）

create table order_info(
    id int unsigned auto_increment comment '主键',
    order_no varchar(10) comment '订单号',
    account_id int comment '用户id',
    sum_price double comment '商品合计',
    primary key(id)
)engine=innodb default charset=utf8

id	order_no	account_id	sum_price
1	201907080001	1	3000
2	201907080002	2	2000
3	201907080003	3	2000
4	201907080004	1	1000

order_detial_info（订单商品表）

create table order_detial_info(
    id int unsigned auto_increment comment '主键',
    order_id int comment '订单id',
    product_id int comment '商品id',
    product_num int comment '商品数量',
    primary key(id)
)engine=innodb default charset=utf8

id	order_id	product_id	product_num
1	1	1	1
2	1	2	1
3	2	1	1
4	3	1	1
5	4	3	1

我们通过进一步转换，消除传递依赖，使之满足第三范式。

总结

在本文描述的过程中，我们通过结合实例的方法，通俗地演绎了数据表规范化的过程，并展示了在此过程中数据冗余、数据库操作异常等问题是如何得到解决的，但对于订单信息的汇总，报表的形成增加了难度。

规范化的过程就是，一步步提升关系范式级别的过程；三大范式只是一般设计数据库的基本理念，可是实际工作中我们根据业务场景（OLAP/OLTP）出于性能或扩展的考虑，出发点不一样，在空间和时间上做的取舍不一样，关系模型设计只满足2NF或1NF，也就是反范式；但作为开发人员一定要了解关系模式设计规范化的过程。

范式

• 第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF（列不可再分，无重复的列）;
• 第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足- 第二范式，简记为2NF（属性依赖完全主键（主键/所有复合主键），不能依赖主键的一部分，消除部分依赖）;
• 第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF（属性不依赖于其他非主属性，消除传递依赖）;
• 数据库范式除了上述三个范式还有BCNF、4NF等，这里不再一一讲解

知识拓展

第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

候选码：若关系（表）中某一属性（列）组的值能唯一标识一个元组（行），而其子集不能，则称该属性组为候选码（一个关系中可能存在多个候选码，则选定其中一个作为主码（主键 primary key））。

候选码的各个属性成为主属性。不包含在任何候选码中的属性成为非主属性或非码属性；

最简单的情况下，候选码只包含一个属性；在最极端情况下，关系模式中的所有属性是这个关系模型的属性码，成为全码。