relational-data-modeling

关系数据建模

关系模式所迫使你做出的决策，以及如何妥善处理，让数据库尽可能执行约束，应用只负责必须由它处理的部分。每条规则都会指出它要纠正的错误默认做法；对于有能力的模型本就能正确处理的情况，不会专门立规。

示例使用 PostgreSQL 18，本技能中的每条 DDL 语句均已在 18.4 上执行，包括失败示例，以确认约束确实会拒绝声称要拒绝的内容。大约三分之一的规则依赖 MySQL 和 SQLite 不具备的机制（EXCLUDE、部分唯一索引、范围类型、WITHOUT OVERLAPS、可延迟约束、NOT VALID）；这些规则会对此作出说明。判断类规则适用于任何关系型引擎。

适用场景

在以下情况下使用本技能：

• 编写或审查 CREATE TABLE / ALTER TABLE、迁移，或 ORM 模式定义（Prisma、Drizzle、Django models、ActiveRecord、Ecto、SQLAlchemy）
• 设计实体关系模型，或为某一行命名——这是键的选择一旦确定就难以更改的阶段
• 用户询问“应该用一张表还是两张表”“多对多关系该如何建模”“模式允许了错误数据”“我们需要历史记录”“我们要添加多租户支持”或“数据库能否强制执行这项约束”
• 某个 Bug 最终被发现是模式允许了错误状态——例如唯一约束本应阻止的重复数据、外键本应阻止的孤儿记录、重叠的预订，或两行都被标记为默认项
• 为已有数据和流量的表添加约束
• 审查由 ORM 或脚手架工具生成的模式，因为多态关联、自反式代理键和一刀切的软删除标记往往由此而来

本技能不适用于：

• 查询调优、执行计划，或为读取路径选择索引——本技能只涵盖约束和外键所需的索引
• 连接池、复制或运维调优
• 非关系型存储，因为本技能所依据的权衡不适用于这类存储

规则类别

#	类别	前缀	涵盖内容
1	标识与键	key-	一行代表什么；每个外键都依赖的选择
2	关系与基数	rel-	确保数据库能够声明引用，而不只是表达意图
3	以约束作为模型	cons-	哪种机制能够真正维护哪种不变量
4	类型与域	type-	可用成本最低的约束；按含义而非习惯选择
5	派生与编码数据	norm-	每次有意复制数据所付出的代价，以及数据库无法查看其内部的内容
6	时间、历史与生命周期	time-	世界发生变化时，一行会如何变化

快速参考

1. 标识与键

• key-primary-key-is-real-identity — 代理键会隐藏自然键；你仍需为表定义 UNIQUE
• key-identity-not-serial — serial 已过时；GENERATED ALWAYS AS IDENTITY 可阻止导致插入失败的不同步问题
• key-uuidv7-for-client-generated — v4 会让每次 B-tree 插入都随机分布；使用 uuidv7()，并且只在确实需要 UUID 时使用
• key-propagate-scoping-key — 将租户键带入子表键，这样就无法写入跨租户引用
• key-partition-axis-binds-the-key — 对于随流量增长的表，分区轴是键设计决策，而不是事后的调优

2. 关系与基数

• rel-no-polymorphic-fk — type + id 无法声明引用完整性；请使用互斥弧或超类型
• rel-relationship-as-entity — 如果关系具有事实和生命周期，就为其命名；否则，将其保留为带键的链接表
• rel-choose-referential-action — CASCADE 表示组合关系；用于账本时会导致数据丢失
• rel-index-referencing-side — PostgreSQL 不会为子表列创建索引；MySQL 会，而这种习惯也适用于 PostgreSQL
• rel-one-to-one-is-one-table — 拆分表只会增加一次联接；除非存储方式或访问控制不同，否则不会强制执行任何约束
• rel-subtypes-through-the-key — UNIQUE (id, kind) 加上固定的判别列可以证明各变体互不重叠

3. 将约束作为模型的一部分

• cons-not-null-by-default — 结果为 NULL 的 CHECK 会通过，而 UNIQUE 也会失去约束作用
• cons-exclude-for-non-overlap — 应用层的重叠检查会产生先读后写的竞争条件；EXCLUDE 则不会
• cons-partial-unique-index — “至多一个处于活动状态的 X”应使用 UNIQUE ... WHERE，而不是触发器
• cons-check-is-single-row — 将查询包装在函数中，会让 CHECK 悄无声息地失效
• cons-deferrable-for-cycles — 将检查延迟到 COMMIT，而不是让列永远允许为空
• cons-not-valid-then-validate — 这说明了如何向正在使用的表添加约束

4. 类型与域

• type-timestamptz-for-instants — timestamp 存储的是不带时区的挂钟时间；生日并非一个时间点
• type-text-over-varchar — varchar(255) 是 MySQL 遗留产物；长度限制应使用 CHECK 或域
• type-numeric-with-currency — 浮点数会产生偏差，money 取决于区域设置，而货币绝不能隐含不明
• type-range-not-two-columns — 两个松散的列允许出现反转的行，并会阻碍所有时间约束
• type-enum-lookup-or-check — 默认使用 CHECK；当值带有属性时使用查找表；enum 是一条单行道

5. 派生数据与编码数据

• norm-derived-needs-a-mechanism — 没有任何约束能保证两份副本相等；请明确说明所用机制，否则就不要复制
• norm-encoded-composite-values — 根据组成部分生成引用代码；解析出的列无法施加约束
• norm-jsonb-for-open-shapes — 如果可以列出键并按其中一个键筛选，它们就应该是列

6. 时间、历史与生命周期

• time-events-not-in-place-updates — 原地更新余额会破坏审计轨迹，并使账户操作串行化
• time-soft-delete-breaks-constraints — deleted_at 会悄无声息地禁用唯一约束和外键
• time-validity-as-a-range — WITHOUT OVERLAPS 可确保无法表示同时存在的两个版本
• time-valid-vs-transaction-time — 只有在必须还原过去的认知时才需要两个时间轴；通常并不需要

使用方法

当某项决策出现时，阅读对应的参考文件——上面的快速参考足以帮助你找到方向。每条规则都会说明它要纠正的错误默认做法及其原因，然后给出一个规范示例。

有两条经验法则贯穿各类别，即使不针对某条具体规则，也值得遵循：

1. 添加列之前，先说清楚什么条件会让两行被视为同一行。 这句话是你对这张表应承担的一项约束。
2. 在应用代码中编写验证之前，先问问能否用某项约束替代它。 如果答案是“不能”，那么明确这一点也很有价值——请参阅 cons-check-is-single-row，了解不变量形态与机制的对应关系。

• 章节定义 — 类别结构与排序
• 规则模板 — 用于添加新规则
• AGENTS.md — 自动生成的涵盖所有规则的目录

参考文件

文件	描述
references/_sections.md	类别定义与排序
assets/templates/_template.md	新规则模板
metadata.json	版本与来源参考