除非另外命名了方言,否则 SQL 示例使用 PostgreSQL。简短的代码片段假定其引用的表已存在。在应用 DDL 之前,请在目标数据库中验证 DDL。
为什么模式设计很重要
设计良好的数据库架构是每个可靠应用程序的基石。如果设计正确,查询速度快,数据保持一致,未来的功能无需痛苦的迁移就能融入。如果设计错误,在项目的整个生命周期中,您将不得不应对数据异常、性能迟缓以及无休止的变通方案。
本指南将引导您完成一个 六步流程 用于从头开始设计模式 — 并以您可以为自己的项目改编的完整实际示例结束。
步骤 1 — 收集需求
在打开任何工具之前,先与利益相关者沟通。了解应用程序的 做, 它管理什么数据,以及这些数据如何关联。提出以下问题:
- 系统跟踪的主要“事物”是什么?(用户、订单、产品…)
- 哪些数据必须是唯一的?(电子邮件、SKU、slug…)
- 应用程序将最常运行哪些查询?
- 是否存在监管限制?(GDPR、HIPAA 等)
- 未来 1-3 年的预期数据量是多少?
写下答案——它们将成为你的规范,并指导后续的每一个决定。
步骤 2 — 识别实体
一个 实体 是系统需要存储数据的任何内容。每个实体通常映射到一个数据库表。对于博客平台,初始实体列表可能如下所示:
- 用户 — 作者和读者
- 发布 — 博客上发布的文章
- 分类 — 帖子的分组(例如,“React”,“SQL”)
- 注释 — 阅读者对帖子的响应
- 标签 — 细粒度标签(与帖子多对多)
在白板上将这些绘制为框或使用 ERD 设计器 来讨论概念模型,然后再选择列类型。
步骤 3 — 定义属性和数据类型
对于每个实体,列出它需要的属性并选择最合适的 SQL 数据类型。尽可能具体 — 优先选择 VARCHAR(255) over TEXT 当你知道最大长度,并且偏好 TIMESTAMPTZ over TIMESTAMP 当需要考虑时区时。
-- Users
id SERIAL PRIMARY KEY
username VARCHAR(50) NOT NULL UNIQUE
email VARCHAR(255) NOT NULL UNIQUE
password_hash TEXT NOT NULL -- store a password hash, never plaintext
bio TEXT
avatar_url VARCHAR(500)
created_at TIMESTAMPTZ DEFAULT now()
-- Posts
id SERIAL PRIMARY KEY
author_id INT NOT NULL -- FK → users.id
category_id INT -- FK → categories.id
title VARCHAR(200) NOT NULL
slug VARCHAR(220) NOT NULL UNIQUE
body TEXT NOT NULL
status VARCHAR(20) DEFAULT 'draft' -- draft | published | archived
published_at TIMESTAMPTZ
created_at TIMESTAMPTZ DEFAULT now()
updated_at TIMESTAMPTZ DEFAULT now()步骤 4 — 建立关系
确定实体之间的关系。这三种基本类型是:
- 一对多 (1:N) — 一个用户可以有许多文章;一篇文章属于一个用户。
- 多对多 (M:N) — 一篇文章可以有许多标签;一个标签可以属于许多文章。这需要一个 连接表.
- 一对一 (1:1) — 一个用户有一个配置文件。通常合并为单个表,除非配置文件数据量大或很少访问。
-- Junction table for the posts ↔ tags many-to-many relationship
CREATE TABLE post_tags (
post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
tag_id INT NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);步骤 5 — 应用约束和索引
约束可保护数据质量。您至少应考虑:
NOT NULL在必须具有值的每一列上。UNIQUE在自然标识符(电子邮件、 slug、用户名)上。CHECK用于域规则的约束(例如,状态必须是三个值之一)。DEFAULT时间戳和状态列的值。- 带有显式的外键
ON DELETE操作。
然后添加 索引 在您经常过滤、排序或连接的列上。外键列是首选。
-- Index foreign-key columns for faster joins
CREATE INDEX idx_posts_author ON posts (author_id);
CREATE INDEX idx_posts_category ON posts (category_id);
CREATE INDEX idx_comments_post ON comments (post_id);
-- Partial index: only published posts (PostgreSQL)
CREATE INDEX idx_posts_published
ON posts (published_at DESC)
WHERE status = 'published';步骤 6 — 规范化(然后在需要时反规范化)
规范化 通过根据一组“范式”组织列和表来消除冗余数据。大多数模式应达到 第三范式 (3NF):
- 第一范式 (1NF) — 每个列都包含原子值;没有重复组。
- 第二范式 (2NF) — 没有非主属性仅依赖于候选键的一部分。
- 第三范式 (3NF) — 每个非平凡依赖都有一个超键行列式或一个属于候选键的依赖属性。
规范化后,您可以有选择地 反规范化 用于性能 — 例如,存储一个 comment_count 在 posts 表以避免昂贵的 COUNT(*) 在每次页面加载时。故意反规范化并记录下来。
完整的博客平台架构
-- Full SQL for the blog platform schema
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
bio TEXT,
avatar_url VARCHAR(500),
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
slug VARCHAR(110) NOT NULL UNIQUE
);
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
author_id INT NOT NULL REFERENCES users (id),
category_id INT REFERENCES categories (id) ON DELETE SET NULL,
title VARCHAR(200) NOT NULL,
slug VARCHAR(220) NOT NULL UNIQUE,
body TEXT NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft','published','archived')),
published_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE post_tags (
post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
tag_id INT NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
user_id INT NOT NULL REFERENCES users (id),
body TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
-- Indexes
CREATE INDEX idx_posts_author ON posts (author_id);
CREATE INDEX idx_posts_category ON posts (category_id);
CREATE INDEX idx_comments_post ON comments (post_id);
CREATE INDEX idx_comments_user ON comments (user_id);将完整的架构粘贴到 SQL转ERD编辑器 来检查其关系。使用 ERD 设计器 用于概念图和已审查的 SQL 转换。



