做数据库相关工作这些年我面试过不少人也带过不少新人发现一个很有意思的现象很多人张口就能背出三大范式写SQL也溜得很但一让他画E-R图或者根据需求设计表结构就开始露怯。要么实体找不全要么联系理不清画出来的图自己都解释不通。E-R图这个东西说难不难说简单也不简单。它就是数据库设计的“图纸”——你还没开始建表还没写一行SQL先用一张图把业务世界里有哪些“东西”、这些“东西”之间什么关系、各自带什么“属性”给描述清楚。图纸画错了后面建的表、写的查询、做的报表全都会跟着歪。所以“数据库-E-R图练习”这个看似基础的题目其实是整个数据库设计能力的分水岭。这篇博文我就拿“电商购物系统”这个最经典的练手场景完整走一遍从需求分析、实体识别、联系梳理到最终画出E-R图并转成建表SQL的全过程。不管你是在准备数据库课程设计还是在复习面试题或者单纯想把手上的烂表结构重构一下这篇内容都能给你一套可以直接照搬的思考方法和实操步骤。1. 画E-R图前先搞懂这几件事1.1 E-R图到底是什么它解决什么问题E-R图全称是实体-联系图Entity-Relationship Diagram1976年由Peter Chen提出到现在快五十年了依然是数据库设计领域最基础也最实用的建模工具。它解决的核心问题只有一个把现实世界的业务规则翻译成计算机能理解的数据结构。打个比方。你开一家小卖部脑子里很清楚有顾客来买东西有商品摆在货架上顾客买了什么、付了多少钱这些你心里都有数。但要把这套生意搬进电脑系统里你就得先把“顾客”“商品”“订单”“付款”这些概念拆清楚。什么是实体Entity顾客、商品就是实体它们是业务中独立存在的“事物”。什么是属性Attribute顾客有姓名、电话商品有价格、库存这些描述实体的特征就是属性。什么是联系Relationship顾客“下”订单订单“包含”商品这个“下”和“包含”就是实体之间的联系方式。E-R图就是把这些要素用统一的图形符号画出来矩形表示实体椭圆表示属性菱形表示联系线段把三者连起来。一张图画完整个业务的数据结构就一目了然了开发、测试、产品都能对着同一张图说话。1.2 一张E-R图为什么能决定数据库设计的成败我见过太多“先建表、后补需求”的项目结果表建了二三十张字段加了几百个上线跑了一个月发现统计报表根本写不出来。为什么因为底层的数据关系从一开始就是乱的。E-R图的价值恰恰在于它逼着你在动手建表之前先把业务逻辑想清楚。举个例子。一个简单的电商系统如果没画E-R图就直接建表你很可能建一张“订单表”里面塞上用户姓名、用户电话、收货地址、商品名称、商品单价、商品数量、订单总价……所有字段堆在一起。看起来挺省事一张表搞定所有查询。但真上线了你就会发现用户改了手机号订单里的手机号却还是旧的商品改了个价格历史订单的金额也变了想统计“这个用户一共买了多少种商品”SQL写得像天书。如果你先画了E-R图就一定会发现“用户”和“订单”是两个实体“订单”和“商品”是多对多的联系自然就会拆成用户表、订单表、商品表、订单明细表四张表。这就是E-R图的威力它用一张图把规范化设计的压力前置到了设计阶段而不是等你写了几十条烂SQL之后再来哭。1.3 画图前的需求分析先有需求后有图很多人拿到题目就急着画矩形这是最大的误区。E-R图是对需求的图形化表达需求不清图画得再漂亮也是空中楼阁。做需求分析最简单有效的方法就是把自己当成系统的使用者把核心业务流程走一遍。以电商购物系统为例你闭上眼睛想象一次完整的购物经历你注册一个账号登录系统。浏览商品列表点进某个商品详情页看看。把商品加入购物车。结算下单填写收货地址。支付订单。商家发货你收到货。你对商品进行评价。这七个步骤走完业务的主干就有了。每走一步你就问自己三个问题这个环节里有哪几个“东西”在参与每个“东西”有哪些关键信息要记录“东西”和“东西”之间是什么关系把这三个问题的答案记下来实体、属性、联系的基本素材就齐了。后面画的每一个矩形、每一个菱形都是从这个业务流程里“长”出来的而不是凭空想出来的。2. 实体、属性、联系画图的核心三要素2.1 怎么从业务描述里“抓”出实体实体识别的核心原则是实体是业务中独立存在、需要被记录信息的事物。判断标准很简单——如果这个“东西”消失或者不存在了业务还成立吗如果业务必需且它有自己独立的信息要记录那它就是实体。实操中我习惯用“名词划线法”。把需求文档或业务流程描述里所有的名词都圈出来然后逐个过滤哪些是实打实的业务对象哪些只是对象的属性哪些只是修饰词。比如需求文档里写“用户在平台注册后可以浏览商品将商品加入购物车生成订单并完成支付随后商品发货用户确认收货后可以对订单进行评价。”圈出来的名词有用户、平台、商品、购物车、订单、支付、收货、评价。现在开始过滤“平台”是系统本身不是系统里要管理的对象排除。“支付”更像是订单的一个行为或状态不是独立实体但有支付单这个概念的话另说。“收货”是发货后的动作通常归类为订单状态不单列实体。“评价”是用户对商品或订单的反馈可以做成独立的“评价”实体也可以做成订单的附属信息。考虑到一个订单一个评价而且评价内容、评分、评价时间这些信息都挺独立我倾向于单列一个“评价”实体。过完这轮过滤核心实体就浮出来了用户、商品、订单、购物车项、评价。注意购物车——很多人纠结它算不算实体。我的建议是购物车本身不是必须单独成实体购物车里的每一行用户、商品、数量、选中状态才是需要记录的所以“购物车项”是一个实体。如果你用Redis之类的方式做购物车那它不进数据库另说但如果入库就按照“购物车项”来设计。2.2 属性和实体怎么区分什么时候该拆成实体属性是描述实体的特征但“特征”这个词实操起来会有边界模糊的时候。我总结了两条判断规则第一属性必须是原子性的不可再分。比如“用户地址”如果只需要存一个字符串那是属性但如果你要按省份、城市、详细地址分别统计和筛选那就该拆开或者单独建一个“地址”实体一个用户有多个地址联系就出来了。再比如“商品分类”如果只是存个分类名那是属性但如果分类分两级甚至三级还带图标、排序、描述等信息就该建“分类”实体商品和分类之间形成多对一或树形关联。第二一个属性值变化时是否会影响其他实体的历史数据。这条是判断“要不要把属性升格为实体/关联”的关键。还是商品和订单的例子订单里存了商品当时的快照信息商品名、单价这就不是“商品”实体的属性而是“订单明细”这个联系本身的属性。为什么因为商品的价格会变订单里记录的是成交那一刻的价格它跟当前商品表里的价格是两回事。这个细节如果你没想清楚以后做订单统计一定会对不上账。2.3 三种联系类型1对1、1对多、多对多实体之间的关系就三种一对一、一对多、多对多。这个知识点看着简单实际画图时最容易出错。一对一1:1A的一个实例最多对应B的一个实例反之亦然。比如“用户”和“用户详情”身份证号、实名认证信息一个用户只有一份详情一份详情只属于一个用户。在数据库里你甚至可以把它们合并成一张表所以一对一联系在设计中往往会被优化掉。一对多1:NA的一个实例对应B的多个实例反之B的一个实例只能对应A的一个实例。这是最最常见的联系类型。比如“用户”和“订单”——一个用户可以有多个订单一个订单只属于一个用户。“分类”和“商品”——一个分类下有很多商品一个商品只属于一个分类。多对多M:NA的一个实例对应B的多个实例反之亦然。“商品”和“订单”就是典型——一个订单包含多个商品一个商品也出现在多个订单里。多对多联系在E-R图上可以直接用菱形表示但到建表阶段必须拆成一张中间表也就是订单明细表。这是E-R图转关系模式的核心技巧后面细说。判断联系类型时我常用一个“双向问句话术”随便拿一个A它能对应几个B再随便拿一个B它能对应几个A两边各自回答“一”还是“多”组合起来就是联系类型。比如“用户”和“订单”一个用户能下多个订单一→多一个订单只能属于一个用户多→一所以是一对多。2.4 联系的属性很多人忽略的关键细节实体有属性联系也有属性。这是个非常容易漏掉的点但它恰恰是E-R图设计中最见功力、最能拉开水平差距的地方。举个最典型的例子“订单”和“商品”之间的多对多联系我之前说了要拆成“订单明细”。那么“订单明细”里有什么有商品数量、有成交单价、有是否评价。这个“商品数量”“成交单价”是订单的属性吗不是因为一个订单包含多个商品每个商品的数量和价格都不同这些信息无法直接挂在订单头上。是商品的属性吗也不是因为商品表里存的是当前价格不是每次成交的价格。所以数量、成交价这类信息属于“订单-商品”这个联系本身。在E-R图里你可以把这些属性画在连接“订单”和“商品”的菱形旁边。到转关系模式时这些属性自然就落到“订单明细表”里了。再举个例子。“学生”和“课程”是多对多联系一个学生选多门课一门课有多个学生选那么“选课成绩”是属性还是联系属性显然成绩既不能只挂在学生上学生有多门课每门课成绩不同也不能只挂在课程上课程有多个学生每个学生成绩不同它必须挂在“选课”这个联系上。掌握了这个思路你画E-R图时就不会再把字段塞错位置。3. 完整实操给电商购物系统画E-R图3.1 需求分析购物系统需要哪些功能既然题目是经典的“对电商购物系统做需求分析并画出E-R图”我们把需求再细化一点基于这个需求来画图。为了练习我们适度增加一些复杂度让它更接近真实业务用户注册登录维护个人基本信息包括用户名、密码、手机号、邮箱、收货地址。一个用户可以有多个收货地址。商品按分类组织分类分两级一级分类如“数码家电”二级分类如“手机”。商品属于最末级分类。用户浏览商品将商品加入购物车。购物车中的每一条记录包含用户、商品、数量、勾选状态。用户结算下单。一个订单对应一个收货地址包含多个商品项每个商品项记录商品快照、数量、成交单价。订单有状态待付款、已付款、已发货、已完成、已取消。付款生成支付流水记录支付方式、支付金额、支付时间。用户确认收货后可以对订单中的每个商品进行评价评价包含评分、内容和图片。上面这些需求已经足够支撑一张中等复杂度的E-R图练习了。从这个需求描述里走一遍“名词划线法”实体基本就齐了用户、收货地址、商品分类、商品、购物车项、订单、订单明细、支付流水、评价。一共9个实体这个规模用于课程设计或面试手绘图已经非常合格。3.2 识别实体与属性逐个盘点清单有了实体清单接下来给每个实体配上属性同时圈出主键。主键的选择原则稳定、唯一、不含业务语义——所以实际开发中大家偏爱自增ID或雪花ID不太建议把手机号、身份证号当主键一改就崩还有隐私问题。实体的属性列表如下用户用户ID主键、用户名、登录密码、手机号、邮箱、注册时间、账号状态。收货地址地址ID主键、用户ID外键、收货人姓名、联系电话、所在省份、城市、详细地址、是否默认地址。商品分类分类ID主键、父分类ID一级分类此字段为空、分类名称、排序值。这里是把两级分类放到一张表里通过“父分类ID”自关联。这是很常见的树形表设计。商品商品ID主键、分类ID外键、商品名称、商品主图、商品描述、当前单价、总库存、上架状态、创建时间。购物车项购物车项ID主键、用户ID外键、商品ID外键、数量、勾选状态、加入时间。订单订单号主键、用户ID外键、收货地址ID外键、订单状态、下单时间、支付时间、发货时间、完成时间。注意订单金额不建议直接存一个总金额字段可以通过明细算出来但如果系统对性能要求高冗余一个总金额也很常见面试时能说出这个考量是加分项。订单明细明细ID主键、订单号外键、商品ID外键、商品名称快照、商品图片快照、成交单价、购买数量。支付流水支付流水号主键、订单号外键、用户ID外键、支付方式、支付金额、支付时间、支付状态。评价评价ID主键、订单明细ID外键、用户ID外键、商品ID外键、评分1-5星、评价内容、评价图片、评价时间。这里有个细节要说明为什么评价要关联商品ID因为评价本质上是对“某次购买中的某个商品”进行反馈关联订单明细ID可以定位到具体是哪一单哪一件商品关联商品ID是为了方便商品详情页直接聚合展示所有评论。有的设计里这两者取一个就行但电商系统里商品详情页和订单页都要展示评价所以我习惯两个都留。3.3 确定联系与联系类型把图画出来实体和属性都清了最后一步就是连线。逐个分析两两实体之间的联系画出菱形标注1和N或M和N。先列出所有联系用户-收货地址一个用户有多个收货地址一个地址只属于一个用户。1:N。商品分类-商品分类自关联一个一级分类下有多个二级分类一个二级分类属于一个一级分类。1:N。二级分类下有多个商品。商品分类-商品一个二级分类下有多个商品一个商品只属于一个分类。1:N。用户-购物车项一个用户有多条购物车项一条购物车项只属于一个用户。1:N。商品-购物车项一个商品可以出现在多个用户的购物车里一条购物车项只对应一个商品。1:N。所以“用户-商品-购物车项”实际是两条1:N汇合到购物车项这个实体上。用户-订单一个用户有多个订单一个订单只属于一个用户。1:N。订单-收货地址一个订单对应一个收货地址一个地址可以被多个订单使用。N:1。这里要特别注意订单关联的是“下单那一刻”的地址快照还是地址ID我的做法是订单表存地址ID同时把收货人、电话、地址冗余一份到订单表因为用户的地址后来可能修改历史订单需要保留当时的地址现场。订单-订单明细一个订单有多个明细一个明细只属于一个订单。1:N。商品-订单明细一个商品可以出现在多个订单明细中一个明细只对应一个商品。1:N。订单-支付流水一个订单可能有多条支付流水比如支付失败重试、部分退款一条流水只属于一个订单。1:N。订单明细-评价一条订单明细对应一个评价也可能没有一个评价只对应一条明细。1:1。这里按照业务规则“一个商品一条评价”即可如果你允许同一个商品评价多次那就要改成多个评价了。把所有实体和联系画到一张图上就是完整的电商购物系统E-R图。画图工具推荐draw.io、ProcessOn、Visio都行手绘也没问题——重要的是关系理清了画出来只是表达问题。3.4 从E-R图导出关系模式建表SQLE-R图画完数据库设计的重头戏来了把它翻译成关系模式也就是表结构。转换规则三条每一个实体转成一张表主键就是实体的主键。1:N联系把“1”那一侧的主键作为外键加到“N”那一侧的表中。比如“用户-订单”就把用户ID加到订单表里。M:N联系单独建一张中间表把两侧的主键都拿过来作为外键联合起来还能当复合主键。比如“商品-订单”就是订单明细表。按照这些规则上面9个实体的建表SQL长这样我用MySQL做示例略去外键约束的繁琐声明重点是结构CREATE TABLE user ( user_id BIGINT PRIMARY KEY, username VARCHAR(50) NOT NULL, password_hash VARCHAR(100) NOT NULL, phone VARCHAR(20), email VARCHAR(100), register_time DATETIME, status TINYINT ); CREATE TABLE user_address ( address_id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, receiver_name VARCHAR(50), receiver_phone VARCHAR(20), province VARCHAR(50), city VARCHAR(50), detail_address VARCHAR(200), is_default TINYINT DEFAULT 0 ); CREATE TABLE category ( category_id BIGINT PRIMARY KEY, parent_id BIGINT DEFAULT NULL, category_name VARCHAR(50) NOT NULL, sort_order INT DEFAULT 0 ); CREATE TABLE product ( product_id BIGINT PRIMARY KEY, category_id BIGINT NOT NULL, product_name VARCHAR(200) NOT NULL, main_image VARCHAR(500), description TEXT, price DECIMAL(10,2) NOT NULL, stock INT DEFAULT 0, is_on_sale TINYINT DEFAULT 1, create_time DATETIME ); CREATE TABLE cart_item ( cart_item_id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, product_id BIGINT NOT NULL, quantity INT NOT NULL DEFAULT 1, is_checked TINYINT DEFAULT 1, add_time DATETIME ); CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, address_id BIGINT, order_status TINYINT NOT NULL, order_time DATETIME, pay_time DATETIME, ship_time DATETIME, finish_time DATETIME, receiver_name VARCHAR(50), receiver_phone VARCHAR(20), receiver_address VARCHAR(200) ); CREATE TABLE order_item ( item_id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, product_id BIGINT NOT NULL, product_name_snapshot VARCHAR(200), product_image_snapshot VARCHAR(500), deal_price DECIMAL(10,2), quantity INT ); CREATE TABLE payment ( payment_id BIGINT PRIMARY KEY, order_id BIGINT NOT NULL, user_id BIGINT NOT NULL, pay_method VARCHAR(20), pay_amount DECIMAL(10,2), pay_time DATETIME, pay_status TINYINT ); CREATE TABLE review ( review_id BIGINT PRIMARY KEY, order_item_id BIGINT NOT NULL, user_id BIGINT NOT NULL, product_id BIGINT NOT NULL, rating TINYINT NOT NULL, content TEXT, images VARCHAR(1000), review_time DATETIME );有用的细节我多两句订单表里的receiver_name等字段就是地址“快照”这就是我在3.2里强调的“历史现场保留”的落地写法。“商品-订单”的多对多关系没有直接体现在某一张表里但order_item表同时持有order_id和product_id中间表的效果就出来了。4. 常见问题与排查技巧实录4.1 实体找多了或找漏了怎么办练E-R图最经典的翻车现场就是实体数量不对。有些同学恨不得把“手机号”也画成实体有些则把“收货地址”直接揉进“用户”实体里了事。前者是找多了后者是找少了。找多了的本质是混淆了属性和实体。记住根源性判断方法如果某信息离开所属者之后没有独立存在的业务意义那它就是属性不是实体。比如“手机号”不可能脱离用户单独出现在业务里所以它是用户属性“收货地址”可以脱离用户存在吗不行但它有多个“实例”而且订单要引用历史地址现场所以它成了实体。找漏了则相反往往是把“联系属性”藏在了实体属性里。典型错误就是订单表里加一列“商品名”——一看到这种设计多半就是漏掉了“订单明细”这个表/实体。排查方法从我自己的经验看很有效画完图后对着每个菱形问一句这个联系本身需要记录哪些信息才能支撑业务如果有信息只能挂在联系上那你就该新建一个实体如订单明细、选课成绩表来承接它。4.2 多对多联系要不要拆成关联表这是E-R图转关系模式时绕不开的决策。我的回答非常明确必须拆而且拆出来的中间表通常会“升级”成一个正式的实体。还是拿“商品”和“订单”举例。表面上看它们是多对多拆出“订单明细”就够了。但你再仔细想“订单明细”有没有自己的属性有数量、成交价、评价状态。当它有了自己的属性之后它就不再是那个卑微的“中间表”了而是一个有主键、有业务含义、有独立查询需求的实体了。同理“用户”和“商品”之间也是多对多一个用户买多种商品一种商品被多个用户买如果不加约束你会拆出“购买记录”表。但如果你把“购物车”加进来“用户-购物车-商品”就拆成了两条1:N购物车项成了独立实体。所以拆不拆、怎么拆取决于业务上这个关联本身是否承载额外的信息。承载了就升格为实体只是表达关系就建个轻量中间表。这个判断能力就是设计经验的体现。4.3 主键怎么选自增、业务字段还是唯一标识E-R图上给定主键很简单但落到真实系统里选错主键会坑死人。三个原则分享给大家。第一能用自增ID就用自增ID别拿业务字段硬扛。有同学喜欢拿手机号当用户ID很爽但哪天用户注销换号或者同一手机号注册两个账号全表外键、日志、流水全乱了。业务字段天然带变数当唯一标识不稳定。第二多对多中间表不要额外搞一个自增ID用复合主键更合适。比如订单明细表用order_id, product_id做联合主键天然锁定了“一个订单里同一商品只出现一次”的业务规则。你要是多加一个自增ID反而让程序猿有机会往里插两行相同商品脏数据就是这么来的。第三分布式场景别用自增ID用雪花ID或UUID。这个对课程设计来说可能超纲了但面试问到“表很大要分库分表”的时候你能答出“自增ID在分布式下无法全局唯一所以用雪花算法”会是明显的加分项。E-R图练习本身不涉及这些但主键思路上提前有这个意识设计出来的表会健壮很多。4.4 E-R图自查清单画完对照一遍画完E-R图千万别急着提交先按我的清单过一遍每个实体都有主键吗主键业务语义是否纯净每个联系的两端都标注了基数1、N、M吗很多图不标基数看了等于白看。所有M:N联系都计划好中间表了吗联系属性有没有归宿比如数量、成绩、时间这类有地方落脚吗有没有信息被重复存储且无法解释的比如订单表和商品表都存了单价——如果是有意做快照就能解释如果是无意冗余就是设计缺陷。自关联或层级结构处理了吗比如商品分类的两级关系。从用户角度走一遍核心流程每个环节是否都有对应的实体和联系这套清单我面试的时候也爱让候选人当场对着自己的图走一遍。能走通的人说明图是真懂了走不通的人基本就是背了模板换个业务场景马上露馅。E-R图的练习本质上练的不是画图而是抽象思维能力。把一团乱麻的业务需求拆成一个个清晰的实体、一组组明确的关系这个能力在任何数据相关的岗位上都是硬通货。我自己带项目这么多年最深的体会就是凡是前期花时间把E-R图琢磨透的项目后面建表、写接口、出报表都顺风顺水凡是图省事跳过这一步的项目后面无一例外都要返工。最后再分享一个练习方法。别光在网上搜现成的E-R图看那跟看别人健身视频一样看了不会长肌肉。找个你熟悉的场景比如图书馆借书、医院挂号、学校的选课系统关掉教程自己从需求分析开始一步步画出实体、属性和联系再转成建表SQL。画完找懂行的朋友帮你过一遍或者对照自查清单检查一遍。练上五六个案例你再看任何业务需求脑子里会自动浮现出一张表关系图那种感觉就是真的入门了。