你好，我是朱晓峰。

在超市项目的数据库设计阶段，超市经营者交给我们一大堆Excel表格。我们需要把这些表格的数据都整理清楚，并且按照一定的规则存储起来，从而进行高效的管理。

比如，当时我们有这样一张进货表：

为了提高数据存储的效率，我们按照第三范式的原则进行拆分，这样就得到了4个表，分别是供货商表、进货单头表、进货单明细表和商品信息表。

供货商表：

进货单头表：

进货单明细表：

商品信息表：

其中，商品信息表、供货商表和进货单头表都满足第三范式的原则，进货单明细表虽然不满足第三范式的原则，但是满足第二范式的要求，而且保留的冗余字段也是基于业务优先的原则保留的。因此，超市经营者给我们提供的进货单表，经过我们的拆解，已经是存取效率最佳的方案了。在进货管理这个局部模块中，是最优的数据库设计方案。

但是，当我们按照这样的方式拆分一连串数据表时，却发现越拆越多，而且支离破碎。事实上，局部最优的表，不仅有可能存在进一步拆分的情况，还有可能会出现数据缺失。

毕竟，数据库设计是牵一发而动全身的。那有没有什么办法提前看到数据库的全貌呢？比如需要哪些数据表、数据表中应该有哪些字段，数据表与数据表之间有什么关系、通过什么字段进行连接，等等。这样我们才能进行整体的梳理和设计。

其实，ER模型就是一个这样的工具。ER模型也叫作实体关系模型，是用来描述现实生活中客观存在的事物、事物的属性，以及事物之间关系的一种数据模型。在开发基于数据库的信息系统的设计阶段，通常使用ER模型来描述信息需求和信息特性，帮助我们理清业务逻辑，从而设计出优秀的数据库。

今天，我还是借助实际案例，带你使用ER模型分析一下超市的业务流程，具体给你讲一讲怎么通过ER模型来理清数据库设计的思路，从而设计出优秀的数据库。

在使用之前，咱们得先知道ER模型里都包括啥。

ER模型包括哪些要素？

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

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

需要注意的是，有的时候，实体和属性不容易区分。比如刚刚商品信息表中的商品的单位，到底是实体还是属性呢？如果从进货的角度出发，单位是商品的属性，但是从超市信息系统的整体出发，单位可以看做一个实体。

那么，该如何区分实体和属性呢？

我给你提供一个原则：我们要从系统整体的角度出发去看，可以独立存在的是实体，不可再分的是属性。也就是说，属性不需要进一步描述，不能包含其他属性。

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

1对1：指实体之间的关系是一一对应的，比如个人与身份证信息之间的关系就是1对1的关系。一个人只能有一个身份证信息，一个身份证信息也只属于一个人。
1对多：指一边的实体通过关系，可以对应多个另外一边的实体。相反，另外一边的实体通过这个关系，则只能对应唯一的一边的实体。比如超市与超市里的收款机之间的从属关系，超市可以拥有多台收款机，但是每一条收款机只能从属于一个超市。
多对多：指关系两边的实体都可以通过关系对应多个对方的实体。比如在进货模块中，供货商与超市之间的关系就是多对多的关系，一个供货商可以给多个超市供货，一个超市也可以从多个供货商那里采购商品。

知道了这些要素，我们就可以给超市业务创建ER模型了，如下图所示：

我来简单解释一下这个图。

在这个图中，供货商和超市之间的供货关系，两边的数字都不是1，表示多对多的关系。同样，超市和顾客之间的零售关系，也是多对多的关系。

这个ER模型，包括了3个实体之间的2种关系：

超市从供货商那里采购商品；
超市把商品卖给顾客。

有了这个ER模型，我们就可以从整体上理解超市的业务了。但是，这里没有包含属性，这样就无法体现实体和关系的具体特征。现在，我们需要把属性加上，用椭圆来表示，这样我们得到的ER模型就更加完整了。

ER模型的细化

刚刚的ER模型展示了超市业务的框架，但是只包括了供货商、超市和顾客这三个实体，以及它们之间的关系，还不能对应到具体的表，以及表与表之间的关联。

因此，我们需要进一步去设计一下这个ER模型的各个局部，也就是细化下超市的具体业务流程，然后把它们综合到一起，形成一个完整的ER模型。这样可以帮助我们理清数据库的设计思路。

我们刚才的超市业务模型，包括了两个模块，分别是进货模块和销售模块。下面我们分别对这2个模块进行细化。

首先，我们来看一下超市业务中的进货模块的ER模型，整理一下其中包含哪些实体、哪些关系和哪些属性。

在我们的进货模块里，有5个实体：

供货商
商品
门店
仓库
员工

其中，供货商、商品和门店是强实体，因为它们不需要依赖其他任何实体。而仓库和员工是弱实体，因为它们虽然都可以独立存在，但是它们都依赖门店这个实体，因此都是弱实体。

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

供货商：名称、地址、电话、联系人。
商品：条码、名称、规格、单位、价格。
门店：编号、地址、电话、联系人。
仓库：编号、名称。
员工：工号、姓名、住址、电话、身份证号、职位。

这样细分之后，我们就可以重新设计进货模块了，ER模型如下：

需要注意的是，这里我是用粗框矩形表示弱实体，用粗框菱形，表示弱实体与它依赖的强实体之间的关系。

第二步，我们再分析一下零售模块。

经过分析，我们发现，在超市的业务流程中，零售业务包括普通零售和会员零售两种模式。普通零售包含的实体，包括门店、商品和收银款台；会员零售包含的实体，包括门店、商品、会员和收银款台。

这样我们就可以提炼出零售业务模块中的实体：

商品
门店
会员
收银款台

其中，商品和门店不依赖于任何其他实体，所以是强实体；会员和收银款台都依赖于门店，所以是弱实体。

有了实体之后，我们就可以确定实体的属性了。

商品：条码、名称、规格、单位、价格。
会员：卡号、发卡门店、名称、电话、身份证、地址、积分、储值。
门店：编号、地址、电话、联系人。
收银款台：编号、名称。

现在，我们就可以重新设计零售模块的ER模型了：

现在，我们把这两个图整合到一起，形成一个完整的ER模型：

如何把ER模型图转换成数据表？

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

我来介绍下转换的原则。

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

好了，下面我就结合前面的表格，给你具体讲解一下怎么运用这些转换的原则，把ER模型转换成具体的数据表，从而把抽象出来的数据模型，落实到具体的数据库设计当中。

一个实体转换成一个数据表

先来看一下强实体转换成数据表。

供货商实体转换成供货商表（demo.supplier）的代码如下所示：

mysql> CREATE TABLE demo.supplier
-> (
-> -- 我们给它添加一个与业务无关的字段“supplierid”为主键，并且设置自增约束。
-> supplierid INT PRIMARY KEY AUTO_INCREMENT,
-> suppliername TEXT,
-> address TEXT,
-> phone TEXT
-> );
Query OK, 0 rows affected (0.06 sec)

商品实体转换成商品表（demo.goodsmaster）：

mysql> CREATE TABLE demo.goodsmaster
-> (
-> --我们给商品信息表添加一个与业务无关的字段“itemnumber”为主键，采用手动赋值的方式，原因是可能存在多个门店录入新品，导致冲突的情况
-> itemnumber INT PRIMARY KEY,
-> barcode TEXT,
-> goodsname TEXT,
-> specification TEXT,
-> unit TEXT,
-> salesprice DECIMAL(10,2)
-> );
Query OK, 0 rows affected (0.07 sec)

门店实体转换成门店表（demo.branch）：

mysql> CREATE TABLE demo.branch
-> (
-> -- 增加一个与业务无关的字段为主键，并且设置自增约束
-> branchid INT PRIMARY KEY AUTO_INCREMENT,
-> branchno TEXT,
-> branchname TEXT,
-> address TEXT,
-> phone TEXT,
-> contacter TEXT
-> );
Query OK, 0 rows affected (0.06 sec)

下面我们再把弱实体转换成数据表。

仓库转换成仓库表（demo.stock）：

mysql> CREATE TABLE demo.stock
-> (
-> --添加与业务无关的自增约束字段为主键
-> stockid INT PRIMARY KEY AUTO_INCREMENT,
-> -- 仓库是弱实体，依赖于强实体门店表，所以要把门店表的主键字段包括进来，作为与门店表关联的外键
-> branchid INT NOT NULL,
-> stockno TEXT NOT NULL,
-> stockname TEXT NOT NULL,
-> -- 设置外键约束，与门店表关联
-> CONSTRAINT fk_stock_branch FOREIGN KEY (branchid) REFERENCES branch (branchid)
-> );
Query OK, 0 rows affected (0.07 sec)

收银款台实体转换成收银款台表（demo.cashier）：

mysql> CREATE TABLE demo.cashier
-> (
-> -- 添加与业务无关的自增字段为主键
-> cashierid INT PRIMARY KEY AUTO_INCREMENT,
-> -- 收银款台是弱实体，依赖于强实体门店表，所以要把门店表的主键字段包括进来，所为与门店表关联的外键
-> branchid INT NOT NULL,
-> cashierno TEXT NOT NULL,
-> cashiername TEXT NOT NULL,
-> -- 设置外键约束，与门店表关联
-> CONSTRAINT fk_cashier_branch FOREIGN KEY (branchid) REFERENCES branch (branchid)
-> );
Query OK, 0 rows affected (0.07 sec)

员工实体转换成员工表（demo.operator）：

mysql> CREATE TABLE demo.operator
-> (
-> -- 添加与业务无关的自增字段为主键
-> operatorid INT PRIMARY KEY AUTO_INCREMENT,
-> -- 员工是弱实体，依赖于强实体门店表，所以要把门店表的主键字段包括进来，所为与门店表关联的外键
-> branchid INT NOT NULL,
-> workno TEXT NOT NULL,
-> operatorname TEXT NOT NULL,
-> phone TEXT,
-> address TEXT,
-> pid TEXT,
-> duty TEXT,
-> -- 设置外键约束，与门店表关联
-> CONSTRAINT fk_operator_branch FOREIGN KEY (branchid) REFERENCES branch (branchid)
-> );
Query OK, 0 rows affected (0.11 sec)

会员实体转换成会员表（demo.membermaster）：

mysql> CREATE TABLE demo.membermaster
-> (
-> -- 添加与业务无关的自增字段为主键
-> memberid INT PRIMARY KEY,
-> -- 会员是弱实体，依赖于强实体门店表，所以要把门店表的主键字段包括进来，所为与门店表关联的外键
-> branchid INT NOT NULL,
-> cardno TEXT NOT NULL,
-> membername TEXT,
-> address TEXT,
-> phone TEXT,
-> pid TEXT,
-> -- 设置默认约束，积分默认为0
-> memberpoints DECIMAL(10,1) NOT NULL DEFAULT 0,
-> -- 设置默认约束，储值默认为0
-> memberdeposit DECIMAL(10,2) NOT NULL DEFAULT 0,
-> -- 设置外键约束，与门店表关联
-> CONSTRAINT fk_member_branch FOREIGN KEY (branchid) REFERENCES branch (branchid)
-> );
Query OK, 0 rows affected (0.07 sec)

一个多对多的关系转换成一个数据表

这个ER模型中的多对多的关系有2个，分别是零售和进货。我们分别设计一个独立的表来表示，这种表一般称为中间表。

我们可以设计一个独立的进货单表，来代表进货关系。这个表关联到4个实体，分别是供货商、商品、仓库、员工，所以，表中必须要包括这4个实体转换成的表的主键。除此之外，我们还要包括进货关系自有的属性：进货时间、供货数量和进货价格。

按照数据表设计的第三范式的要求和业务优先的原则，我们把这个进货单表拆分成2个表，分别是进货单头表和进货单明细表：

CREATE TABLE demo.importhead
(
importid INT PRIMARY KEY,   -- 添加与业务无关的字段为主键 
listnumber TEXT NOT NULL,
supplierid INT NOT NULL,    -- 供货商表的主键，反映了参与进货关系的供货商信息
stockid INT NOT NULL,       -- 仓库表的主键，反映了参与进货关系的仓库信息
operatorid INT NOT NULL,    -- 员工表的主键，反映了参与进货关系的员工信息
recordingdate DATETIME NOT NULL,
totalquantity DECIMAL(10,3) NOT NULL DEFAULT 0,
totalvalue DECIMAL(10,3) NOT NULL DEFAULT 0,
CONSTRAINT fk_importhead_supplier FOREIGN KEY (supplierid) REFERENCES supplier (supplierid),
CONSTRAINT fk_transactionhead_member FOREIGN KEY (memberid) REFERENCES membermaster (memberid),
CONSTRAINT fk_importhead_operator FOREIGN KEY (operatorid) REFERENCES operator (operatorid)
);
CREATE TABLE demo.importdetails
(
importid INT,
itemnumber INT,              -- 商品表的主键，反映了参与进货关系的商品信息
importquantity DECIMAL(10,3) NOT NULL DEFAULT 0,
importprice DECIMAL(10,2) NOT NULL DEFAULT 0,
importvalue DECIMAL(10,2) NOT NULL DEFAULT 0,
PRIMARY KEY (importid,itemnumber),
CONSTRAINT fk_importdetails_goodsmaster FOREIGN KEY (itemnumber) REFERENCES goodsmaster (itemnumber)
);

对于零售关系，我们可以设计一张流水单来表示。

这个表关联4个实体，分别是收银款台、商品、会员和员工。所以，表中也必须要包括这4个实体转换成的表的主键。除此之外，表中还要包括零售关系自有的属性：交易时间、数量、价格等。

按照数据表设计的第三范式的要求，我们把这个流水单表拆分成2个表，分别是流水单头表和流水单明细表：

CREATE TABLE demo.transactionhead
(
transactionid INT PRIMARY KEY,   -- 添加与业务无关的字段为主键
transactionno TEXT NOT NULL,
cashierid INT NOT NULL,          -- 收款机表的主键，反映了参与零售关系的收款机信息
memberid INT,                    -- 会员表的主键，反映了参与零售关系的会员的信息
operatorid INT NOT NULL,         -- 员工表的主键，反映了参与零售关系的员工信息
transdate DATETIME NOT NULL,
CONSTRAINT fk_transactionhead_cashier FOREIGN KEY (cashierid) REFERENCES cashier (cashierid),
CONSTRAINT fk_transactionhead_member FOREIGN KEY (memberid) REFERENCES member (memberid),
CONSTRAINT fk_transactionhead_operator FOREIGN KEY (operatorid) REFERENCES operator (operatorid)
);
CREATE TABLE demo.transactiondetails
(
transactionid INT,
itemnumber INT,                 -- 商品表的主键，反映了参与零售关系的商品信息
quantity DECIMAL(10,3) NOT NULL DEFAULT 0,
price DECIMAL(10,2) NOT NULL DEFAULT 0,
salesvalue DECIMAL(10,2) NOT NULL DEFAULT 0,
PRIMARY KEY (transactionid,itemnumber),
CONSTRAINT fk_transactiondetails_goodsmaster FOREIGN KEY (itemnumber) REFERENCES goodsmaster (itemnumber)
);

通过外键来表达1对多的关系

在上面的表的设计中，我们已经完成了用外键来表达1对多的关系。比如：在流水单头表中，我们分别把cashierid、memberid和operatorid定义成了外键：

CONSTRAINT fk_transactionhead_cashier FOREIGN KEY (cashierid) REFERENCES cashier (cashierid),
CONSTRAINT fk_transactionhead_member FOREIGN KEY (memberid) REFERENCES membermaster (memberid),
CONSTRAINT fk_transactionhead_operator FOREIGN KEY (operatorid) REFERENCES operator (operatorid)

在流水单明细表中，我们把商品编号定义成了外键：

CONSTRAINT fk_transactiondetails_goodsmaster FOREIGN KEY (itemnumber) REFERENCES goodsmaster (itemnumber)

把属性转换成表的字段

在刚刚的设计中，我们也完成了把属性都转换成了表的字段，比如把商品属性（包括条码、名称、规格、单位、价格）转换成了商品信息表中的字段：

mysql> CREATE TABLE demo.goodsmaster
-> (
-> --我们给商品信息表添加一个与业务无关的字段“itemnumber”为主键，采用手动赋值的方式，原因是可能存在多个门店录入新品，导致冲突的情况
-> itemnumber INT PRIMARY KEY,
-> barcode TEXT,                -- 条码属性
-> goodsname TEXT,              -- 名称属性
-> specification TEXT,          -- 规格属性
-> unit TEXT,                   -- 单位属性
-> salesprice DECIMAL(10,2)     -- 价格属性
-> );
Query OK, 0 rows affected (0.07 sec)

这样，我们就完成了ER模型到MySQL数据表的转换。

到这里，我们通过创建超市项目业务流程的ER模型，再把ER模型转换成具体的数据表的过程，完成了利用ER模型设计超市项目数据库的工作。

其实，任何一个基于数据库的应用项目，都可以通过这种先建立ER模型，再转换成数据表的方式，完成数据库的设计工作。ER模型是一种工具。创建ER模型不是目的，目的是把业务逻辑梳理清楚，设计出优秀的数据库。我建议你不是为了建模而建模，要利用创建ER模型的过程来整理思路，这样创建ER模型才有意义。

总结

今天，我们学习了通过绘制ER模型图理清业务逻辑，以及怎么把ER模型转换成MySQL数据表，最终完成项目数据库设计。

这节课的知识点比较多，我用一张图来帮你回顾下重点。

最后，我还想再提醒你一下，ER模型看起来比较麻烦，但是对我们把控项目整体非常重要。如果你只是开发一个小应用，或许简单设计几个表够用了，一旦要设计有一定规模的应用，在项目的初始阶段，建立完整的ER模型就非常关键了。开发应用项目的实质，其实就是建模。胸中有丘壑，才能下笔如有神。道理其实是一样的。

思考题

超市经营者每个月都要进行库房盘点，也就是在一个月的最后一天的营业结束之后，所有的员工一起把库房里的货品都数一遍，然后跟电脑上的库存比对，查看库存损耗。

我想请你思考一下，在这个业务模块中，涉及了哪些实体、属性和关系？另外，请你设计一下ER模型，通过它来整理一下数据库设计的思路。

欢迎在留言区写下你的思考和答案，我们一起交流讨论。如果你觉得今天的内容对你有所帮助，也欢迎你把它分享给你的朋友或同事，我们下节课见。