Software Systems Atlas

深色模式

第3章：ER 图与数据库设计 ​

元数据卡

维度值
难度（进阶入门）
前置第1章 SQL 基础（CREATE TABLE、JOIN），第2章 关系代数（对关系模型的理解）
关键词ER 图、实体、属性、关系、函数依赖、范式、1NF、2NF、3NF、BCNF
代码语言SQL / ER 图（文本）

你的进度

老陈递给你一张发黄的图纸，纸边已经卷了角，有几道被水渍浸过的痕迹。

"这是上一任仓库管理员留下的蓝图。"老陈说，"他走了之后我才翻出来。你看看，能看出哪里出问题了吗？"

你展开图纸。上面画着一个大仓库的布局——所有货架、通道、出入库登记处都密密麻麻标在一张图上。乍一看很完整，但你仔细看就发现了不对劲的地方：一件宝物的编号重复出现在了三个不同的货架标签上；退货区的位置和入库登记处画在了一起，没有单独的记录台；角落里还画了一个"临时堆放"区，上面没有任何标签，只写着五个字——"下次再分类"。

老陈看着你的表情，点点头："发现问题了吧？这张图看着完整，用起来全是坑。"

数据库设计也一样。你随手 CREATE TABLE，把所有字段塞进一张表——一眼看过去好像没问题，但它已经埋下了重复写入、更新遗漏、删除丢失的隐患。等你上线跑了三个月再来翻修，代价比重新建一套还大。

你需要一套方法，在表建好之前就把这些问题扼杀。这就是数据库设计——ER 图帮你理清实体之间的关系，范式帮你消灭冗余和异常。

别把范式当八股文。当你见过一张 50 列的表、被数据不一致坑过三次之后，你会回来感谢这些"条条框框"——它们不是限制，是护栏。

你的任务

理解实体关系建模的基本概念，学会用 ER 图描述业务中的实体和它们之间的关联（1:1、1:N、M:N）。掌握函数依赖的概念，清楚 1NF→2NF→3NF→BCNF 每一层范式解决的问题和拆分方法。最终，你能把一个"大而全"的表逐步拆成一组干净、无冗余的规范化表。

完工时你会明白：为什么好的数据库设计不是往一张表塞的列越多越好，而是表拆得越合理越好。

破局 · 溯源

问题：一张表放一切 ​

先回到最初那个任务表。假设你管理一个宝库入库系统，现在是这样存的：

sql
CREATE TABLE quest_records (
 quest_id INTEGER PRIMARY KEY
 adventurer_id INTEGER
 adventurer_name TEXT
 contact_rune TEXT
 item_id INTEGER
 item_name TEXT
 item_type TEXT
 value REAL
 quantity INTEGER
 start_date TEXT
 target_location TEXT
);

INSERT INTO quest_records VALUES
(1001, 1, '赤焰', '焰之符印', 201, '龙鳞护盾', '防具', 300, 1, '2025-06-01', '铁壁要塞'),
(1002, 1, '赤焰', '焰之符印', 202, '影遁披风', '防具', 100, 2, '2025-06-01', '铁壁要塞'),
(1003, 2, '寒冰', '霜之印记', 201, '龙鳞护盾', '防具', 300, 1, '2025-06-02', '霜风哨塔'),
(1004, 1, '赤焰', '焰之符印', 203, '魔力晶石', '法器', 2000, 1, '2025-06-03', '铁壁要塞'),
(1005, 3, '疾风', '风之刻印', 204, '风行者之靴', '防具', 320, 1, '2025-06-03', '翠林营地');

现在数数问题：

冗余——赤焰的名字和联络符印重复了 3 次。如果赤焰有 100 次任务，重复 100 次。

更新异常——赤焰换防了。你要把所有任务的 target_location 从"铁壁要塞"改成"霜风哨塔"：

sql
UPDATE quest_records
SET target_location = '霜风哨塔'
WHERE adventurer_id = 1;

如果你记错了 WHERE 条件，只更新了 3 行中的 2 行——那么赤焰的记录里地址就开始不一致了。到底哪个地址是真的？

插入异常——新来了一位冒险者，她还没接任务。但 quest_id 是主键不能为空。你没办法把冒险者信息先存进去。

删除异常——疾风取消了唯一的一次任务。删除 quest_id=1005 之后，系统里再也没有疾风的任何信息。如果你还记录了他的勋章、贡献……全丢了。

所有这些异常，根源相同：一张表里放了两个或更多不同"事物"的信息。冒险者的属性、宝物的属性、任务本身的属性——全混在一起了。

分离它们，就是数据库设计的第一课。

ER 图：先把业务画清楚 ​

老陈把图纸摊平在桌上。「先画清楚再动手——这是老规矩。」

ER 图（Entity-Relationship Diagram）不是数据库表结构。它是业务的抽象描述，独立于任何数据库实现——先把业务摸清楚，再谈怎么存。

三个核心构件 ​

「ER 图就三样东西。」老陈在纸上画了三个圈。

实体（Entity）——现实世界中你能数出来的「东西」。一个实体就是一类对象的集合：冒险者、宝物、任务、职业。

属性（Attribute）——实体的特征。冒险者有什么？名号、符印、驻地。宝物有什么？名称、价值、类型。

关系（Relationship）——实体之间怎么关联。冒险者「接」任务，任务「包含」宝物。

用文本 ER 图来表达：

+---------+ +---------+ +-----------+
| 冒险者 || 任务 || 任务物品 |
+---------+ 1:N +---------+ 1:N +-----------+
 | |
 | |
 | +---------+
 | | 宝物 |
 | +---------+

这不是标准 ER 图符号（矩形=实体、菱形=关系、椭圆=属性），但作为文本表达足够清晰。实际画图时用标准符号。

用标准符号（属性用椭圆包围）：

 冒险者 宝物
 / | \ /|\
 名号 符印 驻地 名称 价值 类型
 | |
 \ /
 \ 下任务（1:N） /
 \ /
 包含（M:N） 
 任务 任务物品 
 
 |
 任务日期 发货地址

ER 图的价值在于：在写第一行 CREATE TABLE 之前，你先想清楚业务中的数据是怎么关联的。

关系基数：1:1、1:N、M:N ​

1:1（一对一）——一个实体恰好关联另一个实体的一个实例。

例：一位守卫对应一个岗哨。一个岗哨也只能分配给一位守卫。

员工 1:1 岗哨

在数据库里，1:1 通常用外键加唯一约束实现——或者直接把一方合并到另一方的表里。

1:N（一对多）——一个实体关联另一个实体的多个实例。

例：一个冒险者有多个任务。但一个任务只属于一个冒险者。

冒险者 1:N 任务

这是最常见的关系类型。在 SQL 里，把"多"的一方加外键指向"一"的一方：

sql
CREATE TABLE quests (
 quest_id INTEGER PRIMARY KEY
 adventurer_id INTEGER REFERENCES adventurers(adventurer_id)
 start_date TEXT
 target_location TEXT
);

M:N（多对多）——一个实体关联另一个实体的多个实例，反之亦然。

例：一个任务包含多种宝物，一种宝物出现在多个任务中。

任务 M:N 宝物

M:N 在数据库里需要一个关联表（也叫连接表、中间表）：

sql
CREATE TABLE quest_items (
 quest_id INTEGER REFERENCES quests(quest_id)
 item_id INTEGER REFERENCES vault_items(item_id)
 quantity INTEGER
 PRIMARY KEY (quest_id item_id)
);

这是 ER 建模中最重要的一步：识别出 M:N 关系，插入关联表。如果忽略它，你又会回到"一张表塞所有东西"的老路。

函数依赖：数据之间的"如果……那么……" ​

ER 图解决"实体之间怎么关联"，但没解决"单个表里哪些列依赖哪些列"。这需要函数依赖来分析。

「函数依赖不是 SQL 语法，是数据本身的性质。」老陈写下 X → Y。

如果两行有相同的 X 值，那么它们的 Y 值必须相同。

看任务表：

adventurer_id → adventurer_name
adventurer_id → contact_rune
quest_id → adventurer_id
quest_id → start_date
item_id → item_name item_value
(quest_id item_id) → quantity

读作：

  • 知道了 adventurer_id，就能推导出 adventurer_name——同一个冒险者不可能有两个不同的名字
  • 知道了 quest_id，就能推出 start_date——同一个任务只有一个任务日期
  • 知道了 (quest_id item_id)，就能推出 quantity——是哪个宝物以及取了几件

这些依赖关系就是你在拆表时必须满足的约束。

**范式（Normal Form）**说白了就是：给定一组函数依赖，你的表设计是否优雅？是否避免了冗余和异常？

再往下一步，主键和候选键：

  • 超键：能唯一标识一行的一个或多个属性的组合
  • 候选键：最小的超键（去掉任一属性就不再是超键）
  • 主键：从候选键中选一个作为主要标识

在上面的任务表里，quest_id 是候选键，quest_id 也是主键。对 quest_items 表，单独的 quest_id 或 item_id 都不是超键——必须两者组合才是主键（复合主键）。

深入冒险（核心）

范式：一步一步把表拆干净 ​

老陈翻回第一张图。「你从最乱的表出发，一步一步拆到干净。每往下一层，消除一类异常。别急着一次到位。」

1NF：每一格只能放一个值 ​

「第一范式最简单。」老陈拍了拍桌上的物品登记表。「每个格子里只能放一个值，不能塞列表。」

1NF 的要求：每个属性必须是原子的，不能有嵌套。

违反 1NF 的例子：

quest_iditems
1001"龙鳞护盾 影遁披风"
1002"龙鳞护盾"
1003"魔力晶石 风行者之靴 龙骨法杖"

items 列里塞了一个列表——这违反了 1NF。对策是把 items 拆开：

quest_idreward_type
1001龙鳞护盾
1001影遁披风
1002龙鳞护盾
1003魔力晶石
1003风行者之靴
1003龙骨法杖

现在每一行在 reward_type 列只有一个值。这不是一个"好看"的数据库设计（因为数据重复了），但它符合 1NF。

1NF 是底线：不在一个字段里塞多个值。JSON 字段在某些场景有用，但它不是第一范式——如果你用 JSON 存列表，就要想清楚你在放弃什么。

再普适一点：假如你的 quests 表有以下设计：

sql
-- 违反 1NF：contact_runes 列里有多个符印
CREATE TABLE adventurers (
 adventurer_id INTEGER PRIMARY KEY
 name TEXT
 contact_runes TEXT -- "焰之符印 霜之印记"
);

-- 符合 1NF：每个号码一行
CREATE TABLE adventurer_runes (
 adventurer_id INTEGER REFERENCES adventurers(adventurer_id)
 contact_rune TEXT
 PRIMARY KEY (adventurer_id contact_rune)
);

「为什么非要这么较真？」你问。

因为如果你在一个字段里塞了多个值，SQL 就无法高效地查询更新单个值。WHERE contact_runes LIKE '%焰之符印%' 这种查询速度极慢，而且当你只想删其中一个符印时，你得读出整个字符串，解析，修改，再写回去。

2NF：消除部分依赖 ​

老陈在「1NF」旁边加了一个箭头。「第二范式更细一点。你要是用了复合主键，那所有非主键列都得依赖整个主键，不能只依赖其中一部分。」

2NF 条件：所有非主键属性必须完全依赖于整个主键。

违反 2NF 的表必然有复合主键。看这个 quest_details 表：

sql
-- 假设主键是 (quest_id item_id)
CREATE TABLE quest_details (
 quest_id INTEGER
 item_id INTEGER
 item_name TEXT -- 只依赖 item_id，不依赖 quest_id
 item_value REAL -- 只依赖 item_id，不依赖 quest_id
 quantity INTEGER -- 完全依赖 (quest_id item_id)
 start_date TEXT -- 只依赖 quest_id，不依赖 item_id
 adventurer_name TEXT -- 只依赖 quest_id（假设 adventurer_name 在任务表里）
 PRIMARY KEY (quest_id item_id)
);

检查函数依赖：

属性依赖于
item_nameitem_id（部分依赖）
item_valueitem_id（部分依赖）
quantity(quest_id item_id) 完全依赖
start_datequest_id（部分依赖）
adventurer_namequest_id（部分依赖）

这里的问题是：item_name 只依赖 item_id，而不是整个复合主键。这就叫部分依赖。

部分依赖带来的问题：

  • 如果同一个宝物出现在 10 个任务里，item_name 重复 10 次
  • 要改宝物名，得改 10 行
  • 如果某个宝物没被任何任务引用——它根本不存在于这张表里

解决 2NF 违反：拆表。

quest_details → 拆成三张表
 quests(quest_id start_date adventurer_name) ← 完全依赖 quest_id
 vault_items(item_id item_name item_value) ← 完全依赖 item_id
 quest_items(quest_id item_id quantity) ← 完全依赖 (quest_id item_id)

满足 2NF 之后：

sql
CREATE TABLE quests_2nf (
 quest_id INTEGER PRIMARY KEY
 start_date TEXT
 adventurer_name TEXT
);

CREATE TABLE vault_items_2nf (
 item_id INTEGER PRIMARY KEY
 item_name TEXT
 item_value REAL
);

CREATE TABLE quest_items_2nf (
 quest_id INTEGER REFERENCES quests_2nf(quest_id)
 item_id INTEGER REFERENCES vault_items_2nf(item_id)
 quantity INTEGER
 PRIMARY KEY (quest_id item_id)
);

好，现在 item_name 只存一份了。改一次，所有引用自动生效。宝物信息不再依附于任务是否存在。

3NF：消除传递依赖 ​

老陈又画了一个箭头。「2NF 搞定了，但还不够——非主键列之间也可能有依赖关系。」

3NF 条件：非主键属性之间也不能有依赖关系。否则就产生了传递依赖。

看 quests_2nf 表：

sql
CREATE TABLE quests_2nf (
 quest_id INTEGER PRIMARY KEY
 start_date TEXT
 adventurer_id INTEGER
 adventurer_name TEXT -- adventurer_id → adventurer_name
 contact_rune TEXT -- adventurer_id → contact_rune
 base_location TEXT -- adventurer_id → base_location
);

函数依赖：

  • quest_id → adventurer_id
  • adventurer_id → adventurer_name contact_rune base_location

因此存在传递依赖：

  • quest_id → adventurer_id → adventurer_name

adventurer_name 不直接依赖于 quest_id，而是通过 adventurer_id 传递过去。

传递依赖带来的问题和之前类似：

  • 同一个冒险者有 10 个任务，名号/符印/驻地重复 10 次
  • 冒险者换了符印，要改 10 行——漏了就是数据不一致
  • 还未接任务的冒险者没法插入

解决 3NF 违反：继续拆。

quests_2nf → 拆成两张表
 adventurers(adventurer_id adventurer_name contact_rune base_location)
 quests(quest_id start_date adventurer_id)
sql
CREATE TABLE adventurers_3nf (
 adventurer_id INTEGER PRIMARY KEY
 adventurer_name TEXT
 contact_rune TEXT
 base_location TEXT
);

CREATE TABLE quests_3nf (
 quest_id INTEGER PRIMARY KEY
 start_date TEXT
 adventurer_id INTEGER REFERENCES adventurers_3nf(adventurer_id)
);

现在冒险者信息只存一份。改一次冒险者信息，所有任务自动更新。

对比 2NF 和 3NF：

  • 2NF 解决的是复合主键中只依赖部分主键的问题
  • 3NF 解决的是非主键列之间互相依赖的问题

简单记：2NF 切掉"部分依赖"，3NF 切掉"传递依赖"。

BCNF：3NF 的强化版 ​

BCNF（Boyce-Codd Normal Form）比 3NF 更严格。3NF 允许在某些情况下保留传递依赖（比如主键的依赖关系），BCNF 不允许任何非平凡的函数依赖的左边不是候选键。

3NF 下仍然有问题的例子：

老陈翻出一张旧值班表。「你来看这个——3NF 没拦住的地方，BCNF 来兜底。」

设想一座城门的守卫排班：一位巡逻兵在某座城门上当值时就有一位队长的安排，而一位队长只镇守一座城门。但一座城门可以有多个队长轮值。

sql
CREATE TABLE duty_roster (
 guard TEXT
 gate TEXT
 captain TEXT
 PRIMARY KEY (guard gate)
);

函数依赖：

  • (guard gate) → captain（一个巡逻兵在某座城门上有一位分管队长）
  • captain → gate（一位队长只镇守一座城门）

这里 (guard gate) → captain，而 captain → gate——这就产生了决定因素（captain）不是候选键的情况。BCNF 要求：如果有 X → Y，那么 X 必须是候选键。

这里 captain → gate 成立，但 captain 不是候选键（一位队长可以带多个巡逻兵，但队长不是候选键）。所以违反 BCNF。

BCNF 的分解：

duty_roster → 拆成两张表
 guard_captain(guard captain)
 captain_gate(captain gate)
sql
CREATE TABLE guard_captain (
 guard TEXT
 captain TEXT
 PRIMARY KEY (guard captain)
);

CREATE TABLE captain_gate (
 captain TEXT PRIMARY KEY
 gate TEXT
);

BCNF 和 3NF 的区别微妙：3NF 允许"主键决定属性，属性决定主键的一部分"这种循环，BCNF 不允许。在大多数实际场景里，达到 3NF 已经足够好——BCNF 解决的是更隐蔽的设计缺陷。

完整案例：任务系统从零到 3NF ​

老陈把那张发黄的图纸推到中间。「还记得开头那张一表打天下的 quest_records 吗？现在从头拆一遍。」

原始表（不符合任何范式）：

sql
CREATE TABLE quest_records (
 quest_id INTEGER PRIMARY KEY
 adventurer_name TEXT
 contact_rune TEXT
 base_location TEXT
 item_name TEXT
 item_type TEXT
 value REAL
 quantity INTEGER
 start_date TEXT
 target_location TEXT
);

问题清单：

  • adventurer_name/contact_rune/base_location 重复
  • item_name/item_type/value 重复
  • 改冒险者信息麻烦
  • 改宝物信息麻烦
  • 新冒险者/新宝物没法插入
  • 删任务丢失冒险者信息

第 1 步：达到 1NF

每一格只有一个值——当前表已经满足了。

第 2 步：识别函数依赖

quest_id → adventurer_name contact_rune base_location start_date target_location
item_id(?) → item_name item_type value -- 等等，这里没有 item_id！
(quest_id item_id) → quantity -- 等一下，也没有 item_id！

发现问题了：当前表没有 item_id。如果两件不同的宝物都叫"键盘"，你怎么区分？这说明当前设计的问题比想象的更深——宝物没有独立标识。

先加 item_id：

sql
CREATE TABLE quest_records_v2 (
 quest_id INTEGER
 item_id INTEGER -- 新增
 adventurer_name TEXT
 contact_rune TEXT
 base_location TEXT
 item_name TEXT
 item_type TEXT
 value REAL
 quantity INTEGER
 start_date TEXT
 target_location TEXT
 adventurer_id INTEGER -- 也加上冒险者标识
 PRIMARY KEY (quest_id item_id)
);

现在函数依赖清晰了：

adventurer_id → adventurer_name contact_rune base_location
item_id → item_name item_type value
quest_id → adventurer_id adventurer_name contact_rune base_location start_date target_location
(quest_id item_id) → quantity

第 3 步：达到 2NF（消除部分依赖）

item_name、value 只依赖 item_id（部分依赖），start_date 只依赖 quest_id（部分依赖）。

拆成：

sql
-- 任务基本信息
CREATE TABLE quests (
 quest_id INTEGER PRIMARY KEY
 adventurer_id INTEGER REFERENCES adventurers(adventurer_id)
 start_date TEXT
 target_location TEXT
);

-- 宝物信息
CREATE TABLE vault_items (
 item_id INTEGER PRIMARY KEY
 item_name TEXT
 item_type TEXT
 value REAL
);

-- 任务物品
CREATE TABLE quest_items (
 quest_id INTEGER REFERENCES quests(quest_id)
 item_id INTEGER REFERENCES vault_items(item_id)
 quantity INTEGER
 PRIMARY KEY (quest_id item_id)
);

第 4 步：达到 3NF（消除传递依赖）

adventurer_id → adventurer_name contact_rune base_location 是传递依赖（quest_id → adventurer_id → adventurer_name）。

sql
-- 冒险者信息（分离出来）
CREATE TABLE adventurers (
 adventurer_id INTEGER PRIMARY KEY
 adventurer_name TEXT
 contact_rune TEXT
 base_location TEXT
);

-- 任务（通过外键引用冒险者）
CREATE TABLE quests (
 quest_id INTEGER PRIMARY KEY
 adventurer_id INTEGER REFERENCES adventurers(adventurer_id)
 start_date TEXT
 target_location TEXT
);

-- 宝物
CREATE TABLE vault_items (
 item_id INTEGER PRIMARY KEY
 item_name TEXT
 item_type TEXT
 value REAL
);

-- 任务物品
CREATE TABLE quest_items (
 quest_id INTEGER REFERENCES quests(quest_id)
 item_id INTEGER REFERENCES vault_items(item_id)
 quantity INTEGER
 PRIMARY KEY (quest_id item_id)
);

现在对比一下最初的一张表 vs 4 张规范化表：

原始一张表规范化 4 表
存储空间大量重复，数据膨胀只存一份，空间节省
更新冒险者符印更新 N 行，可能不一致更新 1 行
修改宝物价值更新 N 行更新 1 行
插入新冒险者不行（任务主键问题）INSERT
插入新宝物不行INSERT
删任务丢失冒险者会丢只删任务
查询复杂度一张表搞定需要 JOIN

「代价是查询多了 JOIN。」老陈在对比图下面画了个箭头。「但这是好事情——冗余换 JOIN，永远值得。」

反规范化：什么时候该回到多存一份 ​

「但别把范式当圣经。」老陈收起图纸。「有时候你故意多存一份，反而更快——这叫反规范化，不是偷懒，是有选择。」

反规范化不是随便放的。老陈数了几个典型场景：

  1. 报表/数据仓库：查询量大，冗余一些统计数据避免实时计算
  2. 高频读取的缓存字段：在任务表里直接存 adventurer_name，省去每次都 JOIN
  3. 统计列：在 quests 表里存 quest_count 字段，用触发器或应用层维护

但反规范化有明确的前提：你知道冗余存在，并且有机制保证一致性。不是"我懒得拆表"的借口。

sql
-- 反规范化示例：在任务表里冗余冒险者名号
-- 好处：查任务详情时少一次 JOIN
-- 坏处：冒险者改名号时，所有历史任务的 adventurer_name 仍然显示旧名号
-- 如果你的场景需要"任务快照"（任务生成时的冒险者名号），反规范化反而是正确的

CREATE TABLE quests_denormalized (
 quest_id INTEGER PRIMARY KEY
 adventurer_id INTEGER
 adventurer_name TEXT -- 反规范化：冗余存储
 contact_rune TEXT -- 反规范化
 start_date TEXT
 target_location TEXT
);

反规范化不是"打破规则"，是明确地选择另一个设计目标。你想清楚你在优化什么、牺牲什么之后，可以这么做。但从没做过规范化的人没资格谈反规范化。

常见陷阱

陷阱 1：过于追求完美范式

「范式不是越高级越好。」老陈敲了敲桌面。你的表到了 BCNF，但你的业务查询要 JOIN 8 张表，性能惨不忍睹。这不是范式的问题，是你忘了检查查询模式。好的设计方法是：先按 3NF 设计，然后对照关键查询做性能评估，在明确痛点的地方反规范化。

3NF 是起点，不是终点。OLTP 系统以范式为主，OLAP 数据仓库以反范式为主。不同场景有不同的平衡点。

陷阱 2：忽略业务语义的函数依赖

「函数依赖不能光看数据样本。」老陈画出三行记录。「有时候数据量少，会表现出巧合的依赖。」：

gate_codelocation
100001铁壁要塞
200001霜风哨塔
300001翠林营地

从这三行看，gate_code → location 成立。但 location → gate_code 也成立（每个地点只出现了一次）。实际情况是：一个驻地可能有多个城门编号。要结合业务知识来判断。

陷阱 3：1:1 关系错误

sql
-- 把守卫和岗哨的 1:1 关系做成两张表，各自外键
CREATE TABLE guardians (
 guardian_id INTEGER PRIMARY KEY
 post_id INTEGER UNIQUE -- 每位守卫最多分配一个岗哨
);

CREATE TABLE posts (
 post_id INTEGER PRIMARY KEY
 guardian_id INTEGER UNIQUE -- 每个岗哨最多分配一个人
);

「你拿两张大表存一对一的关系，纯粹跟自己过不去。」老陈把这行 SQL 划掉了。「1:1 关系通常可以合并到一张表里。」

sql
CREATE TABLE guardians (
 guardian_id INTEGER PRIMARY KEY
 post_number TEXT -- 直接合并岗哨信息
);

除非你确定"岗哨属性远多于守卫信息，有些岗哨还需要单独管理（设施巡检记录、库存清单等）"，否则没必要两表。

陷阱 4：M:N 关系不建中间表

sql
-- 错误的 M:N 处理
CREATE TABLE quests (
 quest_id INTEGER PRIMARY KEY
 item_ids TEXT -- "201202203"
);

-- 或者
CREATE TABLE quests (
 quest_id INTEGER PRIMARY KEY
 item_id_1 INTEGER
 item_id_2 INTEGER
 item_id_3 INTEGER
);

老陈皱了皱眉。「两种都是反模式。第一种违反了 1NF，第二种限制了宝物数量。中间表才是正解——就是前面说的 quest_items。」

通关挑战

** 热身（10 分钟）**

给以下关系找出函数依赖，并判断它处于第几范式：

  1. recruits(rid name squad squad_barracks)
  2. quest_enrollments(rid tid grade tid_name tid_credits)
  3. library_scrolls(scroll_id title author shelf floor)
提示

以第二题为例（用堡垒世界的暗语说就是「学徒-试炼注册表」）：

  • (rid tid) → grade（完全依赖）
  • tid → tid_name tid_credits（部分依赖）
  • 这是 1NF——因为存在部分依赖 (tid 单独决定 title 和 credits)

修复：拆成 quest_enrollments(rid tid grade) 和 trials(tid title credits)

** 挑战（30 分钟）**

设计一个宝库进销存系统（keepers + merchants + vault_items + acquisition_logs）。

初始需求：

  • 记录商人信息（名称、联络符印、管辖区域）
  • 记录宝物信息（名称、类型、入库价、出库价）
  • 记录入库单（哪位保管员经手、从哪位商人、日期）
  • 每张入库单可以包含多种宝物
  • 宝物价值可能会变，但入库历史里的价值不能变
  1. 画 ER 图（文本形式）
  2. 写出 3NF 的 CREATE TABLE 语句
  3. 标注每个表上的函数依赖
参考设计方案
keepers 1:N acquisition_logs 1:N acquisition_items N:1 vault_items
merchants 1:N acquisition_logs
sql
CREATE TABLE keepers (
 keeper_id INTEGER PRIMARY KEY
 name TEXT
 post TEXT
);

CREATE TABLE merchants (
 merchant_id INTEGER PRIMARY KEY
 name TEXT
 contact_rune TEXT
 territory TEXT
);

CREATE TABLE acquisition_logs (
 log_id INTEGER PRIMARY KEY
 keeper_id INTEGER REFERENCES keepers(keeper_id)
 merchant_id INTEGER REFERENCES merchants(merchant_id)
 log_date TEXT
);

CREATE TABLE vault_items (
 item_id INTEGER PRIMARY KEY
 name TEXT
 item_type TEXT
 current_value REAL
);

CREATE TABLE acquisition_items (
 log_id INTEGER REFERENCES acquisition_logs(log_id)
 item_id INTEGER REFERENCES vault_items(item_id)
 quantity INTEGER
 unit_value REAL -- 采购时的实际进价，不依赖 vault_items.current_value
 PRIMARY KEY (log_id item_id)
);

注意 unit_value 字段：为什么不在 acquisition_items 里用 vault_items.current_value？因为历史入库价值不能变。反规范化在这里不是问题——它是正确的设计，因为 unit_value 依赖的是入库记录本身，不是宝物。

** 观察（15 分钟）**

用真实的 SQLite 实践——把你一开始的 quest_records 表和规范化后的 4 张表都建出来：

  1. 插入同样的数据
  2. 实验更新一个冒险者符印：两条 SQL 的执行效果有何不同
  3. 执行一条查询"查赤焰的所有任务详情"——比较原始表和规范化表的 SQL 写法复杂度
  4. 插入一个新冒险者（未接任务）——哪张表可以，哪张表不行？

「别光看。」老陈把键盘推到你面前。「亲手建一次表，更新一次数据，你就知道范式到底在解决什么问题。」

验收标准

到这一步你应该能：

  • 画出包含实体、属性、关系基数（1:1/1:N/M:N）的 ER 图
  • 分析出给定关系中的函数依赖
  • 把一张不合范式的表逐步拆到 3NF，每一步解释解决了什么问题
  • 说清楚 1NF、2NF、3NF、BCNF 的区别和递进关系
  • 理解反规范化在什么场景下合理

常见卡点

卡点原因解决
"部分依赖和传递依赖分不清"2NF 和 3NF 看起来很像2NF 针对复合主键中的部分属性，3NF 针对非主键属性之间的传递
"M:N 关系不知道在哪方加外键"M:N 不能在任意一方加单字段外键M:N 必须通过中间表拆成两个 1:N
"ER 图画出来和 SQL 表结构一样"混淆了概念模型和实现模型ER 图是概念设计（独立于 DBMS），SQL 是物理实现。有时一个实体对应多张表，有时多实体合一张表
"分不清 BCNF 和 3NF"边界太微妙一般场景达到 3NF 就够了。BCNF 解决的是"决定因素不是候选键"的情形，你的面试题会考，但普通项目不常见
"反规范化之后又出了数据不一致"没有同步机制反规范化必须配合触发器、应用层逻辑或定时同步来保证一致性

现在不需要理解

  • 外键约束的实现原理：FK 在数据库里是怎么保证引用完整性的？索引如何提升 FK 检查性能？那是第 4 章 B+树的内容
  • ER 图的扩展建模：弱实体、泛化/特化、聚合这些 ER 图的高级概念在第 3 章之后才用到
  • 第四范式（4NF）与多值依赖：现实场景很少涉及，学术界探讨为主
  • 规范化与查询优化器的交互：用 VIEW 把拆开的表重新拼回去——这涉及到视图展开和查询重写（第 8 章）

旅人笔记

一张表放所有东西看起来"简单"，但每次写入和更新都在制造数据不一致的种子。ER 图帮你理清实体之间的关系，范式保证每份数据只存一次——冒险者信息在 adventurers 表，宝物信息在 vault_items 表，任务只是一个关联。3NF 是大多数 OLTP 系统的甜区。

→ 下一站预告 ​

Built with VitePress | Software Systems Atlas

Copyright 2026 Steven Yang