当前位置： 首页 > article >正文

MySQL数据库入门到大蛇尚硅谷宋红康老师笔记 高级篇 part11

article 2025/3/22 20:25:58

第11章_数据库的设计规范

良好的数据库设计则有以下优点：

  • ·节省数据的存储空间
  • ·能够保证数据的完整性
  • ·方便进行数据库应用系统的开发

总之，开始设置数据库的时候，我们就需要重视数据表的设计。为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。

2.范式 

在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。可以理解为，一张数据表的设计结构需要满足的某种设计标准的级别。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

范式的英文名称是NormalForm，简称NF。它是英国人E.F.Codd在上个世纪70年代提出关系数据库模型后总结出来的。范式是关系数据库理论的基础，也是我们在设计数据库结构过程中所要遵循的规则和指导方法。

目前关系型数据库有六种常见范式，按照范式级别，从低到高分别是：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，又称完美范式）。

数据库的范式设计越高阶，余度就越低，同时高阶的范式一定符合低阶范式的要求，满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。

一般来说，在关系型数据库设计中，最高也就遵循到BCNF，普遍还是3NF。但也不绝对，有时候为了提高某些查询性能，我们还需要破坏范式规则，也就是反规范化。

2.3 键和相关属性的概念 

范式的定义会使用到主键和候选键，数据库中的键（Key）由一个或者多个属性组成。数据表中常用的几种键和属性的定义：

  • ·超键：能唯一标识元组的属性集叫做超键。
  • ·候选键：如果超键不包括多余的属性，那么这个超键就是候选键。
  • ·主键：用户可以从候选键中选择一个作为主键。
  • ·外键：如果数据表R1中的某属性集不是R1的主键，而是另一个数据表R2的主键，那么这个属性集就是数据表R1的外键。
  • ·主属性：包含在任一候选键中的属性称为主属性。
  • ·非主属性：与主属性相对，指的是不包含在任何一个候选键中的属性。

通常，我们也将候选键称之为“码”，把主键也称为“主码”。因为键可能是由多个属性组成的，针对单个属性，我们还可以用主属性和非主属性来进行区分

 球员表（player）：球员编号丨姓名丨身份证号丨年龄|球队编号
球队表（team）：球队编号|主教练|球队所在地

  • ·超键：对于球员表来说，超键就是包括球员编号或者身份证号的任意组合，比如（球员编号）（球员编号，姓名）（身份证号，年龄）等。
  • ·候选键：就是最小的超键，对于球员表来说，候选键就是（球员编号）或者（身份证号）
  • ·主键：我们自己选定，也就是从候选键中选择一个，比如（球员编号）。
  • ·外键：球员表中的球队编号。
  • ·主属性、非主属性：在球员表中，主属性是（球员编号）（身份证号），其他的属性（姓名）（年龄）（球队编号）都是非主属性。

 2.4第一范式(1st NF)

第一范式主要是确保数据表中每个字段的值必须具有原子性，也就是说数据表中每个字段的值为不可再次拆分的最小数据单元。

举例1：

假设一家公司要存储员工的姓名和联系方式。它创建一个如下表：

emp_id | emp_name | emp_address | emp_mobile
101    | zhangsan | beijing     | 8912312390
102    | lisi     | liaoning    | 8812121212
103    | wangwu   | hebei       | 7778812
104    | zhaoliu  | shanghai    | 9999000012 1878120923

该表不符合1NF，因为规则说“表的每个属性必须具有原子（单个）值”，lisi和zhaoliu员工的emp_mobile值违反了该规则。为了使表符合1NF，我们应该有如下表数据：

emp_id | emp_name | emp_address | emp_mobile
101    | zhangsan | beijing     | 8912312390
102    | lisi     | liaoning    | 8812121212
102    | lisi     | liaoning    | 9900012222
103    | wangwu   | hebei       | 777881212
104    | zhaoliu  | shanghai    | 9999000012
104    | zhaoliu  | shanghai    | 1878120923

user 表的设计不符合第一范式

字段名称    字段类型     是否是主键  说明
id         INT         是        主键id
username   VARCHAR(30)  否        用户名
password   VARCHAR(50)  否        密码
user_info  VARCHAR(255) 否        用户信息(包含真实姓名、电话、住址)

其中，user_info字段为用户信息，可以进一步拆分成更小粒度的字段，不符合数据库设计对第一范式的要求。将user_info拆分后如下：

字段名称    字段类型     是否是主键  说明
id         INT         是        主键id
username   VARCHAR(30)  否        用户名
password   VARCHAR(50)  否        密码
real_name  VARCHAR(30)  否        真实姓名
phone      VARCHAR(12)  否        联系电话
address    VARCHAR(100) 否        家庭住址

2.5第二范式（2nd NF）

第二范式要求，在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分*。如果知道主键的所有属性的值，就可以检索到任何元组（行）的任何属性的任何值。（要求中的主键，其实可以拓展替换为候选键）。

比赛表 player_game，里面包含球员编号、姓名、年龄、比赛编号、比赛时间和比赛场地等属性，这里候选键和主键都为（球员编号，比赛编号），我们可以通过候选键（或主键）来决定如下的关系：
（球员编号，比赛编号）→（姓名，年龄，比赛时间，比赛场地，得分）

但是这个数据表不满足第二范式，因为数据表中的字段之间还存在着如下的对应关系：
（球员编号）→（姓名，年龄）
（比赛编号）→（比赛时间，比赛场地）

对于非主属性来说，并非完全依赖候选键。这样会产生怎样的问题呢？
1. 数据冗余：如果一个球员可以参加m场比赛，那么球员的姓名和年龄就重复了m-1次。一个比赛也可能会有n个球员参加，比赛的时间和地点就重复了n-1次。
2. 插入异常：如果我们想要添加一场新的比赛，但是这时还没有确定参加的球员都有谁，那么就没法插入。
3. 删除异常：如果我要删除某个球员编号，如果没有单独保存比赛表的话，就会同时把比赛信息删除掉。

4. 更新异常：如果我们调整了某个比赛的时间，那么数据表中所有这个比赛的时间都需要进行调整，否则就会出现一场比赛时间不同的情况。

为了避免出现上述的情况，我们可以把球员比赛表设计为下面的三张表。

表名                属性（字段）
球员 player 表       球员编号、姓名和年龄等属性
比赛 game 表         比赛编号、比赛时间和比赛场地等属性
球员比赛关系 player_game 表    球员编号、比赛编号和得分等属性

这样的话，每张数据表都符合第二范式，也就避免了异常情况的发生。

1NF 告诉我们字段属性需要是原子性的，而 2NF 告诉我们一张表就是一个独立的对象，一张表只表达一个意思。

  小结：第二范式（2NF）要求实体的属性完全依赖主关键字。如果存在不完全依赖，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与元实体之间是一对多的关系。

2.6第三范式（3rd NF）

第三范式是在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段。（即，不能存在非主属性A依赖于非主属性B，非主属性B依赖于主键C的情况，即存在“A一B一C"的决定关系）通俗地讲，该规则的意思是所有非主键属性之间不能有依赖关系，必须相互独立。

球员player表：球员编号、姓名、球队名称和球队主教练。现在，我们把属性之间的依赖关系画出来，如下图所示：

二范式和三范式有相似性。区分起来的时候按主属性侧重和非主属性侧重来区分就好了

 符合3NF后的数据模型通俗地讲，2NF和3NF通常以这句话概括：“每个非键属性依赖于键，依赖于整个键，并且除了键别无他物”。总结一下:一范式的话要求列不可再分，二范式要求不能部分依赖，三范式要求不能依赖传递，而是直接依赖

范式的优点：数据的标准化有助于消除数据库中的数据余，第三范式（3NF）通常被认为在性能、扩展性和数据完整性方面达到了最好的平衡。

范式的缺点：范式的使用，可能降低查询的效率。因为范式等级越高，设计出来的数据表就越多、越精细，数据的冗余度就越低，进行数据查询的时候就可能需要关联多张表，这不但代价昂贵，也可能使一些索引策略无效。

范式只是提出了设计的标准，实际上设计数据表时，未必一定要符合这些标准。开发中，我们会出现为了性能和读取效率违反范式化的原则，通过增加少量的余或重复的数据来提高数据库的读性能，减少关联查询，join表的次数，实现空间换取时间的目的。因此在实际的设计过程中要理论结合实际，灵活运用。
没有完美的设计，只有合适的设计。

 3. 反范式化

如果数据库中的数据量比较大，系统的UV和PV访问频次比较高，则完全按照MySQL的三大范式设计数据表，读数据时会产生大量的关联查询，在一定程度上会影响数据库的读性能。如果我们想对查询效率进行优化，反范式优化也是一种优化思路。此时，可以通过在数据表中增加余字段来提高数据库的读性能。 

举例1： 员工的信息存储在 employees 表中，部门信息存储在 departments 表中。通过 employees 表中的 department_id 字段与 departments 表建立关联关系。如果要查询一个员工所在部门的名称：

select employee_id, department_name
from employees e join departments d
on e.department_id = d.department_id;

如果经常需要进行这个操作，连接查询就会浪费很多时间。可以在 employees 表中增加一个冗余字段 department_name，这样就不用每次都进行连接操作了。

举例2： 反范式化的 goods 商品信息表设计如下：

字段名称字段类型是否是主键说明
idINT是商品id（主键）
category_idVARCHAR(30)A否商品类别id
category_nameVARCHAR(30)否商品类别名称
goods_nameVARCHAR(30)否商品名称
priceDECIMAL(10,2)否商品价格
#07-数据表的设计规范

#反范式化的举例：

CREATE DATABASE atguigudb3;

USE atguigudb3;

#学生表
CREATE TABLE student(
stu_id INT PRIMARY KEY AUTO_INCREMENT,
stu_name VARCHAR(25),
create_time DATETIME
);

#课程评论表
CREATE TABLE class_comment(
comment_id INT PRIMARY KEY AUTO_INCREMENT,
class_id INT,
comment_text VARCHAR(35),
comment_time DATETIME,
stu_id INT
);

###创建向学生表中添加数据的存储过程
DELIMITER //

CREATE PROCEDURE batch_insert_student(IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2017-01-01 00:00:00');
DECLARE date_temp DATETIME;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = DATE_ADD(date_temp, INTERVAL RAND()*60 SECOND);
INSERT INTO student(stu_id, stu_name, create_time)
VALUES((START+i), CONCAT('stu_',i), date_temp);
UNTIL i = max_num
END REPEAT;
COMMIT;
END //

DELIMITER ;

#调用存储过程，学生id从10001开始，添加1000000数据
CALL batch_insert_student(10000,1000000);

####创建向课程评论表中添加数据的存储过程
DELIMITER //

CREATE PROCEDURE batch_insert_class_comments(IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2018-01-01 00:00:00');
DECLARE date_temp DATETIME;
DECLARE comment_text VARCHAR(25);
DECLARE stu_id INT;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = DATE_ADD(date_temp, INTERVAL RAND()*60 SECOND);
SET comment_text = SUBSTR(MD5(RAND()),1, 20);
SET stu_id = FLOOR(RAND()*1000000);
INSERT INTO class_comment(comment_id, class_id, comment_text, comment_time, stu_id)
VALUES((START+i), 10001, comment_text, date_temp, stu_id);
UNTIL i = max_num
END REPEAT;
COMMIT;
END //

DELIMITER ;

#添加数据的存储过程的调用，一共1000000条记录
CALL batch_insert_class_comments(10000,1000000);

#########
SELECT COUNT(*) FROM student;

SELECT COUNT(*) FROM class_comment;

###需求######
SELECT p.comment_text, p.comment_time, stu.stu_name 
FROM class_comment AS p LEFT JOIN student AS stu 
ON p.stu_id = stu.stu_id 
WHERE p.class_id = 10001 
ORDER BY p.comment_id DESC 
LIMIT 10000;

耗费0.016s

#####进行反范式化的设计######
#表的复制
CREATE TABLE class_comment1
AS
SELECT * FROM class_comment;

#添加主键，保证class_comment1 与class_comment的结构相同
ALTER TABLE class_comment1
ADD PRIMARY KEY (comment_id);

SHOW INDEX FROM class_comment1;

#向课程评论表中增加stu_name字段
ALTER TABLE class_comment1
ADD stu_name VARCHAR(25);

#给新添加的字段赋值
UPDATE class_comment1 c
SET stu_name = (
SELECT stu_name
FROM student s
WHERE c.stu_id = s.stu_id
);

#查询同样的需求
SELECT comment_text, comment_time, stu_name 
FROM class_comment1 
WHERE class_id = 10001 
ORDER BY comment_id DESC 
LIMIT 10000;

 3.3反范式的新问题

反范式可以通过空间换时间，提升查询的效率，但是反范式也会带来一些新问题：
·存储空间变大了
·一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则数据不一致
·若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常消耗系统资源
·在数据量小的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加复杂

3.4反范式的适用场景

当冗余信息有价值或者能大幅度提高查询效率的时候，我们才会采取反范式的优化。

 增加冗余字段一定要符合如下两个条件。只有满足这两个条件，才可以考虑增加冗余字段。
1）这个冗余字段不需要经常进行修改；
2）这个几余字段查询的时候不可或缺。

2.历史快照、历史数据的需要

在现实生活中，我们经常需要一些冗余信息，比如订单中的收货人信息，包括姓名、电话和地址等。每次发生的订单收货信息都属于历史快照，需要进行保存，但用户可以随时修改自己的信息，这时保存这些余信息是非常有必要的。
反范式优化也常用在数据仓库的设计中，因为数据仓库通常存储历史数据，对增删改的实时性要求不强，对历史数据的分析需求强。这时适当允许数据的冗余度，更方便进行数据分析。

 4.BCNF（巴斯范式）

人们在3NF的基础上进行了改进，提出了巴斯范式（BCNF），也叫做巴斯-科德范式（Boyce-CoddNormal Form）。BCNF被认为没有新的设计规范加入，只是对第三范式中设计规范要求更强，使得数据库余度更小。所以，称为是修正的第三范式，或扩充的第三范式，BCNF不被称为第四范式。
若一个关系达到了第三范式，并且它只有一个候选键，或者它的每个候选键都是单属性，则该关系自然达到BC范式。
一般来说，一个数据库设计符合3NF或BCNF就可以了。

1.案例            我们分析如下表的范式情况：

仓库名管理员物品名数量
北京仓张三iphone XR10
北京仓张三iphone 720
上海仓李四iphone 7p30
上海仓李四iphone 840

在这个表中，一个仓库只有一个管理员，同时一个管理员也只管理一个仓库。我们先来梳理下这些属性之间的依赖关系。

仓库名决定了管理员，管理员也决定了仓库名，同时（仓库名，物品名）的属性集合可以决定数量这个属性。这样，我们就可以找到数据表的候选键。

候选键：是（管理员，物品名）和（仓库名，物品名），然后我们从候选键中选择一个作为主键，比如（仓库名，物品名）。

主属性：包含在任一候选键中的属性，也就是仓库名，管理员和物品名。

非主属性：数量这个属性。

2. 是否符合三范式？？？  是符合的

3.存在的问题

既然数据表已经符合了 3NF 的要求，是不是就不存在问题了呢？我们来看下面的情况：

  1. 增加一个仓库，但是还没有存放任何物品。根据数据表实体完整性的要求，主键不能有空值，因此会出现插入异常；
  2. 如果仓库更换了管理员，我们就可能会修改数据表中的多条记录；
  3. 如果仓库里的商品都卖空了，那么此时仓库名称和相应的管理员名称也会随之被删除。

你能看到，即便数据表符合 3NF 的要求，同样可能存在插入、更新和删除数据的异常情况。

首先我们需要确认造成异常的原因：主属性仓库名对于候选键（管理员，物品名）是部分依赖的关系，这样就有可能导致上面的异常情况。因此引入BCNF，它在3NF的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。如果在关系R中，U为主键，A属性是主键的一个属性，若存在A->Y，Y为主属性，则该关系不属于BCNF。

 主属性之间不能存在依赖，非主属性之间不能依赖。主属性和非主属性不能部份依赖

5.第四范式（看看就行

多值依赖的概念：

  • 多值依赖 即属性之间的一对多关系，记为 K→→A。

  • 函数依赖 事实上是单值依赖，所以不能表达属性值之间的一对多关系。

  • 平凡的多值依赖：全集 U=K+A，一个 K 可以对应于多个 A，即 K→→A。此时整个表就是一组一对多关系。

  • 非平凡的多值依赖：全集 U=K+A+B，一个 K 可以对应于多个 A，也可以对应于多个 B，A 与 B 互相独立，即 K→→A，K→→B。整个表有多组一对多关系，且有：“一部分是相同的属性集合，“多”部分是互相独立的属性集合。

第四范式即在满足巴斯-科德范式（BCNF）的基础上，消除非平凡且非函数依赖的多值依赖（即把同一表内的多对多关系删除）。

举例1：职工表（职工编号，职工孩子姓名，职工选修课程）。

在这个表中，同一个职工可能会有多个职工孩子姓名。同样，同一个职工也可能会有多个职工选修课程，即这里存在着多值事实，不符合第四范式。

如果要符合第四范式，只需要将上表分为两个表，使它们只有一个多值事实，例如：职工表一（职工编号，职工孩子姓名），职工表二（职工编号，职工选修课程），两个表都只有一个多值事实，所以符合第四范式。

6.第五范式，域键范式（看看就行

在满足第四范式（4NF）的基础上，消除不是由候选键所蕴含的连接依赖。如果关系模式R中的每一个连接依赖均由R的候选键所隐含，则称此关系模式符合第五范式。

7.实战案例 

进货单表：

listnumber (单号)supplierid (供应商编号)suppliername (供应商名称)stock (仓库)barcode (条码)goodsname (名称)property (属性)quantity (数量)importprice (进货价格)importvalue (进货金额)
1000011食品厂仓库0001方便面6包/袋200255000
1000011食品厂仓库0002棒棒糖10支/盒4009.93960
1000022服装厂卖场0003西服套5200010000
1000031食品厂卖场0002棒棒糖10支/盒200153000

这个表中的字段很多，表里的数据量也很惊人。大量重复导致表变得庞大，效率极低。如何改造？

在实际工作场景中，这种由于数据表结构设计不合理，而导致的数据重复的现象并不少见。往往是系统虽然能够运行，承载能力却很差，稍微有点流量，就会出现内存不足、CPU 使用率飙升的情况，甚至会导致整个项目失败。

7.1 迭代1次：考虑1NF

第一范式要求：所有的字段都是基本数据字段，不可进一步拆分。这里需要确认，所有的列中，每个字段只包含一种数据。

7.2 迭代2次：考虑2NF

第二范式要求，在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的。而且所有字段，都必须完全依赖主键，不能只依赖主键的一部分。

确定这个表的主键。通过观察发现，字段"listnumber（单号)+"barcode（条码）"可以唯一标识每一条记录，可以作为主键。

原来的数据表就拆分成了3个表。
进货单头表：

listnumbersupplieridsuppliernamestock
1000011食品厂仓库
1000022服装厂卖场
1000031食品厂卖场

进货单明细表：

listnumberbarcodequantityimportpriceimportvalue
1000010001200255000
10000100024009.93960
10000200035200010000
1000030002200153000

商品信息表：

barcodegoodsnamespecificationunit
0001方便面6包袋
0002棒棒糖10支盒
0003西服NULL套

 比如商品信息表中的“棒棒糖”可能重复，比如这里是盒子，但是实际上可能散装论斤卖条码共用，品，会存在条码共用的情况。所以，所有的字段都不能唯一标识表里的记录。这个时候，我们必须给这个表加上一个主键，比如说是自增字段“itemnumber”

7.3 迭代三次：考虑3NF

 7.4 反业务化

可是，真的可以这样做吗？要回答这个问题，我们就要先了解下实际工作中的业务优先原则。
所谓的业务优先原则，就是指一切以业务需求为主，技术服务于业务。完金按照理论的设计不一定就是最优，还要根据实际情况来决定。这里我们就来分析一下不同选择的利与弊。

对于quantity*importprice=importvalue，看起来“importvalue"似乎是余字段，但并不会导致数据不一致。可是，如果我们把这个字段取消，是会影响业务的。
因为有的时候，供货商会经常进行一些促销活动，按金额促销，那他们拿来的进货单只有金额，没有价格。而"importprice"反而是通过“importvalue"+"quantity"计算出来的，经过四舍五入，会产生较大的误差。这样日积月累，最终会导致查询结果出现较大偏差，影响系统的可靠性。

8. ER模型

ER模型就是一个这样的工具。ER模型也叫作实体关系模型，是用来描述现实生活中客观存在的事物、事物的属性，以及事物之间关系的一种数据模型。在开发基于数据库的信息系统的设计阶段，通常使用ER模型来描述信息需求和信息特性，帮助我们理清业务逻辑，从而设计出优秀的数据库。（你应该在中国高校的数据库系统导论中第3-4节课大概就能学到

8.1 ER 模型包括哪些要素？

ER 模型中有三个要素，分别是实体、属性和关系。

  • 实体，可以看做是数据对象，往往对应于现实生活中的真实存在的个体。在 ER 模型中，用矩形来表示。实体分为两类，分别是强实体和弱实体。强实体是指不依赖于其他实体的实体；弱实体是指对另一个实体有很强的依赖关系的实体。
  • 属性，则是指实体的特性。比如超市的地址、联系电话、员工数等。在 ER 模型中用椭圆形来表示。
  • 关系，则是指实体之间的联系。比如超市把商品卖给顾客，就是一种超市与顾客之间的联系。在 ER 模型中用菱形来表示。

注意：实体和属性不容易区分！这里提供一个原则：我们要从系统整体的角度出发去看，可以独立存在的是实体，不可再分的是属性。也就是说，属性不能包含其他属性。

8.2 关系的类型

在 ER 模型的 3 个要素中，关系又可以分为 3 种类型，分别是一对一、一对多、多对多。

  1. 一对一：指实体之间的关系是一一对应的，比如个人与身份证信息之间的关系就是一对一的关系。一个人只能有一个身份证信息，一个身份证信息也只属于一个人。
  2. 一对多：指一边的实体通过关系，可以对应多个另外一边的实体。相反，另外一边的实体通过这个关系，则只能对应唯一的一边的实体。比如说，我们新建一个班级表，而每个班级都有多个学生，每个学生则对应一个班级，班级对学生就是一对多的关系。
  3. 多对多：指关系两边的实体都可以通过关系对应多个对方的实体。比如在进货模块中，供货商与超市之间的关系就是多对多的关系，一个供货商可以给多个超市供货，一个超市也可以从多个供货商那里采购商品。再比如一个选课表，有许多科目，每个科目有很多学生选，而每个学生又可以选择多个科目，这就是多对多的关系。

我们设计的案例是电商业务，由于电商业务太过庞大且复杂，所以我们做了业务简化，比如针对
SKU（StockKeepingUnit，库存量单位）和SPU（StandardProduct Unit，标准化产品单元）的含义上，我们直接使用了SKU，并没有提及SPU的概念。本次电商业务设计总共有8个实体，如下所示。

  1. ·地址实体
  2. ·用户实体
  3. ·购物车实体
  4. ·评论实体
  5. ·商品实体
  6. ·商品分类实体
  7. ·订单实体
  8. ·订单详情实体

其中，用户和商品分类是强实体，因为它们不需要依赖其他任何实体。而其他属于弱实体，因为它们虽然都可以独立存在，但是它们都依赖用户这个实体，因此都是弱实体。知道了这些要素，我们就可以给电商业务创建ER模型了，如图：

 

 有了这个ER模型，我们就可以从整体上理解电商的业务了。刚刚的ER模型展示了电商业务的框架，但是只包括了订单，地址，用户，购物车，评论，商品，商品分类和订单详情这八个实体，以及它们之间的关系，还不能对应到具体的表，以及表与表之间的关联。我们需要把属性加上，用椭圆来表示，这样我们得到的ER模型就更加完整了。
因此，我们需要进一步去设计一下这个ER模型的各个局部，也就是细化下电商的具体业务流程，然后把它们综合到一起，形成一个完整的ER模型。这样可以帮助我们理清数据库的设计思路。

接下来，我们再分析一下各个实体都有哪些属性，如下所示。

  • （1）地址实体包括用户编号、省、市、地区、政件人、联系电话、是否是默认地址。
  • （2）用户实体包括用户编号、用户名称、昵称、用户密码、手机号、邮箱、头像、用户级别。
  • （3）购物车实体包括购物车编号、用户编号、商品编号、商品数量、图片文件url。
  • （4）订单实体包括订单编号、收货人、收件人电话、总金额、用户编号、付款方式、送货地址、下单时间。
  • （5）订单详情实体包括订单详情编号、订单编号、商品名称、商品编号、商品数量。
  • （6）商品实体包括商品编号、价格、商品名称、分类编号、是否销售，规格、颜色。
  • （7）评论实体包括评论id、评论内容、评论时间、用户编号、商品编号
  • （8）商品分类实体包括类别编号、类别名称、父类别编号

8.5 ER模型图转换成数据表

通过绘制ER模型，我们已经理清了业务逻辑，现在，我们就要进行非常重要的一步了：把绘制好的ER模型，换成具体的数据表，下面介绍下转换的原则：

(1）一个实体通常转换成一个数据表；
(2）一个多对多的关系，通常也转换成一个数据表；
(3）一个1对1，或者1对多的关系，往往通过表的外键来表达，而不是设计一个新的数据表；
(4）属性转换成表的字段。

用户实体转换成用户表（user_info）的代码如下所示。

CREATE TABLE `user_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `user_name` varchar(200) DEFAULT NULL COMMENT '用户名称',
  `nick_name` varchar(200) DEFAULT NULL COMMENT '用户昵称',
  `passwd` varchar(200) DEFAULT NULL COMMENT '用户密码',
  `phone_num` varchar(200) DEFAULT NULL COMMENT '手机号',
  `email` varchar(200) DEFAULT NULL COMMENT '邮箱',
  `head_img` varchar(200) DEFAULT NULL COMMENT '头像',
  `user_level` varchar(200) DEFAULT NULL COMMENT '用户级别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='用户表';

商品分类实体转换成商品分类表（base_category），由于商品分类可以有一级分类和二级分类，
比如一级分类有家电、手机等等分类，二级分类可以根据手机的一级分类分为手机配件、运营商等
，这里我们把商品分类实体规划为两张表，分别是一级分类表和二级分类表，之所以这么规划是因
为一级分类和二级分类都是有限的，存储为两张表业务结构更加清晰。

# 一级分类表
CREATE TABLE `base_category1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(10) NOT NULL COMMENT '分类名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='一级分类表';

# 二级分类表
CREATE TABLE `base_category2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(20) NOT NULL COMMENT '二级分类名称',
  `category1_id` bigint(20) DEFAULT NULL COMMENT '一级分类编号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='二级分类表';

那么如果规划为一张表呢，表结构如下所示。

CREATE TABLE `base_category` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(200) NOT NULL COMMENT '分类名称',
  `category_parent_id` bigint(20) DEFAULT NULL COMMENT '父分类编号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='分类表';

如果这样分类的话，那么查询一级分类的时候，就需要判断父分类编号是否为空，但是如果插
入一级分类的时候也是空，就容易造成业务数据混乱。而且查询二级分类的时候IS NOT NULL
条件是无法使用到索引的。同时，这样的设计也不符合第二范式（因为父分类编号并不依赖分
类编号ID，因为父分类编号可以有很多数据为NULL），所以需要进行表的拆分。因此无论是业
务需求还是数据库表的规范来看都应该拆分为两张表。


...................一共建立了5个表
2.一个多对多的关系转换成一个数据表

这个 ER 模型中的多对多的关系有 1 个，即商品和订单之间的关系，同品类的商品可以出现在不同的订单中，不同的订单也可以包含同一类型的商品，所以它们之间的关系是多对多。针对这种情况需要设计一个独立的表来表示，这种表一般称为中间表。

我们可以设计一个独立的订单详情表，来代表商品和订单之间的包含关系。这个表关联到 2 个实体，分别是订单、商品。所以，表中必须要包括这 2 个实体转换成的表的主键。除此之外，我们还要包括该关系自有的属性：商品数量，商品下单价格以及商品名称。

# 订单详情表
CREATE TABLE `order_detail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单详情编号',
  `order_id` bigint(29) DEFAULT NULL COMMENT '订单编号',
  `sku_id` bigint(20) DEFAULT NULL COMMENT 'sku_id',
  `sku_name` varchar(200) DEFAULT NULL COMMENT 'sku名称',
  `sku_num` varchar(200) DEFAULT NULL COMMENT '购买个数',
  `create_time` datetime DEFAULT NULL COMMENT '操作时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='订单明细表';
3. 通过外键来表达 1 对多的关系

在上面的表的设计中，我们可以用外键来表达 1 对多的关系。比如在商品评论表 sku_comments 中，我们分别把 user_id、sku_id 定义成外键，以使用下面的语句设置外键。

CONSTRAINT fk_comment_user FOREIGN KEY (user_id) REFERENCES user_info (id),

CONSTRAINT fk_comment_sku FOREIGN KEY (sku_id) REFERENCES sku_info (sku_id)

外键约束主要是在数据库层面上保证数据的一致性，但是因为插入和更新数据需要检查外键，理论上性能会有所下降，对性能是负面的影响。

实际的项目，不建议使用外键，一方面是降低开发的复杂度（有外键的话主从表类的操作必须先操作主表），另外是有外键在处理数据的时候非常麻烦。在电商平台，由于并发业务量比较大，所以一般不设置外键，以免影响数据库性能。

在应用层面做数据的一致性检查，本来就是一个正常的功能需求。如学生选课的场景，课程肯定不是输入的，而是通过下拉或查找等方式从系统中进行选取，就能够保证是合法的课程ID，因此就不需要靠数据库的外键来检查了。

  • 1.数据表的个数越少越好
  • 2.数据表中的字段个数越少越好
  • 3.数据表中联合主键的字段个数越少越好
  • 4.使用主键和外键越多越好

三少一多”原则的核心就是简单可复用。简单指的是用更少的表、更少的字段、更少的联合主键字段来完成数据表的设计。可复用则是通过主键、外键的使用来增强数据表之间的复用率。因为一个主键可以理解是一张表的代表。键设计得越多，证明它们之间的利用率越高。
注意：这个原则并不是绝对的，有时候我们需要牺牲数据的沉余度来换取数据处理的效率。

10还有一堆建议在pdf说了22页，真的牛逼

 11.powerdesigner的使用

158-PowerDesigner创建概念、物理数据模型_哔哩哔哩_bilibili

 


http://www.kler.cn/a/595813.html

相关文章：

  • C++修炼：内存管理
  • 最质量实践Docker
  • Github 2025-03-21Java开源项目日报Top9
  • 【Linux 维测专栏 2 -- Deadlock detection介绍】
  • 解决 C 盘空间不足，免费软件高效清理
  • 初级：控制流程面试题精讲
  • 《实战指南：基于Linux环境部署与应用Milvus向量数据库》
  • (四)---四元数的基础知识-(定义)-(乘法)-(逆)-(退化到二维复平面)
  • C++学习之QT中HTTP正则表达式
  • 从OSI七层网络模型角度了解CAN通信协议
  • Android HAL服务注册与获取服务
  • php 批量把数组中的日期时间转为时间戳
  • OpenGL ES -＞乒乓缓冲，计算只用两个帧缓冲对象(Frame Buffer Object)+叠加多个滤镜作用后的Bitmap
  • 算法2--两数相加
  • 系统思考—啤酒游戏经营决策沙盘模拟
  • selenium在实际的项目测试过程中的运用
  • Linux信号：一场内核与用户空间的暗战
  • PyCharm 使用指南：从安装到高效开发
  • EtherCAT转profinet网关集成汽车变速箱制造生产线自动化升级
  • HTTP代理IP技术详解及在Web开发中的应用