[!info] related notes

• 课程 MOC：BodySense 数据库工程深化课程 MOC
• 前置：第 1 课
• 知识：主键、外键与约束、数据库规范化

BodySense 数据库第 2 课：Schema、约束、Cardinality、Normalization 与 JSONB

[!abstract] 本课目标 从“表有哪些列”升级到“数据库允许哪些状态存在”。

从零建立概念：Schema 为什么不只是字段清单

上一课把 Schema 定义成“数据库允许什么状态存在”。

这一课继续问：

怎样把一句业务规则，真正翻译成数据库能自动执行的规则？

例如：

每个用户 email 不能重复
每条 message 必须属于一个存在的 conversation
rollout bucket 必须在 0~9999
一条 row 必须有 user_id

如果这些规则只写在 README 或 Go if 中，它们并不是完整的持久化 invariant。

数据库的主要工具就是：

Data Type
NOT NULL
PRIMARY KEY
UNIQUE
FOREIGN KEY
CHECK
EXCLUDE / partial unique 等 PostgreSQL 能力

Data Type：第一层约束

类型不是只为了让 ORM 生成正确 Go 字段。

例如：

uuid
boolean
integer
timestamptz
jsonb
text
vector

类型决定：

• 能保存什么；
• 有哪些运算符；
• 排序/比较语义；
• 可用什么 index/operator class；
• application ↔ DB 如何编码。

例如“时间点”如果用无时区字符串乱存，后续排序和跨时区语义会比 timestamptz 更难保证。

NULL 与 NOT NULL：NULL 不是空字符串

NULL 表示“没有这个值 / 未知”，而不是：

""
0
false

所以：

email TEXT NOT NULL

表达的是：

这一事实存在时，email 不能处于缺失状态。

是否允许 NULL 是 domain modeling，而不是随手决定。

Primary Key、Unique Key、Business Key

Primary Key

Primary Key 表达：

哪些列能够唯一识别一行。

它同时要求 unique + not null。

BodySense 很多表用 UUID 作为技术身份。

UNIQUE

Unique 保护的是业务不变量。

例如：

UNIQUE (user_id, revision)

表达：

同一用户不能存在两个相同 revision。

PostgreSQL 会用 unique index 来执行唯一性检查。

这不只是“方便查重”，还是一个并发裁决器：

Request A checks missing
Request B checks missing
A INSERT
B INSERT
→ database UNIQUE 最终只允许合法状态

NULL 与 UNIQUE 的细节

PostgreSQL 默认 unique constraint 中多个 NULL 通常不被视为彼此相等，因此可能出现多行 NULL。

PostgreSQL 也支持 NULLS NOT DISTINCT 来让 NULL 在唯一约束语义中视为相同。

不要把“UNIQUE 就什么都只能一份”想得过于简单，要考虑 nullable semantics。

Foreign Key：把“引用必须存在”交给数据库

例如：

conversation_id UUID REFERENCES conversations(id)

表达：

message 不能引用一个数据库中不存在的 conversation。

这是 Referential Integrity。

一个常见误解：声明 FK 会自动给引用列建索引

PostgreSQL 会保证被引用目标是 PK/UNIQUE 等可唯一定位的列；但引用方 FK 列不会因为声明 FOREIGN KEY 就自动获得索引。

如果父表 DELETE/UPDATE 或业务频繁按 FK JOIN/filter：

messages.conversation_id

常常值得单独建 index。

是否建仍应由访问模式决定。

CHECK：把行内谓词变成数据库规则

例如：

CHECK (subject_bucket >= 0 AND subject_bucket < 10000)

每次 INSERT/UPDATE 都必须满足。

CHECK 的边界

PostgreSQL 官方明确不建议用普通 CHECK 去引用其他行/其他表，以此维护跨行长期一致性。

跨行/跨表规则优先考虑：

UNIQUE
FOREIGN KEY
EXCLUDE
transaction logic
trigger（确有必要）

Cardinality：先说业务关系，再决定 FK 放哪

Cardinality 描述实体之间的数量关系。

1 : 1

User 1 ─── 1 Profile

常见实现：

profile.user_id FK + UNIQUE
或
profile.user_id 同时就是 PK

1 : N

Conversation 1 ─── N Messages

通常 N 侧：

messages.conversation_id FK

N : M

User N ─── M Role

关系本身需要第三张表：

user_roles
├─ user_id
├─ role_id
└─ assigned_at

连接表之所以重要，是因为“关系”本身也可能有属性和生命周期。

Normalization：为什么要减少重复事实

假设每条 message 都重复保存：

user_email
user_display_name
conversation_title

用户改一次名字，你可能要更新几十万行。

如果漏了一部分：

同一个 user
→ 多个不一致名字

这就是 Update Anomaly。

Normalization 的主要目标，是让一个事实尽量有一个明确 owner。

1NF / 2NF / 3NF 不要先背口诀

初学阶段先抓因果：

一格里不要塞不可查询的重复组
→ 每个事实依赖正确的 Key
→ 非 Key 属性不要依赖别的非 Key 属性造成重复事实

面试需要时再精确展开范式定义。

Denormalization 为什么仍然存在

生产系统并不是“表越碎越好”。

Read Model、snapshot、事件 payload 可能有意重复：

normalized write truth
→ derived denormalized read projection

关键区别是：

重复是无意的数据漂移，还是有明确 owner / rebuild / invalidation 规则的派生数据？

JSONB：Schema-less 不是“没有 Schema”

JSONB 很灵活，但业务仍然有隐含 schema。

适合：

开放 metadata
冻结过往 Agent output
版本化 payload
低频内部字段
形状经常演进的扩展数据

不适合默认承载：

核心 PK/FK
稳定 status
高频 filter/order/join 字段
必须数据库强约束的业务不变量

选择 JSONB 本质是在选择：

更灵活的 shape
↔
更弱/更复杂的关系约束和字段级治理

1. Constraint 是数据库里的业务防线

常用约束：

PRIMARY KEY → 行身份唯一且非空
FOREIGN KEY → 引用对象必须存在
UNIQUE      → 某个业务键不能重复
NOT NULL    → 该事实不能缺失
CHECK       → 值必须满足业务谓词

例如 BodySense 的 rollout subject_bucket 可以用 CHECK 限制范围；Session phase 可以用 CHECK 限制状态集合。

2. Cardinality 决定表结构

1 : 1 → 常用 UNIQUE FK 或同主键
1 : N → N 侧持有 FK
N : M → 中间连接表，两侧 FK + 关系自身属性

不要把所有关系都塞成 JSONB array。关系若需要独立约束、查询、生命周期或引用，通常应成为独立 relation。

3. Normalization 解决什么

规范化不是“表越多越高级”，而是减少：

update anomaly
insert anomaly
delete anomaly
重复事实漂移

但生产 read model 可以有受控 denormalization，例如 durable projection、冗余计数或 frozen snapshot，只要 ownership 和更新规则明确。

4. JSONB 的正确边界

JSONB 适合：

• 结构本身具有开放/版本化属性；
• 整体读取多于按内部字段建立强关系；
• 需要保留原始/冻结 payload；
• 不值得为每个可选属性增加稳定列。

不适合：

• 核心唯一性/外键关系；
• 经常 join/filter/order 的稳定字段；
• 需要严格列类型与约束的主业务状态。

Prediction

[!question]

1. user_id 只在 Go 校验存在，但数据库没有 FK，会失去什么？
2. UNIQUE(plan_id, check_in_date) 比“接口先 SELECT 再 INSERT”强在哪里？
3. status 是稳定状态机字段，全部塞到 JSONB 是否合适？
4. N:M 关系为什么通常需要连接表，而不是双方各存一个 ID 数组？
5. “范式化越高越好”是否正确？

[!success]- Answer / 解析 1. 会失去数据库级 referential integrity。 任何绕过当前 service 的写入都可能产生孤儿行。

2. UNIQUE 在并发下仍由数据库原子裁决。 SELECT → INSERT 会出现两个事务都先看到不存在，然后一起插入的 race。

3. 通常不合适。 稳定、频繁过滤且需要合法值约束的状态更适合明确列 + CHECK；JSONB 更适合扩展 metadata。

4. 连接本身也是事实。 它可以有创建时间、角色、排序、状态等属性，并且能被 FK/UNIQUE 约束。

5. 不正确。 规范化用于消除事实重复和异常；读性能、历史 snapshot、事件 payload 等场景可能需要有意 denormalization，但必须有明确维护规则。

Lab

在 BodySense baseline 中挑 5 个 CHECK / UNIQUE / FK，分别写出它们保护的业务 invariant。再挑 2 个 JSONB 字段，说明为什么它们暂时不拆表。

L4 Gate

给一个新需求“用户可以保存多个训练目标，但同一种 goal_type 同时只能有一个 active”，设计表、FK、UNIQUE/partial unique 或 CHECK，并解释并发写入时谁负责最终裁决。