database-schema-design

数据库架构设计技能

为给定领域生成一份完整的数据库架构设计文档。架构文档不只是表的列表——它记录了设计决策：建模了什么、实体之间如何关联、架构针对哪些查询进行了优化，以及做出了哪些权衡。

优秀的架构设计文档能帮助工程师理解数据模型、正确查询数据、安全扩展架构，并编写不会造成破坏的迁移。

必需输入

如果尚未提供，请询问以下信息：

• 领域描述——系统的功能；要建模哪些业务对象
• 实体与关系——领域中的主要事物及其相互关系（例如：“一个 User 有多个 Orders；一个 Order 有多个 OrderItems；一个 OrderItem 引用一个 Product”）
• 预期查询模式——最重要的读写查询（例如：“按日期排序，获取某个用户的所有订单”；“按 SKU 查找商品”）
• 数据库引擎——PostgreSQL、MySQL、SQLite、CockroachDB 等——这会影响 DDL 语法和可用类型
• 预期数据量——大致行数、增长率，以及任何分区需求
• 约束——需要遵循的现有惯例、命名标准或迁移约束

输出格式

---

数据库架构设计：[领域 / 服务名称]

服务：[名称] | 团队：[团队名称] 作者：[姓名] | 审核人：[姓名] 日期：[日期] | 数据库引擎：[PostgreSQL X.X / MySQL X.X / 等] 状态：[草稿 / 已审核 / 已批准]

---

1. 概述

[用 2–3 句话描述所建模的领域、本架构的范围，以及关键设计理念（例如：“本架构优先保障面向客户的 API 的读取性能，而非写入简便性”，或“设计时考虑了未来迁移到多租户架构的可能性”）]

范围内：

• [实体或子系统]
• [实体或子系统]

范围外：

• [例如：分析 / 报表表——使用单独的架构]
• [例如：审计日志表——在单独的设计文档中说明]

---

2. 实体关系图

┌───────────────────┐         ┌───────────────────────┐
│      users        │         │       organisations    │
│─────────────────  │         │─────────────────────── │
│ id (PK)           │    ┌───▶│ id (PK)                │
│ org_id (FK)  ─────┼────┘    │ name                   │
│ email             │         │ plan                   │
│ display_name      │         │ created_at             │
│ created_at        │         └───────────────────────┘
│ updated_at        │
└─────────┬─────────┘
          │ 1
          │
          │ N
┌─────────▼─────────┐         ┌───────────────────────┐
│      [table_a]    │         │      [table_b]         │
│─────────────────  │         │─────────────────────── │
│ id (PK)           │    N    │ id (PK)                │
│ user_id (FK) ─────┼────────▶│ [table_a]_id (FK)      │
│ [field]           │    │    │ [field]                │
│ [field]           │    │    │ [field]                │
│ created_at        │         │ created_at             │
└───────────────────┘         └───────────────────────┘

关系摘要：

实体 A	关系	实体 B	备注
organisations	有多个	users	一个组织可以有多个用户
users	有多个	[table_a]	用户删除时软删除
[table_a]	有多个	[table_b]	级联删除
[table_b]	属于	[table_a]	不可为空的外键
[table_c]	多对多（通过 [join_table]）	[table_d]	带有元数据的联结表

---

3. 表定义

organisations

[用 1 句话说明此表存储的内容及其在领域中的作用。]

CREATE TABLE organisations (
    id          UUID            PRIMARY KEY DEFAULT gen_random_uuid(),
    name        VARCHAR(255)    NOT NULL,
    slug        VARCHAR(100)    NOT NULL UNIQUE,
    plan        VARCHAR(50)     NOT NULL DEFAULT 'free'
                                CHECK (plan IN ('free', 'pro', 'enterprise')),
    settings    JSONB           NOT NULL DEFAULT '{}',
    created_at  TIMESTAMPTZ     NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ     NOT NULL DEFAULT now()
);

列	类型	可为空	默认值	备注
id	UUID	否	gen_random_uuid()	代理主键（PK）——分布式使用时优先采用 UUID，而非 serial
name	VARCHAR(255)	否	—	显示名称；不唯一
slug	VARCHAR(100)	否	—	URL 安全标识符；在所有组织中唯一
plan	VARCHAR(50)	否	'free'	通过 CHECK 限定为已知值
settings	JSONB	否	{}	灵活配置；避免用于可查询字段
created_at	TIMESTAMPTZ	否	now()	始终使用 TIMESTAMPTZ，而非 TIMESTAMP
updated_at	TIMESTAMPTZ	否	now()	通过触发器更新（见下文）

---

users

[用 1 句话说明此表存储的内容。]

CREATE TABLE users (
    id              UUID            PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id          UUID            NOT NULL REFERENCES organisations(id)
                                    ON DELETE RESTRICT,
    email           VARCHAR(254)    NOT NULL,
    display_name    VARCHAR(255)    NOT NULL DEFAULT '',
    role            VARCHAR(50)     NOT NULL DEFAULT 'member'
                                    CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
    email_verified  BOOLEAN         NOT NULL DEFAULT false,
    deleted_at      TIMESTAMPTZ     NULL,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ     NOT NULL DEFAULT now(),

    CONSTRAINT users_email_org_unique UNIQUE (email, org_id)
);

列	类型	可为空	默认值	备注
id	UUID	否	gen_random_uuid()	—
org_id	UUID	否	—	organisations 的外键；RESTRICT 可防止产生孤立记录
email	VARCHAR(254)	否	—	RFC 5321 规定的最大长度；在每个组织内唯一（非全局唯一）
role	VARCHAR(50)	否	'member'	应用层 RBAC
deleted_at	TIMESTAMPTZ	是	NULL	软删除；NULL = 活跃

软删除策略：deleted_at IS NOT NULL 的行视为已删除。除非明确要获取已删除记录，否则所有应用查询都必须通过 WHERE deleted_at IS NULL 进行筛选。使用视图或 ORM 作用域来强制执行此规则。

---

[table_a]

[对此表建模内容的描述。]

CREATE TABLE [table_a] (
    id          UUID            PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID            NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    [field_1]   VARCHAR(255)    NOT NULL,
    [field_2]   TEXT            NULL,
    [field_3]   INTEGER         NOT NULL DEFAULT 0 CHECK ([field_3] >= 0),
    status      VARCHAR(50)     NOT NULL DEFAULT 'pending'
                                CHECK (status IN ('pending', 'active', 'archived')),
    metadata    JSONB           NOT NULL DEFAULT '{}',
    created_at  TIMESTAMPTZ     NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ     NOT NULL DEFAULT now()
);

列	类型	可为空	备注
user_id	UUID	否	级联删除——删除用户时，其对应的 [table_a] 行也会删除
[field_1]	VARCHAR(255)	否	[长度限制的原因]
status	VARCHAR(50)	否	状态机：pending → active → archived（不允许其他转换）
metadata	JSONB	否	[此处存储的内容，以及不将其设为类型化列的原因]

---

[join_table] （多对多）

[此表所表示关系的描述。]

CREATE TABLE [join_table] (
    [table_c]_id    UUID        NOT NULL REFERENCES [table_c](id) ON DELETE CASCADE,
    [table_d]_id    UUID        NOT NULL REFERENCES [table_d](id) ON DELETE CASCADE,
    granted_by      UUID        NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    granted_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

    PRIMARY KEY ([table_c]_id, [table_d]_id)
);

采用复合主键的原因： [table_c]_id + [table_d]_id 的组合是自然键——每个关联都是唯一的，主键同时充当唯一性约束，无需单独的索引。

---

4. 索引策略

针对每张表，定义要创建哪些索引及其原因。包括这些索引要服务的查询。

表	索引名称	列	类型	服务的查询	备注
users	users_org_id_idx	(org_id)	B-tree	SELECT * FROM users WHERE org_id = $1	外键查找；提升连接性能所必需
users	users_email_lower_idx	(lower(email))	B-tree（函数索引）	WHERE lower(email) = lower($1)	不区分大小写的电子邮件查找
users	users_active_by_org_idx	(org_id, created_at DESC)	B-tree	WHERE org_id = $1 AND deleted_at IS NULL ORDER BY created_at DESC	可考虑使用部分索引（见下文）
[table_a]	[table_a]_user_id_status_idx	(user_id, status)	B-tree	WHERE user_id = $1 AND status = 'active'	复合索引——列的顺序很重要
[table_a]	[table_a]_metadata_gin_idx	metadata	GIN	WHERE metadata @> '{"key": "value"}'	仅在频繁查询 JSONB 时添加

部分索引（PostgreSQL）：

-- Index only active (non-deleted) users — dramatically smaller for soft-delete tables
CREATE INDEX users_active_email_idx
    ON users (email, org_id)
    WHERE deleted_at IS NULL;

-- Index only pending items — avoids indexing the majority of rows
CREATE INDEX [table_a]_pending_idx
    ON [table_a] (user_id, created_at)
    WHERE status = 'pending';

所应用的索引设计原则：

• 出现在 JOIN 条件中的外键始终建立索引
• 复合索引按选择性排序：选择性最高的列放在最前
• 对不区分大小写的查找使用函数索引
• 仅在频繁执行 JSONB 包含查询时使用 GIN 索引
• 对大型表中按状态筛选的查询使用部分索引

---

5. 访问模式分析

记录此架构旨在支持的主要查询。针对每种查询，展示查询语句、使用的索引及注意事项。

AP-1：获取某个组织的所有活跃用户（分页）

频率： 很高——每次加载仪表板时都会调用
查询：

SELECT id, email, display_name, role, created_at
FROM users
WHERE org_id = $1
  AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 50 OFFSET $2;

使用的索引： users_active_by_org_idx（org_id, created_at DESC）
备注： 数据量较大时使用键集分页（WHERE created_at < $cursor）；行数超过约 10k 后，OFFSET 的性能会下降。

---

AP-2：按电子邮件查找用户（不区分大小写）

频率： 高——每次身份验证尝试都会调用
查询：

SELECT id, org_id, role, email_verified
FROM users
WHERE lower(email) = lower($1)
  AND deleted_at IS NULL;

使用的索引： users_email_lower_idx
备注： 如果不同组织中存在相同的电子邮件地址，则会返回多行。应用程序根据组织上下文进行处理。

---

AP-3：按状态获取用户的 [table_a] 项目

频率： 高
查询：

SELECT *
FROM [table_a]
WHERE user_id = $1
  AND status = $2
ORDER BY created_at DESC
LIMIT 25;

使用的索引： [table_a]_user_id_status_idx
备注： 复合索引覆盖两个筛选列。由于 user_id 的选择性更高，因此 status 必须在索引中排在第二位。

---

AP-4：[根据需要添加其他访问模式]

---

6. 规范化决策

记录有意进行规范化或反规范化的选择，并说明理由。

决策	方案	理由
[例如：是否在用户表中存储组织名称？]	不进行反规范化 — 始终联接到组织表	避免副本过时；组织名称很少更改，且联接成本很低
[例如：状态历史记录]	不放在此表中 — 如有需要，单独建立 [table_a]_status_history	99% 的查询只需要当前状态；历史记录用于审计，而非应用数据
[例如：组织表上的 JSONB settings 列]	反规范化为 JSONB	设置总是一起读取；从不按字段查询；更改架构无需迁移
[例如：计算得出的聚合计数]	不存储 — 在查询时计算	计数较小；维护计数器列需要谨慎处理锁；使用带索引的 SELECT COUNT(*)

---

7. 触发器和自动化

-- Automatically update updated_at on any row modification
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to all tables with updated_at
CREATE TRIGGER users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER [table_a]_updated_at
    BEFORE UPDATE ON [table_a]
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

---

8. 迁移说明

如果要将此架构引入现有系统，请说明迁移方案。

步骤	描述	向后兼容	风险
1	创建 organisations 表	是 — 增量变更	低
2	创建 users 表	是 — 增量变更	低
3	为现有用户回填 org_id	需要经过双写阶段	中
4	为 org_id 添加 NOT NULL 约束	需要 100% 完成回填	中
5	移除已弃用的列	需要先更新应用代码	应用部署完成后风险低

回填策略： [说明如何处理现有数据 — 批次大小、速率限制、验证查询]

回滚： 每个迁移步骤都应能够单独回滚。完整的回滚流程模板请参阅 [database-migration-plan skill]。

---

质量检查

• 每张表都有主键和 created_at 列 — 不依赖按行插入顺序进行隐式排序
• 每个外键都有对应的索引 — 不遗漏 FK 索引，以免联接时导致全表扫描
• 所有时间戳列都使用 TIMESTAMPTZ，而非 TIMESTAMP — 明确处理时区
• 软删除表需记录约定，以及筛选条件的执行位置（ORM 作用域、视图或查询规范）
• 设计中的每种访问模式都有相应索引，或明确说明可接受全表扫描
• JSONB 列有充分理由 — 不将其用作可查询字段的规范架构设计的替代方案
• 记录规范化决策及其理由，而非仅列出决策
• 如果是架构变更而非从零开始设计，迁移说明需涵盖现有数据

反模式

• 不要将 JSONB 列用作会被查询的字段的规范关系架构设计的替代方案
• 不要随意添加索引 — 每个索引都必须有特定访问模式作为依据
• 不要忽略时区处理 — 使用 TIMESTAMPTZ，绝不使用普通 TIMESTAMP
• 不要在未记录规范化决策的情况下进行设计 — 后续维护者需要了解理由，而不仅是结构
• 不要跳过访问模式部分 — 缺少查询模式的架构无法评估其正确性

示例提示语

• “设计一个数据库。”
• “记录现有架构。”
• “对实体及其关系建模。”
• “定义表结构。”
• “规划索引策略。”