2023年7月27日发(作者：)

数据库——关系模型设计数据库关系模型设计背景⽬前公司内部主流数据库是关系型数据库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|-|-|-|-|-|-|-|-|-||2|⼩明|北京|bose降噪⽿机|美国|1|2000|2000|2|⼩明|北京|华为p20|中国|1|1000|1000|2|⼩刚|上海|bose降噪⽿机|美国|1|2000|2000|2|⼩明|北京|iphone X|美国|1|2000|2000|2|⼩丽|唐⼭|华为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=utf8idaccount_nameaccount_address1⼩明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=utf8idproduct_nameproduct_addressproduct_price1bose降噪⽿机2华为p203iphone X美国中国美国2order_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=utf8idorder_noaccount_id12223242order_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_infoid -> product_idid -> product_numid -> sum_price(product_id,product_num) -> sum_priceorder_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=utf8idorder_noaccount_idsum_price3order_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=utf8idorder_idproduct_idproduct_num211311111我们通过进⼀步转换，消除传递依赖，使之满⾜第三范式。总结在本⽂描述的过程中，我们通过结合实例的⽅法，通俗地演绎了数据表规范化的过程，并展⽰了在此过程中数据冗余、数据库操作异常等问题是如何得到解决的，但对于订单信息的汇总，报表的形成增加了难度。规范化的过程就是，⼀步步提升关系范式级别的过程；三⼤范式只是⼀般设计数据库的基本理念，可是实际⼯作中我们根据业务场景（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））。候选码的各个属性成为主属性。不包含在任何候选码中的属性成为⾮主属性或⾮码属性；最简单的情况下，候选码只包含⼀个属性；在最极端情况下，关系模式中的所有属性是这个关系模型的属性码，成为全码。