欢迎来到本系列教程的终极挑战——实战项目！
在前面的 25 章里，我们已经学习了从基础 SQL 查询到高级数据库管理的各种知识和技能。现在，是时候把这些散落的“技能点”全部串联起来，像一个真正的数据库开发者一样，从零开始，为一个真实的应用场景设计和构建数据库。
我们的项目是：一个简单的博客系统。
麻雀虽小，五脏俱全。通过这个项目，我们将实践前面学到的几乎所有核心概念：数据表设计、约束、关系、索引，以及编写核心功能的 SQL 查询。
这一章，我们先从最重要、最顶层的部分开始：需求分析和数据库关系设计。
26.1 需求分析
在编写任何代码或 SQL 之前，我们必须先弄清楚我们到底要做一个什么样的系统。我们需要支持哪些核心功能？系统里有哪些核心的“实体”？
经过一番头脑风暴，我们确定了这个简单博客系统的核心需求：
- 用户 (User)
- 用户可以注册，需要提供用户名、密码和邮箱。
- 用户名和邮箱必须是唯一的。
- 用户可以登录。
- 用户可以发表文章。
- 用户可以发表评论。
- 文章 (Post)
- 每篇文章都必须有一个作者（即用户）。
- 文章有标题和内容。
- 文章可以被一个或多个标签进行分类。
- 评论 (Comment)
- 每条评论都必须针对某一篇文章。
- 每条评论都必须有一个作者（即用户）。
- 评论有其内容。
- 标签 (Tag)
- 标签本身有一个名字（如 “SQL”, “Python”, “生活”)。
- 标签名必须是唯一的。
- 一篇文章可以有多个标签，一个标签也可以用于多篇文章。
26.2 数据库关系设计 (E-R 图)
需求分析完成后，下一步就是要把这些文字描述的需求，转化成结构化的数据库设计。这个过程的核心就是识别出系统中的实体 (Entity)，以及实体之间的关系 (Relationship)。
最常用的工具就是 E-R 图 (Entity-Relationship Diagram)。
第一步：识别核心实体
从需求中，我们能轻易地识别出四个核心实体：
- Users (用户)
- Posts (文章)
- Comments (评论)
- Tags (标签)
这四个实体，将分别对应我们数据库中的四张主表。
第二步：分析实体之间的关系
现在，我们来分析这四个实体之间是如何相互关联的。
- Users 和 Posts 的关系
- 一个用户可以发表多篇文章。
- 一篇文章只能由一个用户发表。
- 结论：这是典型的 一对多 (One-to-Many) 关系。
- Users 和 Comments 的关系
- 一个用户可以发表多条评论。
- 一条评论只能由一个用户发表。
- 结论：这也是 一对多 (One-to-Many) 关系。
- Posts 和 Comments 的关系
- 一篇文章可以有多条评论。
- 一条评论只能针对一篇文章。
- 结论：这还是 一对多 (One-to-Many) 关系。
- Posts 和 Tags 的关系
- 一篇文章可以有多个标签。
- 一个标签也可以被用在多篇文章上。
- 结论：这是经典的 多对多 (Many-to-Many) 关系。
如何处理“多对多”关系？
在关系型数据库中，我们无法直接实现一个多对多的关系。我们必须引入一个额外的“中间表”（也叫连接表、桥接表），来将这个多对多关系拆解成两个一对多关系。
我们将创建一个名为 post_tags 的表。它的每一行，都只记录了一件事：“某篇文章被打上了某个标签”。
- Posts 和 post_tags 是一对多。
- Tags 和 post_tags 也是一对多。
第三步：绘制 E-R 图
现在，我们可以把上面的分析结果画成一张图了。E-R 图有很多种画法，这里我们用一种简洁的方式来表示：
+-----------+ +-----------+ +-------------+
| Users | | Posts | | Comments |
+-----------+ +-----------+ +-------------+
| user_id (PK) |----<| author_id (FK) |----<| post_id (FK) |
| username | | post_id (PK)| | comment_id(PK)|
| email | | title | | author_id (FK)>----(to Users)
| ... | | content | | content |
+-----------+ | ... | | ... |
+-----------+ +-------------+
|
|
^
|
+-----------------+
| post_tags | (中间表)
+-----------------+
| post_id (FK, PK)|
| tag_id (FK, PK) |
+-----------------+
|
|
v
|
+-----------+
| Tags |
+-----------+
| tag_id (PK) |
| tag_name |
+-----------+图例解读:
- (PK): Primary Key (主键)
- (FK): Foreign Key (外键)
- ----<: "多"的一端指向"一"的一端，表示一对多关系。
第四步：确定表结构和字段
根据 E-R 图，我们最终确定了五张表的详细结构：
- users
- user_id: SERIAL PRIMARY KEY - 用户ID，自增主键
- username: VARCHAR(50) NOT NULL UNIQUE - 用户名
- email: VARCHAR(100) NOT NULL UNIQUE - 邮箱
- password_hash: TEXT NOT NULL - 存储加密后的密码
- created_at: TIMESTAMPTZ DEFAULT now() - 注册时间
- posts
- post_id: SERIAL PRIMARY KEY - 文章ID
- author_id: INT NOT NULL REFERENCES users(user_id) - 作者ID，外键
- title: VARCHAR(200) NOT NULL - 标题
- content: TEXT - 内容
- created_at: TIMESTAMPTZ DEFAULT now() - 发表时间
- comments
- comment_id: SERIAL PRIMARY KEY - 评论ID
- post_id: INT NOT NULL REFERENCES posts(post_id) - 关联的文章ID
- author_id: INT NOT NULL REFERENCES users(user_id) - 评论作者ID
- content: TEXT NOT NULL - 评论内容
- created_at: TIMESTAMPTZ DEFAULT now() - 评论时间
- tags
- tag_id: SERIAL PRIMARY KEY - 标签ID
- tag_name: VARCHAR(50) NOT NULL UNIQUE - 标签名
- post_tags (中间表)
- post_id: INT NOT NULL REFERENCES posts(post_id)
- tag_id: INT NOT NULL REFERENCES tags(tag_id)
- PRIMARY KEY (post_id, tag_id) - 复合主键，保证了“一篇文章-一个标签”的组合是唯一的。
本章小结
太棒了！我们已经完成了数据库开发中最重要、最具创造性的一步。
- 我们通过需求分析，明确了系统的核心功能和实体。
- 通过关系设计，识别了实体间的“一对多”和“多对多”关系。
- 我们学会了如何使用中间表来解决“多对多”问题。
- 最终，我们产出了一份包含 5 张表的、详细的数据库表结构设计。
这份设计蓝图，就是我们下一章将要付诸实践的基础。在下一章，我们将把这份蓝图变成真实的代码，用 CREATE TABLE 语句在数据库中实现我们设计的表结构和所有约束。准备好将你的设计变成现实了吗？我们下一章见！