为什么多对多关系一定需要第三张中间表,记录两张表的关联关系!
这是一个非常经典的数据库设计问题。简单来说,因为现实世界的关系是多对多的,而数据库的一行只能存一个“有限”的内容。
让我用一个非常直观的例子来解释:
场景:学生和课程
- 一个学生可以选多门课程(张三选了数学和英语)
- 一门课程可以有多个学生(数学课有张三、李四、王五)
这就是典型的多对多关系。
错误尝试:不加中间表会怎样?
尝试1:只在学生表里加一个“课程”字段
| 学生ID | 学生姓名 | 所选课程 |
|---|---|---|
| 1 | 张三 | 数学,英语 |
❌ 问题:这一格放了多个值,违反了数据库设计的第一范式(属性不可再分)。以后想查“谁选了数学”会非常麻烦,得用模糊匹配,性能极差。
尝试2:只在课程表里加一个“学生”字段
| 课程ID | 课程名称 | 选课学生 |
|---|---|---|
| 101 | 数学 | 张三,李四,王五 |
❌ 问题:同上,还是多个值挤在一格里。
尝试3:在学生表里加多个课程字段
| 学生ID | 学生姓名 | 课程1 | 课程2 | 课程3 | 课程4 |
|---|---|---|---|---|---|
| 1 | 张三 | 数学 | 英语 | NULL | NULL |
❌ 问题:
- 浪费空间:很多人可能只选1-2门课,大部分字段是空的
- 无法扩展:如果有学生选了10门课怎么办?改表结构加字段?
- 查询麻烦:想知道张三选了哪些课,得查4个字段;想知道谁选了数学,得把4个字段都查一遍
为什么中间表是标准解法?
中间表就像是一个登记本,专门记录“谁”和“什么”有关系。
学生表(存人的信息)
| 学生ID | 学生姓名 |
|---|---|
| 1 | 张三 |
| 2 | 李四 |
课程表(存课程的信息)
| 课程ID | 课程名称 |
|---|---|
| 101 | 数学 |
| 102 | 英语 |
中间表(选课登记本)- 关键在这里!
| ID | 学生ID | 课程ID |
|---|---|---|
| 1 | 1 | 101 |
| 2 | 1 | 102 |
| 3 | 2 | 101 |
这个中间表在做什么?
- 第1行:张三(ID=1) 选了 数学(ID=101)
- 第2行:张三(ID=1) 选了 英语(ID=102)
- 第3行:李四(ID=2) 选了 数学(ID=101)
中间表的三大核心价值
1. 打破存储限制
每一行只记录一个关系。张三选了两门课,就记两行。没有任何格子需要存多个值。
2. 双向查询都很快
- 查张三的课:
SELECT * FROM 中间表 WHERE 学生ID = 1→ 得到101,102 → 去课程表拿名字 - 查谁选了数学:
SELECT * FROM 中间表 WHERE 课程ID = 101→ 得到1,2 → 去学生表拿名字
3. 可以记录“关系本身的信息”
中间表除了存两个ID,还可以存这个关系特有的属性:
| ID | 学生ID | 课程ID | 选课时间 | 成绩 | 是否缴费 |
|---|---|---|---|---|---|
| 1 | 1 | 101 | 2023-09-01 | 85 | 是 |
这些信息既不属于学生(不是学生天生的属性),也不属于课程(不是课程本身的属性),只属于“张三选了数学”这个关系。没有中间表,这些信息根本没地方放。
一句话总结
多对多关系需要中间表,是因为:
关系本身也是一种“数据”,需要有自己的“容器”。
就像你不能把“演出关系”塞进“演员”那张表里,也不能塞进“电影”那张表里——你需要一个演出表来记录谁拍了什么电影、什么时候拍的。中间表就是数据库里的“演出表”。
或者你不能把“租赁关系”塞进“租客”那张表里,也不能塞进“房子”那张表里——你需要一个租赁合同表来记录谁租了哪个房子、什么时候租的。中间表就是数据库里的“租赁合同”。
相关笔记
- 数据库设计-对比一张宽表和分表存储 — 表设计范式的另一个维度
- 窗口函数使用场景 — 表内分组聚合/排序的 SQL 进阶用法