主题
界面
数据库范式与表关联设计
1. 本节定位
《表设计与约束》 讲"一张表怎么建"，本节讲"多张表怎么组织"。
这是表设计里最容易出错的地方——同一份业务数据，可以有多种表结构方案，但只有少数几种是"不会越用越乱"的。
2. 学习目标
完成本节后，你应该能够：
- 说出三种关系（一对一、一对多、多对多）的表结构做法；
- 用"库存放哪一侧"迅速判断一对多的方向；
- 说出三大范式的实际含义（不是背定义，而是能判断"这张表违不违反"）；
- 判断什么时候该故意违反范式（反范式），并说出代价；
- 写出三种关系的关联查询。
3. 前置知识检查
4. 为什么需要这个知识
表结构是"改起来最贵"的东西：
| 改动 | 成本 |
|---|---|
| 改一行业务代码 | 分钟级，发版即生效 |
| 改查询 SQL | 分钟级 |
| 加一个字段 | 需要迁移脚本、要兼容老代码 |
| 改表关系 | 要迁数据、要改所有相关查询、有数据丢失风险 |
所以"表设计"是一次性成本最高、但收益最持久的决策。本节的目标是让你在设计阶段就少犯错。
5. 核心概念
5.1 三种关系与表结构（本节核心）
一对多（最常见）
规则：外键放在"多"的那一侧。
text
用户（一） ──────< 订单（多）sql
CREATE TABLE app_user (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(32) NOT NULL UNIQUE
);
CREATE TABLE shop_order (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL, -- 外键在"多"方
order_no VARCHAR(32) NOT NULL UNIQUE,
CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES app_user (id)
);为什么外键在"多"方：一个用户有多个订单，账要记在订单上。反过来（在用户表里存"订单列表"）就没法用一列表达了。
判断技巧：问自己"这个关系里，谁会有多条记录"——有"多条"的那一方，就要持有"一"那一方的主键。
多对多
规则：必须有一张中间表。
text
订单（多） ────< 订单明细 >──── 商品（多）sql
CREATE TABLE order_item (
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
unit_price_in_cents INT NOT NULL, -- 下单时的单价（快照）
PRIMARY KEY (order_id, product_id), -- 复合主键
CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES shop_order (id),
CONSTRAINT fk_item_product FOREIGN KEY (product_id) REFERENCES product (id)
);四个设计要点：
| 要点 | 说明 |
|---|---|
| 复合主键 | (order_id, product_id) 表示"同一订单里同一商品只有一条明细" |
| 中间表可以有业务字段 | quantity 是关系的属性（不是订单的、也不是商品的） |
| 价格要快照 | 下单时的单价必须存下来——商品改价后，历史订单金额不能跟着变 |
| 别用自增 ID 当唯一主键 | 复合主键（或复合唯一约束）才能表达"不重复"的业务语义 |
"价格快照"这一点极其重要，它是"反范式"最正当的理由之一（见 5.3）：
sql
-- 错误做法：订单金额靠 JOIN 商品表实时算
SELECT SUM(i.quantity * p.price_in_cents) -- 商品一涨价，历史订单金额就变了 ✗
FROM order_item i JOIN product p ON p.id = i.product_id;
-- 正确做法：下单时把单价存进明细
SELECT SUM(i.quantity * i.unit_price_in_cents) -- 历史金额永远不变 ✓
FROM order_item i;一对一
两种做法：
| 做法 | 说明 | 适用 |
|---|---|---|
| 共享主键 | 从表的主键同时是外键 | 关系必然存在（如用户 → 用户设置） |
| 唯一外键 | 从表有独立主键，外键加 UNIQUE | 关系可选（如用户 → 档案） |
sql
-- 共享主键：user_profile.user_id 既是主键也是外键
CREATE TABLE user_profile (
user_id BIGINT PRIMARY KEY,
city VARCHAR(32),
birthday DATE,
CONSTRAINT fk_profile_user FOREIGN KEY (user_id) REFERENCES app_user (id)
);为什么要把一对一拆成两张表（而不是放一张表里）：
| 理由 | 说明 |
|---|---|
| 字段访问频率差异大 | 常用字段在主表，少用的大字段（头像、简介）拆出去，减少主表行大小 |
| 可空性不同 | 主表字段都必填，扩展信息很多可空 |
| 权限不同 | 敏感信息（身份证）单独一张表，单独授权 |
如果业务上就是一张表——就不要拆。拆表会增加 JOIN 成本。拆分的理由必须是"上面三条之一"，而不是"看起来整齐"。
5.2 三大范式（用例子判断）
范式不是"必须遵守的规则"，而是"减少数据冗余的经验总结"。 用例子理解：
第一范式（1NF）：字段不可再分
sql
-- 违反：一个字段里塞了多个值
CREATE TABLE orders (tags VARCHAR(100)); -- '电子, 促销, 加急'问题：想查"所有加急订单"时只能用 LIKE '%加急%'——无法用索引、也无法准确匹配（"加急"与"不加急"都会命中）。
修复：拆成关联表，或每行一个标签。
第二范式（2NF）：非主键字段完全依赖主键
sql
-- 违反：用 (order_id, product_id) 当主键，但 product_name 只依赖 product_id
CREATE TABLE order_item (
order_id BIGINT,
product_id BIGINT,
product_name VARCHAR(50), -- 只跟 product_id 有关 ✗
quantity INT,
PRIMARY KEY (order_id, product_id)
);问题：同一商品在 1000 个订单里出现，名字就存了 1000 遍——改一次名字要改 1000 行，还可能出现不一致。
修复：product_name 放到 product 表，通过 product_id 关联取。
注意：这个例子正好是"该违反"的场景——如果 product_name 是下单时的商品名快照，那它就应该留在这里（与"价格快照"同理）。关键在于它是"当前值"还是"快照值"。
第三范式（3NF）：非主键字段不依赖其他非主键字段
sql
-- 违反：city 依赖 zip_code，而 zip_code 依赖 id（传递依赖）
CREATE TABLE app_user (
id BIGINT PRIMARY KEY,
zip_code VARCHAR(10),
city VARCHAR(32) -- 通过 zip_code 才能确定 ✗
);问题：同一个邮编的用户，city 存了很多遍；改一次要改一批。
修复：把 zip_code → city 的映射单独建一张表。
判断技巧：如果 A 字段变了，B 字段也必须跟着变，那 B 就不该和 A 放一起（除非 B 是快照）。
5.3 反范式：什么时候故意违反
范式减少冗余，但冗余不一定坏。三种正当的"反范式"场景：
| 场景 | 例子 | 理由 |
|---|---|---|
| 快照 | 订单明细存下单时的单价与商品名 | 历史数据不能随主数据变化 |
| 性能 | 订单表冗余 user_nickname，列表页不用 JOIN | 高频查询，冗余值极少变化 |
| 报表 | 单独的统计表（日订单量） | 实时聚合太慢，用定时任务预计算 |
反范式的代价（必须认账）：
- 一致性风险：冗余值可能与源数据不一致（必须靠代码保证同步）；
- 更新成本：修一次名字要更新多处；
- 写入放大：每次写主表可能要多写几处。
判断标准：
这个字段是"当前值"还是"快照值"？
- 快照值 → 必须冗余（而且永远不要同步更新）
- 当前值 → 默认不冗余；只有"读远多于写、且能保证同步"时才冗余
5.4 关联查询的标准写法
| 关系 | 查询方式 | 例子 |
|---|---|---|
| 一对一 | LEFT JOIN（可能没有档案） | 用户 + 档案 |
| 一对多 | LEFT JOIN + GROUP BY（要"含 0 个"的） | 用户 + 订单数 |
| 一对多（仅有的） | INNER JOIN | 只看有订单的用户 |
| 多对多 | 两次 JOIN（中间表 + 另一端） | 订单 + 商品明细 |
| 多对多（反向） | 从另一端 LEFT JOIN 中间表 | 商品 + 被下单总数 |
一个高频错误的提醒：
sql
-- 想统计"每个用户的订单数"，但用了 INNER JOIN
SELECT u.username, COUNT(*) FROM app_user u
JOIN shop_order o ON o.user_id = u.id
GROUP BY u.id, u.username;
-- 没有订单的用户直接消失了 ✗（内连接把左表没匹配的行过滤掉了）改成 LEFT JOIN ✓ 但要注意用 COUNT(o.id) 而不是 COUNT(*)（《SQL 基础》 的练习里讲过）。
6. 递进示例
6.1 最小示例：一对多
sql
CREATE TABLE shop_order (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES app_user (id)
);6.2 业务示例：多对多 + 价格快照
见 5.1 节的 order_item。
6.3 反例：用"逗号分隔的字符串"表达多对多
sql
-- 反例：一列里塞多个 id
CREATE TABLE article (
id BIGINT PRIMARY KEY,
tag_ids VARCHAR(200) -- '1,5,9'
);它带来的问题（每一条都真实存在）：
| 问题 | 说明 |
|---|---|
| 查不了 | "查出带标签 5 的文章" 只能用 LIKE '%5%'——会误命中 15、25、50 |
| 索引失效 | LIKE '%...%' 用不上索引 |
| 改不了 | 删一个标签要读出来、拆分、删、再拼回去（并发下必然出错） |
| 约束不了 | 无法用外键保证"这些 id 真实存在" |
修复：建 article_tag(article_id, tag_id) 中间表 ✓
7. 完整可运行代码
文件：labs/sql-basics/src/main/java/cn/kb/learn/sql/RelationshipLesson.java（节选）
java
public class RelationshipLesson {
public static void main(String[] args) {
try (SqlRunner db = SqlRunner.open()) {
// 一对多的"一"方 + 一对一的"一"方
db.execute("""
CREATE TABLE app_user (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(32) NOT NULL UNIQUE,
nickname VARCHAR(32) NOT NULL
)
""");
// 一对一：共享主键
db.execute("""
CREATE TABLE user_profile (
user_id BIGINT PRIMARY KEY,
city VARCHAR(32),
birthday DATE,
CONSTRAINT fk_profile_user FOREIGN KEY (user_id) REFERENCES app_user (id)
)
""");
// 一对多的"多"方
db.execute("""
CREATE TABLE shop_order (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
order_no VARCHAR(32) NOT NULL UNIQUE,
CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES app_user (id)
)
""");
// 多对多：中间表（复合主键 + 价格快照）
db.execute("""
CREATE TABLE order_item (
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
unit_price_in_cents INT NOT NULL,
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES shop_order (id),
CONSTRAINT fk_item_product FOREIGN KEY (product_id) REFERENCES product (id)
)
""");
// 一对多查询：每个用户的订单数（含 0）
System.out.print(db.query("""
SELECT u.username, COUNT(o.id) AS order_count
FROM app_user u
LEFT JOIN shop_order o ON o.user_id = u.id
GROUP BY u.id, u.username
ORDER BY u.id
"""));
// 多对多查询：订单明细
System.out.print(db.query("""
SELECT o.order_no, p.name AS product_name, i.quantity,
i.quantity * i.unit_price_in_cents AS amount_in_cents
FROM order_item i
JOIN shop_order o ON o.id = i.order_id
JOIN product p ON p.id = i.product_id
ORDER BY o.order_no, p.name
"""));
// 反范式：冗余用户名到订单表
db.execute("ALTER TABLE shop_order ADD COLUMN user_nickname VARCHAR(32)");
db.execute("""
UPDATE shop_order o
SET user_nickname = (SELECT u.nickname FROM app_user u WHERE u.id = o.user_id)
""");
}
}
}运行命令：
powershell
cd labs
./mvnw -pl sql-basics compile exec:exec -Dexec.mainClass=cn.kb.learn.sql.RelationshipLesson预期输出（节选关键部分）：
text
=== 3. 一对一查询：用户 + 档案 ===
username | nickname | city | birthday
----------------------------------------
alice | 爱丽丝 | 上海 | 1995-03-12
bob | 鲍勃 | 北京 | 1998-11-02
（共 2 行）
=== 4. 一对多查询：每个用户的订单数 ===
username | order_count
-----------------------
alice | 2
bob | 1
（共 2 行）
=== 5. 多对多查询：订单明细（含商品名与金额） ===
order_no | product_name | quantity | unit_price_in_cents | amount_in_cents
---------------------------------------------------------------------------
ORD-1001 | 键盘 | 1 | 19900 | 19900
ORD-1001 | 鼠标 | 2 | 9900 | 19800
ORD-1002 | 显示器 | 1 | 89900 | 89900
ORD-1003 | 键盘 | 1 | 19900 | 19900
（共 4 行）
=== 6. 多对多反向：每个商品被下单的总数量 ===
name | total_quantity
----------------------
键盘 | 2
鼠标 | 2
显示器 | 1
（共 3 行）
=== 7. 反范式：在订单表里冗余用户名 ===
order_no | user_id | user_nickname
-----------------------------------
ORD-1001 | 1 | 爱丽丝
ORD-1002 | 1 | 爱丽丝
ORD-1003 | 2 | 鲍勃
（共 3 行）
冗余后查询订单不再需要 JOIN；代价是用户名变更时必须同步更新订单表四处值得对照：
| 输出 | 含义 |
|---|---|
第 5 步两次 JOIN | 多对多查询必须穿过中间表：order_item → shop_order + product |
第 6 步 LEFT JOIN + COALESCE | "从未被下单的商品"也要显示（数量为 0） |
| 第 7 步冗余后订单表有 3 行、2 个不同用户名 | 冗余值的维护成本：改一次昵称要更新 2 行 |
unit_price_in_cents 在明细表中 | 价格快照：商品改价不影响历史订单 |
8. Debug 观察点
- 在
order_item的PRIMARY KEY (order_id, product_id)上做实验：插入两条相同的(1, 1)，观察被复合主键拒绝——这就是"中间表防重复"的机制； - 在第 4 步的
LEFT JOIN处改成JOIN，观察"没有订单的用户"是否消失——体会两种 JOIN 的语义差别； - 在第 5 步的查询里把
i.unit_price_in_cents改成p.price_in_cents（关联商品表取当前价），然后修改product表里键盘的价格，重跑——观察历史订单金额跟着变了 ✓ 亲手验证"为什么必须做价格快照"； - 观察
user_profile的user_id：它既是主键又是外键——一次声明实现了"一对一 + 非空 + 唯一"三重约束。
9. 常见错误
错误 1：一对多把外键放错侧
sql
-- 反例：在"一"方存"多"方的 id
CREATE TABLE app_user (
id BIGINT PRIMARY KEY,
order_id BIGINT -- 一个用户只能存一个订单 ✗
);修复：外键放"多"方（shop_order.user_id）。
错误 2：多对多用逗号分隔字符串
见 6.3 节。修复：建中间表。
错误 3：订单金额靠实时 JOIN 计算
见 5.1 节。修复：下单时把价格与商品名快照到明细表。
错误 4：为了"范式"拆表拆过头
sql
-- 反例：把"省份"也拆成一张表 + 一个外键
CREATE TABLE province (id BIGINT PRIMARY KEY, name VARCHAR(20));
ALTER TABLE app_user ADD COLUMN province_id BIGINT;问题：省份几乎不变，拆表带来的 JOIN 成本（每一行都要关一次）远大于"省几百字节"的收益——除非你要做"省份维表"（做统计、做校验）。
修复：判断标准是"这张维表有没有其他业务价值"（如统计、下拉框数据源）。
错误 5：忘了中间表的复合主键
sql
CREATE TABLE order_item (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 只用了自增主键
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL
-- 没有 (order_id, product_id) 的唯一约束 ✗
);后果：同一订单里同一商品可能出现两条明细（数量分散在两条里），统计时容易算错。
修复：加 UNIQUE (order_id, product_id)（如果业务允许同商品多行——比如不同规格——那就把规格加进唯一约束）。
错误 6：中间表缺少业务字段
sql
CREATE TABLE user_role (
user_id BIGINT,
role_id BIGINT,
PRIMARY KEY (user_id, role_id)
);看起来没问题，但业务上往往还需要：
| 缺失字段 | 后果 |
|---|---|
granted_at | 不知道何时授权 |
granted_by | 不知道谁授的权（审计需要） |
expires_at | 无法做"临时权限" |
修复：中间表是"关系的实体"，它可以有自己的属性——设计时问一句"这段关系本身有没有需要记录的信息"。
10. 动手实验
目标：为一个小业务设计完整的表结构。
步骤：
- 运行第 7 节的命令，核对输出；
- 做一个实验：把第 5 步查询里的
i.unit_price_in_cents换成p.price_in_cents，然后更新商品价格，观察历史订单金额变化——写下你的结论； - 设计一个"博客"的表结构：用户、文章、标签、评论（提示：文章↔标签是多对多，需要中间表）；
- 给第 3 步的设计写三条查询：a) 每篇文章的评论数；b) 每个标签下的文章数；c) 某篇文章的所有标签；
- 找一处该反范式的地方：在文章列表页要显示作者昵称，你会怎么设计？说明理由；
- 检查你的设计：有没有"逗号分隔的字段"？有没有"该快照却存了引用"的字段？
验收方式：能说出第 2 步的结论，以及第 5 步你是"冗余"还是"JOIN"，为什么。
扩展任务：给 order_item 加上"商品名快照"字段，并写一个测试证明：商品改名后，历史订单里显示的商品名不变。
11. 练习题
基础题
- 判断：一对多关系中，外键应该放在"一"的那一侧。（对 / 错）
- 填空：多对多关系必须有一张 ______ 表；订单明细中的价格属于 ______ 值，必须存下来。
- 简答：为什么订单明细里要存"下单时的单价"，而不是靠 JOIN 商品表实时算？
代码阅读题
sql
CREATE TABLE article (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
author_id BIGINT NOT NULL,
tag_ids VARCHAR(200)
);- 这张表违反了什么设计原则？具体会导致哪些问题？
- 给出修改后的表结构（包含必要的关联表）。
编程题
- 为一个"在线课程"系统设计表结构：
- 实体：学生、课程、章节、选课记录；
- 关系：一个学生选多门课（多对多）；一门课有多个章节（一对多）；章节有顺序；
- 要求：
- 写出所有
CREATE TABLE语句（含约束与索引）； - 选课记录需要记录"选课时间"与"学习进度"（提示：这是关系的属性）；
- 写一条查询：列出某个学生的所有课程与进度；
- 写出所有
- 说明：为什么"选课记录"不能直接放在学生表里？
综合任务
- 给一个"电商系统"做表设计并写设计说明：
- 至少 5 张表：用户、商品、商品分类、订单、订单明细；
- 关系：分类与商品是一对多（或一对多？想想"一个商品能否属于多个分类"）；订单与商品是多对多（通过明细）；
- 要求：
- 写出完整 DDL（含约束、索引、注释）；
- 标出每一处"反范式"并说明理由（快照？性能？）；
- 写出 4 条典型查询：商品列表（带分类名）、用户订单列表（带金额）、某商品的历史销量、某分类下的商品数；
- 说明：如果"一个商品可以属于多个分类"，你的设计要怎么改？
- 额外要求：列出这份设计的 3 个"未来可能的痛点"（如分库分表、软删除、多规格商品）。
分级提示
提示 1（方向）：第 4 题的关键是 tag_ids 用逗号分隔存储——查、改、约束三方面都有问题。
提示 2（关键点）：第 6 题的"选课记录"是一个关系实体，它的属性（选课时间、进度）既不属于学生也不属于课程。
提示 3（接近答案）：第 7 题的"商品属于多个分类"需要把"分类-商品"改造成多对多（中间表 product_category），而不是在商品表里放一个 category_id。
12. 参考答案
基础题
- 错。外键放在"多"的那一侧。判断技巧：问"谁会有多条记录"——有"多条"的一方持有"一"方的主键。
- 中间表（关联表）；快照值。
- 因为商品价格是会变的主数据，而订单金额是不该变的历史事实。
sql
-- 用实时 JOIN：商品一涨价，昨天的订单金额也跟着变了 ✗
SELECT SUM(i.quantity * p.price_in_cents) FROM order_item i JOIN product p ON ...;
-- 用快照：历史金额永远不变 ✓
SELECT SUM(i.quantity * i.unit_price_in_cents) FROM order_item i;这不只是"技术问题"，而是"业务正确性问题"：对账、退款、财务统计都依赖"订单金额不会变"。
代码阅读题（第 4、5 题）
第 4 题：违反第一范式（字段不可再分）——tag_ids 里塞了多个值。
具体问题：
| 维度 | 问题 |
|---|---|
| 查询 | "查出带标签 5 的文章" 只能用 LIKE '%5%'——会误命中 15、25；用 LIKE '%,5,%' 又要处理首尾边界 |
| 索引 | LIKE '%...%' 无法使用索引，数据量大后必然全表扫描 |
| 修改 | 删一个标签要"读出来 → 拆分 → 删除 → 拼回去"，并发下会互相覆盖 |
| 约束 | 无法用外键保证这些 id 真实存在——可能出现指向已删除标签的脏数据 |
| 统计 | "每个标签有多少文章" 需要扫全表 + 字符串处理 |
第 5 题：修改后
sql
CREATE TABLE article (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
author_id BIGINT NOT NULL,
CONSTRAINT fk_article_author FOREIGN KEY (author_id) REFERENCES app_user (id)
);
CREATE TABLE tag (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(32) NOT NULL UNIQUE
);
-- 中间表：多对多
CREATE TABLE article_tag (
article_id BIGINT NOT NULL,
tag_id BIGINT NOT NULL,
PRIMARY KEY (article_id, tag_id),
CONSTRAINT fk_at_article FOREIGN KEY (article_id) REFERENCES article (id) ON DELETE CASCADE,
CONSTRAINT fk_at_tag FOREIGN KEY (tag_id) REFERENCES tag (id)
);修改后的收益：
| 之前的痛点 | 现在 |
|---|---|
LIKE '%5%' 误命中 | WHERE tag_id = 5 精确匹配、可用索引 |
| 改标签要拼字符串 | INSERT / DELETE 一行 |
| 无法约束 | 外键保证 tag 存在 |
| 统计要扫全表 | SELECT tag_id, COUNT(*) FROM article_tag GROUP BY tag_id |
注意 ON DELETE CASCADE 用在这里是合适的：文章被删时，它的标签关联就该一起消失（关联脱离文章没有意义）✓ 这与 《表设计与约束》 里"订单 → 用户不该 CASCADE"形成对比——判断标准是"子记录脱离父记录还有没有意义"。
编程题（第 6 题）
sql
CREATE TABLE student (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(32) NOT NULL UNIQUE,
real_name VARCHAR(32) NOT NULL
);
CREATE TABLE course (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
price_in_cents INT NOT NULL,
CONSTRAINT chk_course_price CHECK (price_in_cents >= 0)
);
CREATE TABLE chapter (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
course_id BIGINT NOT NULL,
title VARCHAR(100) NOT NULL,
seq_no INT NOT NULL, -- 章节顺序
CONSTRAINT fk_chapter_course FOREIGN KEY (course_id) REFERENCES course (id) ON DELETE CASCADE,
CONSTRAINT uk_chapter_seq UNIQUE (course_id, seq_no) -- 同一课程内序号不重复
);
-- 多对多 + 关系的属性
CREATE TABLE enrollment (
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
progress INT NOT NULL DEFAULT 0, -- 学习进度（0-100）
PRIMARY KEY (student_id, course_id),
CONSTRAINT fk_enroll_student FOREIGN KEY (student_id) REFERENCES student (id),
CONSTRAINT fk_enroll_course FOREIGN KEY (course_id) REFERENCES course (id),
CONSTRAINT chk_progress CHECK (progress BETWEEN 0 AND 100)
);
-- 查询：某个学生的所有课程与进度
SELECT c.title,
e.progress,
e.enrolled_at
FROM enrollment e
JOIN course c ON c.id = e.course_id
WHERE e.student_id = 1
ORDER BY e.enrolled_at DESC;为什么"选课记录"不能直接放在学生表里：
| 原因 | 说明 |
|---|---|
| 一对多无法用一列表达 | 一个学生选多门课——学生表里放 course_id 只能表达"一门" |
| 关系的属性无处安放 | enrolled_at、progress 既不属于学生、也不属于课程，只属于"这段选课关系" |
| 查询与统计困难 | "某课程的选课人数"需要扫全表解析字符串（如果硬塞成 '1,2,3'） |
| 无法约束 | 无法保证课程 id 真实存在 |
uk_chapter_seq UNIQUE (course_id, seq_no) 值得注意：它保证了同一课程内章节序号不重复——这就是"复合唯一约束"的正确用法：唯一性只在一组内成立。
综合任务（第 7 题）
设计说明（含反范式标注）：
sql
CREATE TABLE product (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price_in_cents INT NOT NULL,
status VARCHAR(16) NOT NULL DEFAULT 'ON_SALE',
CONSTRAINT chk_product_price CHECK (price_in_cents > 0),
CONSTRAINT chk_product_status CHECK (status IN ('ON_SALE', 'OFF_SHELF'))
);
CREATE TABLE shop_order (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(32) NOT NULL UNIQUE,
user_id BIGINT NOT NULL,
user_nickname VARCHAR(32) NOT NULL, -- 【反范式 1】快照：下单时的昵称
total_in_cents BIGINT NOT NULL, -- 【反范式 2】冗余：订单总额（避免每次 SUM）
status VARCHAR(16) NOT NULL DEFAULT 'CREATED',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE order_item (
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
product_name VARCHAR(100) NOT NULL, -- 【反范式 3】快照：下单时的商品名
unit_price_in_cents INT NOT NULL, -- 【反范式 4】快照：下单时的单价
quantity INT NOT NULL,
PRIMARY KEY (order_id, product_id),
CONSTRAINT chk_item_quantity CHECK (quantity > 0)
);四处反范式的理由：
| # | 字段 | 类型 | 理由 |
|---|---|---|---|
| 1 | user_nickname | 快照 | 订单列表页高频查询，避免 JOIN；且下单后昵称变更不该影响历史订单显示 |
| 2 | total_in_cents | 性能冗余 | 订单列表要显示总额，每次 SUM 明细代价高；写时算好，读时直接取 |
| 3 | product_name | 快照 | 商品改名后，历史订单要显示当时的名字 |
| 4 | unit_price_in_cents | 快照 | 【最重要】商品改价不能影响历史订单金额 |
"一个商品属于多个分类"的改造：
sql
-- 之前（商品表里放 category_id）——只能属于一个分类
ALTER TABLE product ADD COLUMN category_id BIGINT;
-- 改造后：多对多中间表
CREATE TABLE product_category (
product_id BIGINT NOT NULL,
category_id BIGINT NOT NULL,
PRIMARY KEY (product_id, category_id),
CONSTRAINT fk_pc_product FOREIGN KEY (product_id) REFERENCES product (id) ON DELETE CASCADE,
CONSTRAINT fk_pc_category FOREIGN KEY (category_id) REFERENCES category (id) ON DELETE CASCADE
);注意"删除行为"的选择：这里用 CASCADE 是合适的——关联记录脱离两端都没有意义 ✓
三条"未来可能的痛点"：
| 痛点 | 说明 | 缓解方向 |
|---|---|---|
| 订单表数据量膨胀 | 千万级后单表查询变慢 | 按时间分表（order_2026_01）或分区 |
| 软删除与唯一约束冲突 | 用户"删除"后手机号不能再用（因为唯一约束） | 用"删除时把手机号置为 13800000000_deleted_1"或改唯一索引为部分索引 |
| 商品多规格 | 一个商品有颜色/尺寸，"价格与库存"属于 SKU 而不属于商品 | 引入 product_sku 表；order_item 引用 sku_id |
"多规格"这一条尤其值得提前想：很多电商系统在"上 SKU"时才改造表结构，而订单明细已经引用了 product_id——改造要同时改历史数据与新代码，成本很高。
表设计的一条经验：
设计时多花 30 分钟想"半年后的业务会不会让这张表撑不住"，比事后迁移省几十个小时。
13. 掌握标准
- [ ] 能说出三种关系的表结构做法，并知道外键放在哪一侧；
- [ ] 知道多对多的中间表要有复合主键与业务字段；
- [ ] 能用"是否传递依赖"判断第三范式，用"是否可再分"判断第一范式；
- [ ] 知道价格与商品名必须做快照，并说得出理由；
- [ ] 能区分"当前值"与"快照值"，并据此决定是否冗余；
- [ ] 知道
ON DELETE CASCADE用在"关联脱离父记录无意义"的场景。
14. 扩展资源
- MySQL 官方文档：外键约束
- 数据库范式（维基百科）
- 《索引》：表设计好了，下一步是让它查得快
15. 下一步学习
《索引》：索引为什么快、什么时候会失效、怎么建对索引。
前置知识
进度只保存在本机浏览器，可随时清除