我在第二份工作接手了一个外卖平台的历史数据。orders 表里二十多个字段从下单时间、用户手机号、用户地址到店铺ID、店铺名、店铺电话、商品名、商品价格甚至配送员姓名和手机号全挤在同一张表里。某个下午商家改了一次店名过去几个月几千条历史订单全部跟着被更新财务对不上账客诉一片。带我的人只看了一眼表结构说了句“一张表里塞了两种以上实体迟早出事。回去把数据依赖、设计异常、关系分解、范式这条线过一遍。”这句话当时不觉得重后来我才理解数据库设计里最难的部分不是写 SQL不是调索引而是先把一张表里装了几个“世界”看清楚。这篇文章就把这条完整的因果链拆开讲透适合正在学数据库课程的人也适合工作两三年、建表基本靠直觉的开发同行。1. 一锅端的orders大表先看清一张表里到底装了几个实体1.1 事故现场这张表实在是太“方便”了当时的表结构简化后大概是这个味道订单号、用户手机号、用户地址、店铺ID、店铺名、店铺电话、商品ID、商品名、商品单价、购买数量、配送员姓名、配送员电话、下单时间。主键采用了(订单号, 商品ID)因为一个订单可以包含多个商品。现在回头看这个设计在当时有一个非常“充分”的理由查询方便。后端同事想查订单详情一条 SQL 就能把用户、店铺、商品、骑手全部带到省掉好几个 JOIN。业务催着上线谁也没空纠结理论。但问题恰恰就出在这个“方便”上。店铺ID、店铺名、店铺电话这些字段本质上属于店铺实体不属于订单实体。它们被塞进订单行之后就会随着每一笔订单被复制一遍。假设一家店有 1 万条历史订单店铺名在这张表里就有 1 万份拷贝。商家改一次店名程序为了让历史数据也显示新店名就得把这 1 万行全部 UPDATE。只要漏掉任何一条同一天的报表里就会同时出现两个店名对账必炸。1.2 问题不在行数多而在“实体”混在了一起实体是现实世界里独立存在的对象。用户、店铺、商品、配送员、订单本身各有各的属性也各有各的生命周期。用户可能注销店铺可能迁址商品可能下架但它们都不会因为一笔订单消失而消失。当多个实体被塞进同一张表时一个实体的属性会跟着另一个实体的每次出现而重复。更麻烦的是如果主键是多个实体的标识拼起来的比如(订单号, 商品ID)那么店铺信息和商品信息就都只依赖这个组合键的一部分。这种“依赖没对准主键”的状态就是后面一系列异常的地基。在这里明确一个观点“实体”不是玄学它决定了字段之间的依赖关系。每个实体至少有一组属性能在不依赖其他实体的情况下唯一标识自己比如店铺ID、商品ID、用户ID。当多个实体挤在一张表里时主键往往就会变成多个标识的组合。1.3 数据依赖、设计异常、关系分解、范式本质是一条因果链这四个词的逻辑关系比大部分教材讲得要紧凑得多数据依赖描述的是字段 A 确定之后字段 B 能不能跟着唯一确定。它把业务规则翻译成“箭头”。设计异常是这些箭头没有对准主键之后出现的症状插不进去、删了连带、改了爆炸。关系分解是治病动作把一张表按正确的箭头拆成多张表。范式是判断病治没治好的刻度1NF、2NF、3NF、BCNF 一级级往上验。所以我的建议是不要先背范式定义而是从数据依赖出发发现问题再做分解最后用范式做验收。下面依次展开。2. 数据依赖先把业务规则翻译成“箭头”2.1 函数依赖就是“X一旦确定Y就跟着确定”严谨点说在关系 R 里只要任意两行数据的 X 属性值相同Y 属性值就一定相同那么称 Y 函数依赖于 X记作X → Y。生活里到处都是这种例子身份证号确定姓名就确定订单号确定下单时间就确定学号确定所在系就确定。这些箭头背后是业务规则不是某一时刻的表数据碰巧相等。判断一个箭头成不成立必须问业务专家“一个商品ID在所有时间、所有状态下是不是都只能对应一个商品名”这个细节很多人会翻车。比如两家店都卖“手打柠檬茶”商品名完全一样但价格和门店完全不同。这时候“商品名 → 价格”就不成立必须用商品ID做箭头左侧。设计数据库时凡是遇到“名称类”字段都要先问一句它真的能做到全局唯一吗不确定的话请给它配一个ID让ID 去决定名称。2.2 完全依赖、部分依赖、传递依赖三条要命箭头用一个简化过的订单明细表继续说订单明细(订单号, 商品ID, 商品名, 商品单价, 店铺ID, 店铺名)业务规则假设如下一个订单可以包含多个商品一个商品可以出现在多个订单里每个商品属于一个店铺商品ID 决定商品名、基础单价和店铺ID店铺ID 决定店铺名。这里的“商品单价”指的是店内基础单价不是订单成交价否则它就依赖完整的(订单号, 商品ID)了。这个表的主键是(订单号, 商品ID)。接下来看三类依赖完全函数依赖X → Y且 X 的任何真子集都不能决定 Y。比如订单成交价必须同时知道“哪个订单”和“哪个商品”才能确定成交价这就是典型的完全依赖。部分函数依赖Y 能被候选键的某个真子集决定。上面表里的商品名就是最典型的例子它只依赖商品ID跟订单号没有任何关系。商品单价和店铺ID同理也都是只依赖商品ID 的部分依赖。传递函数依赖X → AA → YY 经过 A 转了一手才被 X 确定。在上面这张表里由于(订单号, 商品ID) → 商品ID → 店铺ID → 店铺名店铺名这个字段实际上是在依赖路径的末端经过了商品ID 和店铺ID 两次“中转”才和主键产生关系。2.3 写依赖集时最容易漏的三类情况第一漏了“业务约束型依赖”。比如业务规定“一个教师只能教一门课”这就是一个合法且重要的函数依赖教师号 → 课程号。但需求文档里通常不会单独写这句话它是从“排课规则”里提炼出来的。如果前期的实体或规则清单里没有它后面做 BCNF 判断就会得到假结果。第二混淆“唯一标识”和“函数依赖”。手机号表面上看能唯一指向一个用户但如果业务允许用户换绑手机号老手机号在历史数据里就可能对应多个用户这时手机号 → 用户ID就不成立。唯一标识是否可持续要看业务的全生命周期不能只看某张表的当前数据。第三忽略了时间版本。地址、电话这类会变化的属性设计时要清楚它们到底依赖“用户ID”还是“用户ID 生效时间”。前者意味着字段会被更新覆盖后者意味着要保留历史版本。这两种设计对后面范式判断的影响完全不同。另外补充一句函数依赖只能表达“一对一、多对一”的确定性。还有更复杂的多值依赖比如一个课程有多个教材、多个教师这是 4NF 的范畴。OLTP 系统里很少刻意追到 4NF但知道这条链上有这个续集对理解范式边界有帮助。2.4 一个实用习惯先用自然语言写规则再转成箭头我现在设计表之前一定先在文档里写业务规则。规则写成这样“一个订单包含 1 到 N 个商品”“一个商品只属于一个店铺”“一个店铺有唯一店名”“一个商品在同一订单内只出现一行数量用字段记录”。然后逐条把规则转成函数依赖比如“商品ID → 店铺ID”“商家ID → 店铺名”。这一步很长但很值。因为它逼着你把业务方口头说的“大概是一对多吧”精确成“到底谁是‘一’谁是‘多’”。一旦箭头写全了后面所有的拆表操作都有了依据不再靠拍脑袋。3. 设计异常为什么能跑起来的表会一步步变质3.1 四类异常一张大订单表全遇到继续用那张订单明细(订单号, 商品ID, 商品名, 商品单价, 店铺ID, 店铺名, 店铺电话)的表主键(订单号, 商品ID)来看看一张“能跑”的表会怎么变质。插入异常想先录入一家新店但这家店还没上架任何商品自然也没有订单。此时整行数据里订单号和商品ID都是空的而它们又是主键的组成部分数据库直接拒绝插入。结果是一个真实存在的店铺实体在系统里连建档都建不了。它的“出生”被主键绑架了。删除异常店铺下架了最后一个商品运维顺手删掉了最后一条商品明细。结果不只是商品没了店铺ID、店铺名、店铺电话也一起从表里消失了。现实中店铺还在系统里却查无此店。一个实体的“死亡”被另一个实体的删除连带触发了。更新异常店铺改电话必须把该店铺所有历史订单行里的店铺电话全部 UPDATE 一遍。改一万行也好漏一行也罢只要有一个不一致后面所有基于这张表的统计都可能对不上。上一节事故里的改店名本质就是更新异常。数据冗余店铺名、店铺电话这类静态属性在每一行订单里被反复存储。磁盘空间其实是最不值钱的问题真正麻烦的是同一事实有无数份拷贝想在任意一次写操作后让它们保持一致只能把所有拷贝都改一遍。3.2 异常的本质一行数据里并存了多条生命周期实体都有自己的生命周期。订单会创建、会完成店铺会入驻、会停业商品会上架、会下架它们的时间轴完全独立。把多个实体挤在同一张表后实体的“存活”就被绑死了。订单还没产生店铺不能建档这是插入异常订单明细被删店铺跟着消失这是删除异常店铺信息一变所有历史订单全要重写这是更新异常和冗余。四类异常看起来症状不同根子是同一个一行记录里同时依赖了多个实体的主键。3.3 判断表“有没有病”靠的是写操作而不是读操作很多开发朋友说“我的表用着没问题啊SELECT 都能查出来”。但设计异常基本不在读路径上发作它是在写路径上爆发的。能查不代表能正确写一张表健不健康要拿三个场景去试能不能插入一条“子实体还没出现”的父实体记录删除一条明细时会不会连带着把另一个实体的信息也删掉修改一个共享属性时需要 UPDATE 多少行这三个问题的答案只要有一个“很别扭”就该考虑拆表。我在做数据库设计评审的时候看一张表超过五分钟还理不清它的候选键基本就能预判它会在哪类写操作上出事。3.4 冗余不是原罪冗余引起的“必须同步修改”才是需要强调一下冗余本身并不总是坏事。报表系统里大量冗余字段就是为了减少 JOIN、提高读性能这叫有预谋的冗余。但无约束冗余会导致同一个事实有很多副本如果这些副本在每次写操作时都必须保持完全一致一旦没有事务或同步机制兜底就一定会出现不一致。大订单表的问题不是“存了店铺名”而是把店铺名绑定在每一行订单记录的生命周期里。店铺名一旦变化所有历史订单行都必须跟着变这就是异常。理解了这条边界后面讲到反范式时就不会走极端。4. 关系分解拆表不难难的是拆完不丢数据4.1 分解的本质是投影不是迁移关系分解是指把原关系的属性集拆成若干子集每个子集形成一张新表原表的数据通过投影产生新表的数据。以后想要完整视图再用 JOIN 把子表拼回去。听起来很简单但有一个硬要求拼回来的表必须和原表一模一样。准确说所有子表自然连接的结果既不能多一行也不能少一行。多出来的行叫有损分解它会凭空造出用户从没录过的组合数据。很多人以为拆表就是把列分一分结果一 JOIN 出现爆炸行还找不到原因。4.2 有损分解的经典反例想象一张选课成绩表(学号, 课程号, 成绩)。有人为了减少列数把它拆成(学号, 成绩)和(课程号, 成绩)想让两张表各自留一份成绩。这拆法表面上“每张表都很干净”但两个学生各选了两门课时JOIN 出来的结果会怎样第一张子表有(S1, 90)第二张子表有(C1, 90)和(C2, 85)。如果强行按成绩列相等做连接就会组合出(S1, C1, 90)和(S1, C2, 90)。其中一个组合(S1, C2, 90)可能完全不存在是系统自己脑补出来的数据。这就是有损分解的本质子表之间丢失了(学号, 课程号)这个真实对应关系偏偏公共列又不足以还原它。现实中更常见的等价错误是公共列不是任何一张子表的键或者公共列虽然值相等但一对多关系被串成了笛卡尔积。拆完之后不验证迟早会在这上面吃大亏。4.3 无损分解的判定法则看交集能不能当“钥匙”理论给出的判断非常干净如果把 R 拆成 R1 和 R2只要R1 ∩ R2能函数决定 R1 或 R2 中的某一个这个分解就是无损的。用人话说两个子表公共的那几列必须能在其中一张子表里承担唯一标识的作用。比如把订单明细拆成订单表和店铺表时公共列是店铺ID而店铺ID 在店铺表里是主键它能决定店铺名、店铺电话所以这个分解是可靠的。反过来选课成绩表的错误拆法中公共列是成绩成绩在(学号, 成绩)里不能决定学号在(课程号, 成绩)里也不能决定课程号。公共列没有“钥匙”能力于是 JOIN 就出问题。拆成三张及以上时无损性判断要两两迭代但实际工程中大部分情况下一次只处理一个依赖箭头很少碰到特别复杂的情形。4.4 除了不丢数据还要尽量保留函数依赖有损是硬伤但还有一种软伤叫丢失依赖。以选课表(学号, 课程号, 教师工号)为例业务规则包含“教师工号 → 课程号”和“(学号, 课程号) → 教师工号”。如果把表拆成(学号, 教师工号)和(教师工号, 课程号)公共列教师工号能决定课程号分解本身是无损的。但原来的约束“同一个学生同一门课只能有一位教师”在单独任何一张子表里都没法直接验证。要检查这个约束必须把两张表 JOIN 起来要么在应用层写逻辑要么用触发器麻烦且容易漏。这就是为什么函数依赖要一行行对照子表看一遍的原因。如果一个依赖的左右两边属性还完整地落在同一个子模式里这个依赖就被保留了否则就要评估约束检查的成本。也正因为这样实际工程里绝大多数 OLTP 系统做到 3NF 就很稳不必强行追求 BCNF。4.5 实操拆表流程按照平时的工作习惯我们拆表可以按下面这六步走列出当前表的全部属性找出候选键。把所有函数依赖箭头标全。对每个“部分依赖”把被依赖的那部分属性和它的结果列拆出去组成新表。对每个“传递依赖”把中间属性和最终结果属性拆出去。每拆一次立刻用交集判定法验证无损性。把原始函数依赖逐条对照子表检查是否丢失丢了要评估能不能接受。SQL 层面也可以做验证比如先把原表分组计数再对拆完的子表做 JOIN 计数两个 COUNT 不一致就一定有问题。SELECT COUNT(*) FROM 原表; SELECT COUNT(*) FROM 子表1 JOIN 子表2 ON 子表1.公共键 子表2.公共键;第二个查询的数量如果大于第一个说明 JOIN 产生了多余行这个拆法就不能上线。5. 范式阶梯从1NF到BCNF每一级到底在解决什么5.1 1NF属性必须原子但原子不等于越细越好第一范式要求每个字段不可再分。比如地址字段存“北京市海淀区xx路xx号”这是一个字符串算原子如果存成“北京市|海淀区|xx路|xx号”还要在 SQL 里拆开用就是非原子。但这里有个常见的过度设计把地址无脑拆成省、市、区、街道、门牌号十几个字段。要不要拆取决于业务是否真的需要按省、市单独统计。如果只需要整体展示一个字符串字段反而更省事。第一范式更像是一条底线大部分数据库表天然满足不值得为此焦虑。5.2 2NF把只依赖主键一半的属性扫地出门第二范式要求在 1NF 基础上消除非主属性对候选键的部分依赖。回到订单明细表候选键是(订单号, 商品ID)但商品名只依赖商品ID是典型的部分依赖。2NF 要求把商品信息拆出去订单表保留(订单号, 商品ID, 数量)商品表单独存放(商品ID, 商品名, 商品单价, 店铺ID)。这样商品信息只存一份店铺也不会再跟着每个商品无限重复。还有一个偷懒的判断技巧如果候选键本身就是单一属性那这张表天然不存在部分依赖2NF 自动满足。只有当候选键是复合键时才需要认真检查有没有“只依赖半把钥匙”的属性。5.3 3NF切断“二传手”依赖第三范式要求在 2NF 基础上消除非主属性对候选键的传递依赖。最经典的例子是学生表(学号, 姓名, 系名, 系主任)。这里学号 → 系名系名 → 系主任所以学号间接确定了系主任。系主任虽然最终能由学号推导出来但它本质上依赖的是系名这个属性。拆法就是把系信息单独拿出去学生表保留(学号, 姓名, 系名)系表单独存(系名, 系主任)。可以刻在工位上的一句话是所有非主属性只能直接依赖候选键不能通过别的属性“转一手”再依赖。拆完 3NF 之后每个非主属性都与主键保持一对一的直接关系。5.4 BCNF连“决定因素”都必须手里有主键3NF 还有一个漏网之鱼如果某个属性 X 本身不是候选键却能决定其他属性而且这个决定关系既不构成部分依赖也不构成传递依赖那么这张表是 3NF但不满足 BCNF。回到选课表(学号, 课程号, 教师工号)规则是“教师工号 → 课程号”“(学号, 课程号) → 教师工号”。候选键是(学号, 课程号)没有部分依赖也没有传递依赖所以它是 3NF。但教师工号不是超键却能决定课程号BCNF 不允许这种情况存在。要满足 BCNF可以拆成(学号, 教师工号)和(教师工号, 课程号)但正如前面说的拆完之后(学号, 课程号) → 教师工号这个依赖就丢了。现实业务里如果系统能接受约束靠应用层维护BCNF 没问题如果希望数据库层面尽量干净地维护约束3NF 往往更稳妥。5.5 一张表看懂四道关卡范式消除的问题怎么检查典型信号1NF非原子字段字段是否还能继续拆一个字段存了多个值或一段结构化文本2NF部分依赖非主属性是否只依赖主键的一部分复合主键的某个“子键”值在表里大面积重复3NF传递依赖非主属性之间是否互相决定出现了“A → BB → C”的依赖链BCNF非超键决定关系每个依赖箭头左侧是否都是超键某个普通字段能决定另一个字段但它不是主键口诀可以这样记2NF 处理“只依赖半把钥匙”的属性3NF 处理“转了两手才到主键”的属性BCNF 处理“手里没钥匙却想说了算”的属性。6. 从业务需求到规范表一套可以直接抄的落地流程6.1 六步法把前面所有理论收拢成一套可直接执行的操作流程列出业务对象。把系统里要管理的所有“名词”写出来用户、文章、评论、店铺、商品、订单。写出业务规则。用自然语言写清楚这些对象之间的一对一、一对多、多对多关系。推导函数依赖。把每条业务规则翻译成箭头重点找出非主属性之间的依赖。设计初步表结构。先按业务直觉建表找出每张表的候选键。逐级检查依赖并分解。对照 2NF、3NF 规则处理部分依赖和传递依赖每次拆完验证无损性。用异常剧本测试。模拟插入、删除、更新三种异常场景确认没有隐藏问题。前面几步是设计期的工作最后一步是验收期的工作。这一步很多人会跳掉但恰恰是它最能暴露表结构的真实问题。6.2 一个完整案例博客系统假设需求是用户可以写多篇文章文章只属于一个用户一篇文章可以有多个评论一条评论只属于一篇文章用户有昵称、邮箱文章有标题、正文评论有内容、评论时间。很多人拿到需求会直接把作者昵称塞进文章表再把文章标题塞进评论表。这样查询确实方便但文章改一次标题所有历史评论都要跟着 UPDATE评论实体被文章属性绑架了。按依赖分析走一遍结果很清晰用户表(user_id, user_name, email)user_id 决定其余字段。文章表(article_id, user_id, title, content)article_id 决定其余字段。评论表(comment_id, article_id, content, comment_time)comment_id 决定其余字段。所有非主属性都直接依赖自己的候选键没有部分依赖也没有传递依赖整体达到 3NF。至于评论表要不要刻意冗余文章标题那是反范式决策必须有同步机制兜底而不是顺手就加。这个例子告诉我们先列规则再建表比一边建表一边想字段清晰得多。依赖清单本身就可以当数据库设计文档用。6.3 什么时候可以反范式反范式不是错错的是无意识冗余。在下面这些场景里冗余反而是设计目标报表查询场景统计每月订单量时订单表如果冗余了用户所在城市就能避免每次聚合都 JOIN 用户表。日志与流水场景数据一次性写入、极少更新冗余字段永远不会触发更新异常。分析型宽表数仓里经常直接把维度属性冗余进事实表这叫维度建模是分析场景的标准做法。关键区别只在一个问题上这个冗余字段未来会不会被 UPDATE。如果会就得谨慎大概率需要拆如果写入后永不修改冗余带来的收益就远大于风险。6.4 三个常见误区误区一字段少就不用拆。设计问题的根子不在字段数量在依赖关系。两张表三个字段也可能存在传递依赖比如(学号, 系名, 系主任)字段少不代表结构健康。误区二拆得越碎越好。拆到 BCNF 不代表完美查询要 JOIN 更多表索引、缓存、网络开销都会变大依赖还可能丢失。绝大多数 OLTP 系统到 3NF 就够了更高的范式留到确有必要再说。误区三范式只在面试用。它其实是排查线上数据不一致事故的高效工具。复盘几步走下来更新异常出现的地方往往就是冗余字段所在顺着依赖链往回找就能定位到当初该拆没拆的那张表。我自己在实际操作中的体会是范式不是拿来背的而是建表前用来自问的三个问题这张表的主键是真正唯一标识一行业务记录吗非主属性里有没有只依赖主键一部分的字段有没有需要跟着另一个字段一起改的冗余属性问三遍基本能挡掉八成返工。最后再分享一个小技巧。任何新表上线前别只测正常流程拿着“异常剧本”过一遍试着插入一条缺少子实体的父实体记录试着删除一条“最后一个子项”的记录试着修改一个被复制了多次的字段。三个动作做完该不该拆答案会自己跳出来。
