E-R 图向关系模型的转换是数据库设计里最容易被轻视、也最不能出错的一环。很多人画 E-R 图的时候思路清晰方框菱形连线一气呵成可一到落表就卡住了多对多联系到底要不要单独建表弱实体的主键怎么拼用户和商品之间那条线该不该变成一个字段。我在做过十几个业务系统之后越来越觉得 E-R 图只是想清楚了而 E-R 图向关系模型的转换才是写下来中间这一步的取舍质量直接决定了后面三年你要不要反复改表。这篇内容面向的是正在做课程设计的学生、刚接手从零建库的后端开发、以及需要评审数据库设计的架构同学。我会把转换规则拆到能被验证的程度再拿一个完整的电商购物系统从头走一遍从需求分析、实体抽取、E-R 图设计到逐条套用规则生成表结构最后给出可直接执行的建表语句、约束设计和索引建议。整个过程里我会说清楚每一步为什么这么选以及那些文档里不写、但线上一定会遇到的问题。1. 先搞清楚一件事E-R 图和关系模型到底差在哪1.1 概念世界和关系世界是两套语言E-R 图属于概念数据模型它是给人看的。方框代表实体椭圆代表属性菱形代表联系线条上的 1、N、M 标注基数。这套语言的优势在于表达力强能容纳现实世界的模糊性——比如一个订单包含多个商品这在图上就是一条线加一个 N简单直接谁看都懂。但它不能直接存进数据库因为关系型数据库只认一个东西二维表。关系模型属于逻辑数据模型它的表达能力被严格限制在三件事上表、列、行。所有的语义都必须压扁成某个表里的某个字段引用了另一个表的主键。这就带来一个根本矛盾——E-R 图里那种线和菱形的独立结构在关系模型里没有对等物。联系的语义要么被吸收进某张表变成一个外键列要么被迫独立成一张只有外键的表。转换过程本质上就是在做这种语义降维而降维一定会伴随信息形式的改变。我习惯把这一步类比成把一栋立体建筑投影成三视图。投影之后你依然能还原建筑但每个视图单独看都不完整必须三个视图配合。同理转换后的表结构要能完整还原 E-R 图所描述的业务约束靠的不是某一张表而是表之间的外键关系和约束共同构成的整体。1.2 转换链条上最容易丢的三类信息在动手写表之前先明确哪些东西最容易在转换过程中丢掉这比记规则更重要。第一类是基数约束的细节。E-R 图上写的是 1:N但这个1是必须存在还是可以不存在N这一端是至少一条还是可以零条这些叫参与度约束图上的线条往往标不全。比如订单和支付记录之间一个订单可能还没支付也可能有多条支付尝试记录如果只写 1:N转换出来的外键字段可空性就没法确定。第二类是属性本身的语义弱化。E-R 图里复合属性比如收货地址可以拆成省、市、区、详细地址在图上是一个带分支的椭圆转换时如果不拆开全部塞进一个字段后面想按城市统计订单量就只能做字符串匹配性能和准确度都会崩。第三类是联系上的属性。这条最隐蔽——M:N 联系本身可以带属性比如用户和商品之间的收藏联系带一个收藏时间或者订单和商品之间的包含联系带一个购买数量和成交单价。很多人转换时只顾着建中间表的主键把联系上的属性忘了结果数量只能从别处反推反推出来的值还可能因为商品改价而失真。提示转换完成之后我习惯做一次反向验证——拿着表结构去回答业务问题比如某个用户上周买过哪些品类的商品。如果这个问题需要绕三张表还没有明确的连接路径说明转换时丢了联系。2. 七条转换规则从图形元素到表结构的一一映射2.1 实体、弱实体与属性的拆解强实体的转换最直接一个实体对应一张表实体的每个简单属性对应表的一列实体的标识符对应表的主键。这里没什么争议真正的坑在于属性类型的判定。简单属性直接建列。复合属性要拆成多个列比如地址拆成 province、city、district、detail 四列而不是一列 address 存全串。理由很实际拆开之后可以单独给 city 建索引做分组统计合并成一个字符串就只能全表扫描。多值属性必须独立成表比如一个用户可以有多个手机号这时手机号不是用户表的列而是一张 user_phone 表结构是 (user_id, phone)主键是两者组合。派生属性一般不建列比如年龄可以由出生日期算出来订单总金额可以由明细汇总得到存下来反而要处理同步更新的一致性问题。弱实体的处理需要多一步。弱实体没有自己的完整标识符它的标识依赖某个强实体存在比如订单明细依赖订单存在明细编号只在同一个订单内唯一。转换时弱实体的主键是自身部分键 所依赖强实体的主键的组合。订单明细的主键就是 (order_id, line_no)其中 line_no 是明细在订单内的序号。同时 order_id 还兼作外键并要加上级联删除约束——订单删了明细必须跟着删否则就是孤儿数据。2.2 二元联系的三种基数处理这是转换的核心区也是最容易记混的地方。我把三种基数的处理方式拉成一张表方便对照。联系类型转换策略外键放在哪是否需要新表1:1合并到任一端通常合并到参与度更高的一端被合并端的表一般不需要1:N把 1 端的主键放到 N 端作外键N 端的表不需要M:N独立建一张联系表两个外键都在新表需要1:1 联系的处理要点是往哪边合并。判断依据是可选性把外键放在可选的一边可以允许 NULL如果放在必选的一边就会出现必须插入另一条记录才能插入当前记录的鸡生蛋问题。比如用户和实名认证信息是一对一但用户可能还没做实名那么 user_id 应该放在认证信息表里而不是反过来。1:N 联系几乎是关系模型里最舒服的结构外键天然表达了多这一端的归属。这里有一个细节要注意外键列要不要建索引答案是只要这个外键会被用来做连接查询或者按父表过滤就应该建。MySQL 的 InnoDB 在建外键约束时会自动为外键列创建索引但如果只是逻辑外键不在数据库层加约束就得自己手动建否则 N 端表的按父查询会走全表扫描。M:N 联系必须独立建表这张表通常叫连接表或中间表。它的主键有几种选择用两端主键组合做主键、用代理键做主键加唯一约束、或者干脆不要主键只要两个索引。我的经验是如果这张表上有业务属性比如数量、时间用代理键做主键更利于后续扩展如果纯粹是关系映射用组合主键更省空间也更能防止重复插入。2.3 自反联系、多元联系与泛化的处理自反联系是同一个实体内部的关系比如员工和上级经理、商品和替代商品、分类和父分类。一元 1:N 的自反联系转换时外键指向自己的表。分类表就是典型category 表里有一个 parent_id 指向自己的 id。一元 M:N 的自反联系则需要一张中间表两个外键指向同一张主体表比如用户关注用户就是 (follower_id, followee_id) 的组合还要加上约束防止自己关注自己。多元联系指的是三个及以上实体参与的联系比如供应商向项目供应零件这种三方关系。多元联系一律独立建表表里放所有参与实体的主键作为外键主键通常是全体外键的组合。这里有个容易忽略的点多元联系的基数语义比二元复杂得多比如供应商-项目-零件如果标注的是 M:N:P那么主键就是三个外键的组合但如果某个参与实体的基数是 1语义会变得很微妙通常意味着业务上需要拆成更细的联系值得回头重新审视 E-R 图。泛化关系的转换有三种常见策略。第一种是单表继承所有子类共用一个表用类型字段区分子类特有属性允许为 NULL。这种方式查询简单但宽表里 NULL 值多约束也不好加。第二种是每个子类一张表只存子类特有属性和主键查询时需要和父表连接。第三种是具体表继承每个子类表都包含全部字段没有父表。电商系统里对用户做泛化时普通用户、商家用户、管理员我一般倾向单表继承加类型字段因为大部分属性重合且登录逻辑需要统一查一张表。3. 实战推演电商购物系统的需求梳理与 E-R 图落地3.1 从业务场景抽出实体和联系先别急着画图把电商购物系统的核心流程走一遍实体是从流程里自然浮出来的。用户注册登录、浏览商品、加入购物车、下单、支付、发货、收货、评价——这条主线上每一步涉及的名词就是候选实体。我整理出来的候选实体清单是这样的用户、收货地址、商品分类、商品、商品图片、库存、购物车、购物车项、订单、订单明细、支付记录、物流记录、评价、优惠券、用户优惠券。看着挺多但其中有些是属性而不是实体比如商品图片如果只是商品的一组图片可以当作多值属性处理也可能因为要记录排序和上传时间而独立成表。联系方面把主线上的动词抽出来用户拥有多个收货地址1:N用户拥有一个购物车1:1购物车包含多个商品通过购物车项实质是 M:N用户下多个订单1:N订单包含多个商品通过订单明细M:N 且带数量和单价属性订单对应支付记录1:N因为可能有多次支付尝试订单对应物流记录1:N用户对商品做评价M:N因为一个用户可以对多个商品评价一个商品有多个评价商品属于分类N:1分类有父分类自反 1:N用户领取优惠券M:N。把实体画成矩形、联系画成菱形标上基数再把属性挂在对应元素上E-R 图就成型了。这里我用文字把关键结构描述清楚方便你在纸上复现用户与收货地址之间是 1:N用户与订单之间是 1:N订单与订单明细之间是弱实体关系明细依赖于订单订单明细与商品之间是 N:1每条明细对应一个商品商品与分类之间是 N:1分类与自身是 1:N 自反订单与支付记录 1:N用户与商品通过评价形成 M:N 并带评分和内容属性。值得单独说的是购物车。很多设计把购物车直接做成一个表加用户外键其实更准确的建模是用户和商品之间是 M:N 的加入购物车联系带一个数量属性转换后就是 cart_item 表字段是 (user_id, product_id, quantity, added_at)。如果业务支持同一商品在不同店铺分别加购那还要把店铺维度加进主键变成 (user_id, product_id, shop_id)。这个判断不做清楚后面就会出现用户加了同一个商品两次购物车里只有一条记录还在纳闷的问题。3.2 逐条套用规则生成表结构现在把规则一条条往 E-R 图上套。强实体直接建表user、address、category、product、shop、order、payment、logistics、review、coupon 各建一张。弱实体 order_item 建表主键用 (order_id, line_no)。多值属性 store_phone 或 product_image 独立建表。1:N 联系的外键下沉address 表加 user_idproduct 表加 category_id 和 shop_idorder 表加 user_id 和 address_idpayment 表和 logistics 表各加 order_id。M:N 联系建中间表user 和 product 之间的评价建 review 表主键可以用 (user_id, product_id, order_id) 组合因为同一用户对同一商品在不同订单里可以分别评价这个三元组合比二元更贴合业务user 和 coupon 之间建 user_coupon 表主键 (user_id, coupon_id, claimed_at) 或者用代理键。自反联系处理category 表加 parent_id 指向自己。这里我特别想强调主键的选择问题。订单表用自增 id 做主键还是用业务单号做主键我的做法是两者都要自增 id 做主键用于外键关联业务单号建唯一索引用于对外展示和查询。原因是自增 id 短、有序、连接效率高而业务单号可能很长、可能带时间戳或随机串做外键会让所有子表都变宽。这个选择在转换阶段就要定下来后期改主键的代价极大。3.3 主外键、约束与建表 SQL 落地规则套完之后把结果落成可执行的语句。下面截取核心几张表重点看约束和外键的设计。CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(64) NOT NULL, password_hash VARCHAR(128) NOT NULL, user_type TINYINT NOT NULL DEFAULT 1 COMMENT 1普通 2商家 3管理员, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE address ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, receiver VARCHAR(32) NOT NULL, province VARCHAR(32) NOT NULL, city VARCHAR(32) NOT NULL, district VARCHAR(32) NOT NULL, detail VARCHAR(255) NOT NULL, is_default TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY idx_user (user_id), CONSTRAINT fk_address_user FOREIGN KEY (user_id) REFERENCES user (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;地址表这里刻意把省市区拆成了独立列而不是一列文本。原因在后面按地域统计订单时会体现出来拆开之后可以给 city 建索引也可以直接做 GROUP BY不用任何字符串函数。订单主表和明细表的关系是弱实体转换的典型注意明细表的主键构成和级联约束。CREATE TABLE order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, address_id BIGINT UNSIGNED NOT NULL, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_created (user_id, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE order_item ( order_id BIGINT UNSIGNED NOT NULL, line_no INT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, quantity INT UNSIGNED NOT NULL, unit_price DECIMAL(10,2) NOT NULL COMMENT 下单时成交单价, PRIMARY KEY (order_id, line_no), KEY idx_product (product_id), CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES order (id) ON DELETE CASCADE, CONSTRAINT fk_item_product FOREIGN KEY (product_id) REFERENCES product (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这两张表里藏着两个转换决策。第一个决策是 unit_price 必须存在明细表里不能只存 product 表的价格。因为商品价格会变订单要记录成交那一刻的价格这是联系上的属性在转换时必须保留的典型例子。第二个决策是给 order 表建了 (user_id, created_at) 的联合索引因为查我的订单列表并按时间倒序是最频繁的查询路径这个索引能让它走索引扫描而不是全表扫。审核一张转换结果表最好的办法就是把高频业务查询逐条写出来看每一条能不能用上索引。写不出来的查询就是索引没设计到位的信号。4. 范式校验与反范式取舍4.1 用三范式给转换结果做体检转换出来的表结构是不是合理可以用范式快速自检。第一范式要求每列都是原子值这对应多值属性必须拆表——如果一个订单表里存了逗号分隔的商品 id 串直接违反 1NF这是新手最常见的问题。第二范式要求非主属性完全依赖主键这主要针对组合主键的表。比如 order_item 的主键是 (order_id, line_no)如果把商品名称放在这张表里商品名称只依赖 product_id 而不依赖整个主键就违反了 2NF。第三范式要求消除传递依赖如果 order 表里放了 user_name而 user_name 依赖 user_iduser_id 又依赖订单主键这就是传递依赖。用范式体检之后理论上表结构是没有冗余的。但数据库设计不是越规范越好这是我要说的重点。4.2 哪些字段该主动冗余真实业务里完全符合 3NF 的表结构在查询时会付出大量连接代价。电商场景下订单列表页需要展示商品名称和商品图片如果每次都从 order_item 连接到 product 再连接 product_image一个订单列表页可能就要 join 四五张表。这时候主动冗余就有价值。我一般会在这些位置做反范式订单明细里冗余商品名称和首图防止商品下架后订单详情显示不出来这其实是业务正确性要求不只是性能订单主表里冗余商品总件数商品表里冗余分类名称用于列表筛选展示。判断标准很简单——当某个字段的取值在业务上应该被冻结时冗余就是必要的当它只是可以算出时冗余才是可选的优化。提示反范式带来的最大风险是更新一致性问题。如果冗余的是商品名称商品改名后历史订单里的名称要保持旧值那就不需要同步如果冗余的是库存数量那就必须保证事务内同步更新。这两种情况本质不同别混在一起处理。5. 踩坑实录与高频问题速查5.1 转换阶段最容易犯的六类错误第一类是把 M:N 联系硬塞成一端的外键。见过有人把订单到商品的多对多关系直接在订单表里加一个 product_id结果一个订单只能买一种商品上线两周后被迫重构。判断标准很简单只要一条记录需要对应多条记录就是 N 端就必须有中间表或者把外键放到对端。第二类是漏掉联系上的属性。收藏夹的收藏时间、订单明细的成交单价、用户领券的领取时间这些都是联系属性转换时最容易丢。丢了之后业务上想按收藏时间排序就只能给用户表或商品表加时间字段语义全乱。第三类是弱实体的主键拼错。弱实体表的主键必须包含所依赖强实体的主键如果只用自己的部分键做主键跨订单就会出现主键冲突。曾经有人给订单明细用自增 id 做主键看似没问题但一查同一订单内是否有重复商品就没法用唯一约束来保证只能在应用层兜容易出并发问题。第四类是外键可空性判断错误。1:N 联系里如果 N 端记录可以不依赖父记录存在外键就必须允许 NULL。比如商品必须属于某个分类吗如果允许未分类商品存在category_id 就该可空如果不允许就该加 NOT NULL 并在业务上保证。第五类是忽略基数约束的参与度。E-R 图上的 1:N 不代表一端必须有 N 端。给商品加分类外键时没加 NOT NULL导致脏数据进来分类为空前端筛选就漏掉这些商品。第六类是把泛化硬做成多张表却忘了统一查询。用户做了单表继承但登录时查了普通用户表商家用户就登不进来。这类问题的根因是转换时没把查询路径和存储结构分开考虑。5.2 转换问题速查表我把处理过的典型问题整理成表遇到类似情况可以直接对照。现象可能的转换缺陷处理方式一个订单只能有一种商品M:N 被错误转成 1:N拆出 order_item 中间表历史订单显示的商品价格变了成交价格没冗余到明细表明细表增加 unit_price 字段按城市统计订单量很慢地址被存成一整串文本拆分为省市区独立列并建索引删除订单后明细还在弱实体未设级联删除外键加 ON DELETE CASCADE同一商品在购物车重复出现购物车主键选择不当用 (user_id, product_id) 组合主键分类层级查询要递归很多次自反联系未考虑查询模式加 path 字段或闭包表辅助5.3 一些上手就能用的经验转换完成后我有个固定动作打开数据库把每张表的前两行随便插一下看外键约束会不会报错看插入顺序有没有循环依赖。有些设计会出现 A 表必须引用 B、B 表又必须引用 A 的情况这就是转换时把双向 1:N 当成了双向必须实际上至少要有一端是可空的。另一个经验是关于主键类型的统一。如果确定了用 BIGINT 自增那所有表的主键和外键类型都要一致包括 UNSIGNED 修饰符。类型不一致的外键在 MySQL 里会静默地不走索引这个坑我在一个订单量过千万的系统里踩过排查了整整两天才发现是 int 和 bigint 的差异导致连接查询全表扫。还有一件事值得提前想清楚软删除怎么做。订单、商品这类数据一般不能物理删除会加 is_deleted 标记。这时所有唯一索引都要跟着调整比如用户名唯一索引要从 (username) 改成 (username, is_deleted)否则删掉的用户名就再也注册不了了。这类约束层面的影响往往在转换阶段就要一并规划等到上线后再改代价会成倍增加。这套流程走下来你会发现 E-R 图向关系模型的转换远不只是照着画表它是一次把业务语义、查询模式和约束完整性放在一起权衡的工程活动。画图阶段想得越细转换阶段能套用的规则就越明确落表之后的返工概率也越低。 E-R图向关系模型转换：规则、约束与电商建表实战
E-R 图向关系模型的转换是数据库设计里最容易被轻视、也最不能出错的一环。很多人画 E-R 图的时候思路清晰方框菱形连线一气呵成可一到落表就卡住了多对多联系到底要不要单独建表弱实体的主键怎么拼用户和商品之间那条线该不该变成一个字段。我在做过十几个业务系统之后越来越觉得 E-R 图只是想清楚了而 E-R 图向关系模型的转换才是写下来中间这一步的取舍质量直接决定了后面三年你要不要反复改表。这篇内容面向的是正在做课程设计的学生、刚接手从零建库的后端开发、以及需要评审数据库设计的架构同学。我会把转换规则拆到能被验证的程度再拿一个完整的电商购物系统从头走一遍从需求分析、实体抽取、E-R 图设计到逐条套用规则生成表结构最后给出可直接执行的建表语句、约束设计和索引建议。整个过程里我会说清楚每一步为什么这么选以及那些文档里不写、但线上一定会遇到的问题。1. 先搞清楚一件事E-R 图和关系模型到底差在哪1.1 概念世界和关系世界是两套语言E-R 图属于概念数据模型它是给人看的。方框代表实体椭圆代表属性菱形代表联系线条上的 1、N、M 标注基数。这套语言的优势在于表达力强能容纳现实世界的模糊性——比如一个订单包含多个商品这在图上就是一条线加一个 N简单直接谁看都懂。但它不能直接存进数据库因为关系型数据库只认一个东西二维表。关系模型属于逻辑数据模型它的表达能力被严格限制在三件事上表、列、行。所有的语义都必须压扁成某个表里的某个字段引用了另一个表的主键。这就带来一个根本矛盾——E-R 图里那种线和菱形的独立结构在关系模型里没有对等物。联系的语义要么被吸收进某张表变成一个外键列要么被迫独立成一张只有外键的表。转换过程本质上就是在做这种语义降维而降维一定会伴随信息形式的改变。我习惯把这一步类比成把一栋立体建筑投影成三视图。投影之后你依然能还原建筑但每个视图单独看都不完整必须三个视图配合。同理转换后的表结构要能完整还原 E-R 图所描述的业务约束靠的不是某一张表而是表之间的外键关系和约束共同构成的整体。1.2 转换链条上最容易丢的三类信息在动手写表之前先明确哪些东西最容易在转换过程中丢掉这比记规则更重要。第一类是基数约束的细节。E-R 图上写的是 1:N但这个1是必须存在还是可以不存在N这一端是至少一条还是可以零条这些叫参与度约束图上的线条往往标不全。比如订单和支付记录之间一个订单可能还没支付也可能有多条支付尝试记录如果只写 1:N转换出来的外键字段可空性就没法确定。第二类是属性本身的语义弱化。E-R 图里复合属性比如收货地址可以拆成省、市、区、详细地址在图上是一个带分支的椭圆转换时如果不拆开全部塞进一个字段后面想按城市统计订单量就只能做字符串匹配性能和准确度都会崩。第三类是联系上的属性。这条最隐蔽——M:N 联系本身可以带属性比如用户和商品之间的收藏联系带一个收藏时间或者订单和商品之间的包含联系带一个购买数量和成交单价。很多人转换时只顾着建中间表的主键把联系上的属性忘了结果数量只能从别处反推反推出来的值还可能因为商品改价而失真。提示转换完成之后我习惯做一次反向验证——拿着表结构去回答业务问题比如某个用户上周买过哪些品类的商品。如果这个问题需要绕三张表还没有明确的连接路径说明转换时丢了联系。2. 七条转换规则从图形元素到表结构的一一映射2.1 实体、弱实体与属性的拆解强实体的转换最直接一个实体对应一张表实体的每个简单属性对应表的一列实体的标识符对应表的主键。这里没什么争议真正的坑在于属性类型的判定。简单属性直接建列。复合属性要拆成多个列比如地址拆成 province、city、district、detail 四列而不是一列 address 存全串。理由很实际拆开之后可以单独给 city 建索引做分组统计合并成一个字符串就只能全表扫描。多值属性必须独立成表比如一个用户可以有多个手机号这时手机号不是用户表的列而是一张 user_phone 表结构是 (user_id, phone)主键是两者组合。派生属性一般不建列比如年龄可以由出生日期算出来订单总金额可以由明细汇总得到存下来反而要处理同步更新的一致性问题。弱实体的处理需要多一步。弱实体没有自己的完整标识符它的标识依赖某个强实体存在比如订单明细依赖订单存在明细编号只在同一个订单内唯一。转换时弱实体的主键是自身部分键 所依赖强实体的主键的组合。订单明细的主键就是 (order_id, line_no)其中 line_no 是明细在订单内的序号。同时 order_id 还兼作外键并要加上级联删除约束——订单删了明细必须跟着删否则就是孤儿数据。2.2 二元联系的三种基数处理这是转换的核心区也是最容易记混的地方。我把三种基数的处理方式拉成一张表方便对照。联系类型转换策略外键放在哪是否需要新表1:1合并到任一端通常合并到参与度更高的一端被合并端的表一般不需要1:N把 1 端的主键放到 N 端作外键N 端的表不需要M:N独立建一张联系表两个外键都在新表需要1:1 联系的处理要点是往哪边合并。判断依据是可选性把外键放在可选的一边可以允许 NULL如果放在必选的一边就会出现必须插入另一条记录才能插入当前记录的鸡生蛋问题。比如用户和实名认证信息是一对一但用户可能还没做实名那么 user_id 应该放在认证信息表里而不是反过来。1:N 联系几乎是关系模型里最舒服的结构外键天然表达了多这一端的归属。这里有一个细节要注意外键列要不要建索引答案是只要这个外键会被用来做连接查询或者按父表过滤就应该建。MySQL 的 InnoDB 在建外键约束时会自动为外键列创建索引但如果只是逻辑外键不在数据库层加约束就得自己手动建否则 N 端表的按父查询会走全表扫描。M:N 联系必须独立建表这张表通常叫连接表或中间表。它的主键有几种选择用两端主键组合做主键、用代理键做主键加唯一约束、或者干脆不要主键只要两个索引。我的经验是如果这张表上有业务属性比如数量、时间用代理键做主键更利于后续扩展如果纯粹是关系映射用组合主键更省空间也更能防止重复插入。2.3 自反联系、多元联系与泛化的处理自反联系是同一个实体内部的关系比如员工和上级经理、商品和替代商品、分类和父分类。一元 1:N 的自反联系转换时外键指向自己的表。分类表就是典型category 表里有一个 parent_id 指向自己的 id。一元 M:N 的自反联系则需要一张中间表两个外键指向同一张主体表比如用户关注用户就是 (follower_id, followee_id) 的组合还要加上约束防止自己关注自己。多元联系指的是三个及以上实体参与的联系比如供应商向项目供应零件这种三方关系。多元联系一律独立建表表里放所有参与实体的主键作为外键主键通常是全体外键的组合。这里有个容易忽略的点多元联系的基数语义比二元复杂得多比如供应商-项目-零件如果标注的是 M:N:P那么主键就是三个外键的组合但如果某个参与实体的基数是 1语义会变得很微妙通常意味着业务上需要拆成更细的联系值得回头重新审视 E-R 图。泛化关系的转换有三种常见策略。第一种是单表继承所有子类共用一个表用类型字段区分子类特有属性允许为 NULL。这种方式查询简单但宽表里 NULL 值多约束也不好加。第二种是每个子类一张表只存子类特有属性和主键查询时需要和父表连接。第三种是具体表继承每个子类表都包含全部字段没有父表。电商系统里对用户做泛化时普通用户、商家用户、管理员我一般倾向单表继承加类型字段因为大部分属性重合且登录逻辑需要统一查一张表。3. 实战推演电商购物系统的需求梳理与 E-R 图落地3.1 从业务场景抽出实体和联系先别急着画图把电商购物系统的核心流程走一遍实体是从流程里自然浮出来的。用户注册登录、浏览商品、加入购物车、下单、支付、发货、收货、评价——这条主线上每一步涉及的名词就是候选实体。我整理出来的候选实体清单是这样的用户、收货地址、商品分类、商品、商品图片、库存、购物车、购物车项、订单、订单明细、支付记录、物流记录、评价、优惠券、用户优惠券。看着挺多但其中有些是属性而不是实体比如商品图片如果只是商品的一组图片可以当作多值属性处理也可能因为要记录排序和上传时间而独立成表。联系方面把主线上的动词抽出来用户拥有多个收货地址1:N用户拥有一个购物车1:1购物车包含多个商品通过购物车项实质是 M:N用户下多个订单1:N订单包含多个商品通过订单明细M:N 且带数量和单价属性订单对应支付记录1:N因为可能有多次支付尝试订单对应物流记录1:N用户对商品做评价M:N因为一个用户可以对多个商品评价一个商品有多个评价商品属于分类N:1分类有父分类自反 1:N用户领取优惠券M:N。把实体画成矩形、联系画成菱形标上基数再把属性挂在对应元素上E-R 图就成型了。这里我用文字把关键结构描述清楚方便你在纸上复现用户与收货地址之间是 1:N用户与订单之间是 1:N订单与订单明细之间是弱实体关系明细依赖于订单订单明细与商品之间是 N:1每条明细对应一个商品商品与分类之间是 N:1分类与自身是 1:N 自反订单与支付记录 1:N用户与商品通过评价形成 M:N 并带评分和内容属性。值得单独说的是购物车。很多设计把购物车直接做成一个表加用户外键其实更准确的建模是用户和商品之间是 M:N 的加入购物车联系带一个数量属性转换后就是 cart_item 表字段是 (user_id, product_id, quantity, added_at)。如果业务支持同一商品在不同店铺分别加购那还要把店铺维度加进主键变成 (user_id, product_id, shop_id)。这个判断不做清楚后面就会出现用户加了同一个商品两次购物车里只有一条记录还在纳闷的问题。3.2 逐条套用规则生成表结构现在把规则一条条往 E-R 图上套。强实体直接建表user、address、category、product、shop、order、payment、logistics、review、coupon 各建一张。弱实体 order_item 建表主键用 (order_id, line_no)。多值属性 store_phone 或 product_image 独立建表。1:N 联系的外键下沉address 表加 user_idproduct 表加 category_id 和 shop_idorder 表加 user_id 和 address_idpayment 表和 logistics 表各加 order_id。M:N 联系建中间表user 和 product 之间的评价建 review 表主键可以用 (user_id, product_id, order_id) 组合因为同一用户对同一商品在不同订单里可以分别评价这个三元组合比二元更贴合业务user 和 coupon 之间建 user_coupon 表主键 (user_id, coupon_id, claimed_at) 或者用代理键。自反联系处理category 表加 parent_id 指向自己。这里我特别想强调主键的选择问题。订单表用自增 id 做主键还是用业务单号做主键我的做法是两者都要自增 id 做主键用于外键关联业务单号建唯一索引用于对外展示和查询。原因是自增 id 短、有序、连接效率高而业务单号可能很长、可能带时间戳或随机串做外键会让所有子表都变宽。这个选择在转换阶段就要定下来后期改主键的代价极大。3.3 主外键、约束与建表 SQL 落地规则套完之后把结果落成可执行的语句。下面截取核心几张表重点看约束和外键的设计。CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(64) NOT NULL, password_hash VARCHAR(128) NOT NULL, user_type TINYINT NOT NULL DEFAULT 1 COMMENT 1普通 2商家 3管理员, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE address ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, receiver VARCHAR(32) NOT NULL, province VARCHAR(32) NOT NULL, city VARCHAR(32) NOT NULL, district VARCHAR(32) NOT NULL, detail VARCHAR(255) NOT NULL, is_default TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY idx_user (user_id), CONSTRAINT fk_address_user FOREIGN KEY (user_id) REFERENCES user (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;地址表这里刻意把省市区拆成了独立列而不是一列文本。原因在后面按地域统计订单时会体现出来拆开之后可以给 city 建索引也可以直接做 GROUP BY不用任何字符串函数。订单主表和明细表的关系是弱实体转换的典型注意明细表的主键构成和级联约束。CREATE TABLE order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, address_id BIGINT UNSIGNED NOT NULL, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_created (user_id, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE order_item ( order_id BIGINT UNSIGNED NOT NULL, line_no INT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, quantity INT UNSIGNED NOT NULL, unit_price DECIMAL(10,2) NOT NULL COMMENT 下单时成交单价, PRIMARY KEY (order_id, line_no), KEY idx_product (product_id), CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES order (id) ON DELETE CASCADE, CONSTRAINT fk_item_product FOREIGN KEY (product_id) REFERENCES product (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这两张表里藏着两个转换决策。第一个决策是 unit_price 必须存在明细表里不能只存 product 表的价格。因为商品价格会变订单要记录成交那一刻的价格这是联系上的属性在转换时必须保留的典型例子。第二个决策是给 order 表建了 (user_id, created_at) 的联合索引因为查我的订单列表并按时间倒序是最频繁的查询路径这个索引能让它走索引扫描而不是全表扫。审核一张转换结果表最好的办法就是把高频业务查询逐条写出来看每一条能不能用上索引。写不出来的查询就是索引没设计到位的信号。4. 范式校验与反范式取舍4.1 用三范式给转换结果做体检转换出来的表结构是不是合理可以用范式快速自检。第一范式要求每列都是原子值这对应多值属性必须拆表——如果一个订单表里存了逗号分隔的商品 id 串直接违反 1NF这是新手最常见的问题。第二范式要求非主属性完全依赖主键这主要针对组合主键的表。比如 order_item 的主键是 (order_id, line_no)如果把商品名称放在这张表里商品名称只依赖 product_id 而不依赖整个主键就违反了 2NF。第三范式要求消除传递依赖如果 order 表里放了 user_name而 user_name 依赖 user_iduser_id 又依赖订单主键这就是传递依赖。用范式体检之后理论上表结构是没有冗余的。但数据库设计不是越规范越好这是我要说的重点。4.2 哪些字段该主动冗余真实业务里完全符合 3NF 的表结构在查询时会付出大量连接代价。电商场景下订单列表页需要展示商品名称和商品图片如果每次都从 order_item 连接到 product 再连接 product_image一个订单列表页可能就要 join 四五张表。这时候主动冗余就有价值。我一般会在这些位置做反范式订单明细里冗余商品名称和首图防止商品下架后订单详情显示不出来这其实是业务正确性要求不只是性能订单主表里冗余商品总件数商品表里冗余分类名称用于列表筛选展示。判断标准很简单——当某个字段的取值在业务上应该被冻结时冗余就是必要的当它只是可以算出时冗余才是可选的优化。提示反范式带来的最大风险是更新一致性问题。如果冗余的是商品名称商品改名后历史订单里的名称要保持旧值那就不需要同步如果冗余的是库存数量那就必须保证事务内同步更新。这两种情况本质不同别混在一起处理。5. 踩坑实录与高频问题速查5.1 转换阶段最容易犯的六类错误第一类是把 M:N 联系硬塞成一端的外键。见过有人把订单到商品的多对多关系直接在订单表里加一个 product_id结果一个订单只能买一种商品上线两周后被迫重构。判断标准很简单只要一条记录需要对应多条记录就是 N 端就必须有中间表或者把外键放到对端。第二类是漏掉联系上的属性。收藏夹的收藏时间、订单明细的成交单价、用户领券的领取时间这些都是联系属性转换时最容易丢。丢了之后业务上想按收藏时间排序就只能给用户表或商品表加时间字段语义全乱。第三类是弱实体的主键拼错。弱实体表的主键必须包含所依赖强实体的主键如果只用自己的部分键做主键跨订单就会出现主键冲突。曾经有人给订单明细用自增 id 做主键看似没问题但一查同一订单内是否有重复商品就没法用唯一约束来保证只能在应用层兜容易出并发问题。第四类是外键可空性判断错误。1:N 联系里如果 N 端记录可以不依赖父记录存在外键就必须允许 NULL。比如商品必须属于某个分类吗如果允许未分类商品存在category_id 就该可空如果不允许就该加 NOT NULL 并在业务上保证。第五类是忽略基数约束的参与度。E-R 图上的 1:N 不代表一端必须有 N 端。给商品加分类外键时没加 NOT NULL导致脏数据进来分类为空前端筛选就漏掉这些商品。第六类是把泛化硬做成多张表却忘了统一查询。用户做了单表继承但登录时查了普通用户表商家用户就登不进来。这类问题的根因是转换时没把查询路径和存储结构分开考虑。5.2 转换问题速查表我把处理过的典型问题整理成表遇到类似情况可以直接对照。现象可能的转换缺陷处理方式一个订单只能有一种商品M:N 被错误转成 1:N拆出 order_item 中间表历史订单显示的商品价格变了成交价格没冗余到明细表明细表增加 unit_price 字段按城市统计订单量很慢地址被存成一整串文本拆分为省市区独立列并建索引删除订单后明细还在弱实体未设级联删除外键加 ON DELETE CASCADE同一商品在购物车重复出现购物车主键选择不当用 (user_id, product_id) 组合主键分类层级查询要递归很多次自反联系未考虑查询模式加 path 字段或闭包表辅助5.3 一些上手就能用的经验转换完成后我有个固定动作打开数据库把每张表的前两行随便插一下看外键约束会不会报错看插入顺序有没有循环依赖。有些设计会出现 A 表必须引用 B、B 表又必须引用 A 的情况这就是转换时把双向 1:N 当成了双向必须实际上至少要有一端是可空的。另一个经验是关于主键类型的统一。如果确定了用 BIGINT 自增那所有表的主键和外键类型都要一致包括 UNSIGNED 修饰符。类型不一致的外键在 MySQL 里会静默地不走索引这个坑我在一个订单量过千万的系统里踩过排查了整整两天才发现是 int 和 bigint 的差异导致连接查询全表扫。还有一件事值得提前想清楚软删除怎么做。订单、商品这类数据一般不能物理删除会加 is_deleted 标记。这时所有唯一索引都要跟着调整比如用户名唯一索引要从 (username) 改成 (username, is_deleted)否则删掉的用户名就再也注册不了了。这类约束层面的影响往往在转换阶段就要一并规划等到上线后再改代价会成倍增加。这套流程走下来你会发现 E-R 图向关系模型的转换远不只是照着画表它是一次把业务语义、查询模式和约束完整性放在一起权衡的工程活动。画图阶段想得越细转换阶段能套用的规则就越明确落表之后的返工概率也越低。