3.1 从业务规则到 ER 模型与关系 Schema

数据库设计把对象身份、关系数量和生命周期变成可执行约束。只有表名和连线的图还无法回答“能否重复领取”或“完成后能否删除”。本课逐条把这些规则映射到键、外键和状态更新条件；表可以画得很整齐，规则仍要逐条落地。

本课 SQL 是各设计点的局部示例（partial），不是对上一章数据库的连续迁移。完整执行需在独立数据库补齐 catalog_items、组织表与外键，并统一类型约束。

本课目标

从业务语言识别 entity、attribute 与 relationship；
区分 natural key、surrogate key 与 foreign key；
正确表达 one-to-one、one-to-many、many-to-many；
把 ER model 映射到 SQL schema；
记录 temporal、optional 与 deletion semantics。

从事实和规则开始

假设任务系统有这些规则：

每位 customer 有稳定 ID、显示名和等级；
每项 task 由一位 customer 领取；
一项 task 可以需要多种 item，同一种 item 可出现在多项 task；
每个 task-item pair 记录 quantity；
task 的 status 只能按允许的状态机变化；
删除 item 时，若仍被 task 引用应拒绝；
task 完成后，需要保留当时 reward 和 item requirement 的历史。

第 3、4 条共同说明 task 与 Item 是 many-to-many，而且 relationship 自己有 quantity attribute。因此需要 associative entity，而不是在一列里保存逗号分隔的 item IDs。

Entity 与 value object

判断某个概念是否需要独立 entity，可问：

它是否有独立 identity？
生命周期是否独立？
是否被多个对象引用？
是否需要单独约束、权限或历史？

“地址”可能只是用户的一组 value attributes，也可能是可复用、可验证、有版本的 shipping address entity。答案来自业务规则，不来自名词本身。

不要把每个 string 都抽成 lookup table。只有当值域需要稳定 identity、metadata、referential integrity 或独立 lifecycle 时，独立表才有明确收益。

Key 的三种角色

Candidate/natural key

候选键（candidate key）是能唯一标识一行的最小属性集合；自然键（natural key）则指有业务含义的标识，例如租户内的外部客户编码。两者描述不同维度。自然键作为候选键时，必须有唯一性和非空保证；若它会变化，还要设计引用更新规则。

Surrogate key

系统生成、没有业务含义的标识，如 integer ID 或 UUID。它能让引用不随业务编码变化；是否更短取决于实际类型和长度，也不会自动阻止业务重复。若 (tenant_id, external_code) 必须唯一，使用 surrogate primary key 后仍应加 UNIQUE constraint。

Foreign key

foreign key 表达 child value 必须引用存在的 candidate key。它不只是方便 join，更准确地说是一份 referential integrity contract。

sql

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    tenant_id     INTEGER NOT NULL,
    external_code TEXT NOT NULL,
    display_name  TEXT NOT NULL,
    level         INTEGER NOT NULL CHECK (level >= 1),
    UNIQUE (tenant_id, external_code)
);

是否使用 surrogate key 与是否保留 natural uniqueness 是两个独立决定。

Cardinality 与 participation

ER 图上的连线至少要回答：

每个 A 最少/最多关联多少 B；
relationship 对哪一侧 mandatory；
relationship 是否有自己的 attributes；
relationship 的有效期和删除规则是什么。

常见映射：

关系	关系 schema 映射
one-to-many	foreign key 通常放在 many side
optional one-to-many	foreign key 可以 NULL，或使用单独 relationship table
one-to-one	foreign key + UNIQUE；放哪侧取决于 optionality/lifecycle
many-to-many	associative table，包含两侧 foreign keys

One-to-many

sql

CREATE TABLE tasks (
    task_id      INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status        TEXT NOT NULL,
    reward_cents  INTEGER NOT NULL CHECK (reward_cents >= 0),
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT,
    CHECK (status IN ('active', 'completed', 'cancelled'))
);

NOT NULL 表达每项 task 必须有 customer；FK 表达这个 customer 必须存在。它们是不同 constraint。

Many-to-many with relationship attribute

sql

CREATE TABLE task_items (
    task_id INTEGER NOT NULL,
    item_id  INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (task_id, item_id),
    FOREIGN KEY (task_id)
        REFERENCES tasks(task_id)
        ON DELETE CASCADE,
    FOREIGN KEY (item_id)
        REFERENCES catalog_items(item_id)
        ON DELETE RESTRICT
);

composite primary key 表达同一 task/item pair 最多一行。若业务允许同一 item 以不同 role 多次出现，key 必须加入 role 或使用独立 relationship ID，同时另写合适的 uniqueness rule。

One-to-one 并不常见

sql

CREATE TABLE customer_profiles (
    customer_id INTEGER PRIMARY KEY,
    biography     TEXT,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

shared primary key 保证一个 customer 最多一个 profile。拆表可能为了 optional large columns、security boundary 或 lifecycle，不是因为“每个概念都应该一张表”。如果两边总是同时创建、同时读取、约束和权限一致，放在一张表可能更简单。

Optionality 不应靠 sentinel value

没有发现日期应保存 NULL，而不是 0000-00-00；没有所属组织应是 nullable FK 或独立 membership history，而不是 organization_id=0，除非 0 本身是合法引用的明确记录。

随意使用占位值会让字段含义和筛选条件变复杂。有效外键仍会拒绝不存在的 0；若为了让它通过而创建“无组织”父行，就必须额外区分这条占位记录与真实组织。

Multi-valued attribute 要建 relationship

错误设计：

text

tasks.required_item_ids = '1,3,5'

问题包括：无法用 ordinary FK 约束每个元素、搜索依赖 string parsing、更新存在重复与顺序问题、无法自然记录 quantity。

正确设计是 task_items。JSON/array column 有合理用途，但一旦内部元素需要查询、约束、关联和独立更新，就应认真评估关系建模。

状态机不是一个 CHECK 就够了

CHECK (status IN (...)) 只限制当前值，不能阻止 completed -> active 这类非法 transition。完整状态机可由：

application service 在 transaction 中校验；
transition table + stored procedure；
trigger；
append-only event/history model；
database-specific policy。

无论选择哪种，都要处理 concurrent update，不能“先 SELECT 再 UPDATE”却没有 version/lock predicate。

例如 optimistic update：

sql

UPDATE tasks
SET status = 'completed'
WHERE task_id = :task_id
  AND status = 'active';

应用必须检查 affected rows 恰好为 1；为 0 可能表示不存在或状态已经变化。

时间会改变模型

“customer 属于 organization”如果只关心当前状态，一个 nullable organization_id 可能足够；如果要回答过去某天属于哪个 organization，就需要 membership interval：

sql

CREATE TABLE organization_memberships (
    customer_id INTEGER NOT NULL,
    organization_id      INTEGER NOT NULL,
    valid_from    TEXT NOT NULL,
    valid_to      TEXT,
    PRIMARY KEY (customer_id, valid_from),
    CHECK (valid_to IS NULL OR valid_to > valid_from)
);

这里的文本比较要求统一、可按字典序比较的日期或时间格式与时区；示例未给出完整日期验证和外键。这个 CHECK 仍不能阻止同一 customer 的 intervals overlap。是否用 exclusion constraint、trigger 或 application transaction 取决于数据库能力。

删除语义要逐条选择

RESTRICT/NO ACTION：都约束悬空引用，但检查时机可能不同；SQLite 的 RESTRICT 立即拒绝，NO ACTION 按即时或延迟约束的检查时机判断；
CASCADE：父行删除带走 dependent rows；
SET NULL：关系变为 unknown/absent，但 child 必须允许 NULL；
soft delete：保留 row 并标记生命周期，不自动解决 uniqueness、FK 和 query filtering。

CASCADE 适合纯 dependent rows，不应因为方便就用于具有独立审计或财务意义的数据。删除前画出 dependency graph 并测试影响范围。本例第 7 条要求保留完成记录，因此还要禁止删除已完成的 task，或先保存受保护的历史快照；仅靠 task_items 的 CASCADE 并不能满足这条规则。

外键动作和延迟检查的细节见 SQLite 外键文档。

从模型到 migration

设计交付物应包含：

业务术语与 invariant；
ER/cardinality/optionality；
table、key、constraint 与 index 初稿；
deletion 与 history policy；
migration/backfill/rollback；
representative queries 与 expected cardinality；
不能由数据库直接保证的规则及 owner。

本课小结

ER model 的核心不是图形，而是 identity、cardinality、participation 和 lifecycle。映射为 schema 后，要让 primary key、UNIQUE、foreign key、CHECK 与 transaction 尽可能承担可执行约束。下一课用 functional dependency 检查一个 relation 是否混入了多个不同事实。

第1章逻辑

第2章集合论

第3章证明方法

第4章递归、归纳与递推

第5章关系与函数

第6章组合数学

第7章图论

第8章概率与统计

第9章数论

第10章线性代数

第11章微积分与优化

第12章信息论与数值计算