为了规范化建模表之间的关系

今天我们要深入聊一个很重要的话题：表之间关系的建模。 其实规范化不仅仅是原子性数据和去冗余， 更重要的是搞清楚表之间的正确关系。

如果说数据库是有序的信息存储系统，那表之间的关系就是逻辑桥梁，告诉你数据怎么互相关联。 想象一下图书馆，书的信息和作者的信息是分开存的，但每本书通过特殊的关系“认识”自己的作者。再比如电商：商品数据和买家信息是独立的，但有人下单时，系统会通过订单表把具体买家和具体商品连起来。

在医院，病人和自己的病历有关联，医生和排班表有关，药品和处方有关。这些关系让系统能明白哪些信息是相关的，没必要重复存一堆数据。

这些关系类型其实跟现实生活很像：护照只属于一个人（一对一），一个老师可以教好几个课（一对多），学生可以选不同的课，每门课也有不同的学生（多对多）。

一对一 (1:1)

这种关系下，表“A”里的一条记录只对应表“B”里的一条记录。 比如我们有“员工”和“护照信息”两张表。 一个员工只能有一个护照，每个护照也只属于一个员工。

例子：

员工

护照信息

这里通过外键 员工_id 建立关系，指向“员工”表的id。

一对多 (1:N)

这是最常见的关系类型。这里表“A”里的每条记录可以和表“B”里的多条记录关联，但表“B”里的每条记录只和表“A”里的一条记录关联。比如有“老师”和“课程”两张表。一个老师可以教多个课程。

例子：

老师

课程

通过“课程”表里的外键 老师_id 建立关系。

多对多 (M:N)

当你有很多很多东西要关联时，这种关系就很有意思但也有点复杂。这里表“A”里的每条记录可以和表“B”里的多条记录关联，反过来也一样。比如学生可以选多门课，每门课也有很多学生。

例子：

学生

课程

要建立这种关系，我们需要一个中间表，专门存学生和课程的对应关系：

报名

用外键建模关系

外键就是一列（或几列），指向另一张表的主键。这是建立表之间关系的基础。

外键例子：

CREATE TABLE 课程 (
    id SERIAL PRIMARY KEY,
    课程名 VARCHAR(255)
);

CREATE TABLE 报名 (
    id SERIAL PRIMARY KEY,
    学生_id INT,
    课程_id INT,
    FOREIGN KEY (课程_id) REFERENCES 课程(id)
);

怎么避免设计外键时出错？首先要保证外键和主键的字段类型一致——不然数据库根本不让你建关系。还有要提前想好删除记录时怎么办。比如你删了父表的一行，子表的记录怎么办？常用的做法是用 ON DELETE CASCADE，这样相关数据会跟着主记录一起删掉。这样能保持数据整洁，避免“悬挂”引用。

多对多关系的实现

举个例子：我们有学生和课程。一个学生可以报多门课，一门课也可以有多个学生。要实现M:N关系，我们建三张表：学生、课程和报名。

CREATE TABLE 学生 (
    id SERIAL PRIMARY KEY,
    名字 VARCHAR(255)
);

CREATE TABLE 课程 (
    id SERIAL PRIMARY KEY,
    课程名 VARCHAR(255)
);

CREATE TABLE 报名 (
    id SERIAL PRIMARY KEY,
    学生_id INT,
    课程_id INT,
    FOREIGN KEY (学生_id) REFERENCES 学生(id),
    FOREIGN KEY (课程_id) REFERENCES 课程(id)
);

现在我们可以往报名表里加数据，把学生和课程关联起来。

实践任务

建一个课程管理系统的数据库结构。你需要有学生、课程和报名三张表。实现所有表之间的关系。然后插入一些学生、课程和报名的示例数据。 来看看怎么搞：

1. 建表：

CREATE TABLE 学生 (
    id SERIAL PRIMARY KEY,
    名字 VARCHAR(255)
);

CREATE TABLE 课程 (
    id SERIAL PRIMARY KEY,
    课程名 VARCHAR(255)
);

CREATE TABLE 报名 (
    id SERIAL PRIMARY KEY,
    学生_id INT,
    课程_id INT,
    FOREIGN KEY (学生_id) REFERENCES 学生(id),
    FOREIGN KEY (课程_id) REFERENCES 课程(id)
);

2. 插入数据：

INSERT INTO 学生 (名字) VALUES ('奥托 林'), ('玛丽亚 奇');
INSERT INTO 课程 (课程名) VALUES ('SQL基础'), ('数据库管理');
INSERT INTO 报名 (学生_id, 课程_id) VALUES (1, 1), (1, 2), (2, 1);

3. 查查数据：

SELECT
    学生.名字 AS 学生, 
    课程.课程名 AS 课程
FROM 报名
JOIN 学生 ON 报名.学生_id = 学生.id
JOIN 课程 ON 报名.课程_id = 课程.id;

结果：

建模关系时的难点和注意点

你在建模表关系时，可能会遇到这些坑：

• 删除数据时出错（比如子表有依赖父表的记录）。
• 数据量大时查询性能问题。M:N关系尤其“吃资源”，因为要多做几次join。

解决这些问题可以靠：

• 给外键加索引
• 提前设计好数据库结构
• 在规范化和性能之间找平衡

我们刚刚用很基础的方式聊了表之间关系的建模，并且实际做了个课程管理系统的数据库结构。其实我也想举个大点的例子，但还真没想好怎么举。大例子又复杂又无聊，没啥用。我打算等课程快结束时再回头聊聊这个问题。