References: Eric Evans, Domain-Driven Design; E.F. Codd's normalization theory; PostgreSQL documentation; MySQL design guidelines; Stripe / GitHub data modeling practices.
Applicable Scenarios
- Extract entities from PRDs, API contracts, or business rules
- Design tables, fields, field types, default values, and nullability rules
- Design primary keys, foreign keys, unique constraints, and check constraints
- Decide between normalization, denormalization, and redundant fields
- Refactor existing table structures / split tables / merge tables
- Output DDL or migration drafts
Core Principles
1. 先表达业务事实，再考虑页面展示
Schema 是业务的"记忆"，不是 UI 的镜像
-
Clear entity boundaries and explicit relationship cardinality One-to-one / one-to-many / many-to-many / polymorphic must all be explicit
-
Constraints live at the database layer Facts that must be unique in the business must have a UNIQUE fallback Fields that do not allow NULL must be NOT NULL Amounts that must be positive must have CHECK > 0
-
Field semantics consistent with API/backend Naming, types, enum values, and nullability rules stay in sync
-
Migratable, rollback-capable, verifiable First think through how to change / how to revert / how to verify consistency
-
Three time fields are mandatory created_at / updated_at / business time (such as paid_at)
-
Use soft deletes with caution deleted_at looks harmless, but it breaks unique constraints
## 标准流程
```text
1. 读取 PRD / API 契约 / 现有 schema
↓
2. 识别实体（DDD 视角）
- 聚合根（Aggregate Root）
- 实体（Entity）
- 值对象（Value Object）
- 关系（Association）
↓
3. 画 ER 图（Mermaid 或工具）
↓
4. 设计表和字段
- 命名规范
- 类型选择
- 必填 / 默认值
↓
5. 设计主键、外键、唯一约束、检查约束
↓
6. 标注空值、默认值、枚举、时间字段、审计字段
↓
7. 评估规范化与必要冗余
↓
8. 输出 schema 说明 + DDL 草案 + 待确认问题
Naming Conventions
表名：
✅ orders （复数）
✅ order_items （子资源 + 复数）
❌ Order （首字母大写）
❌ tbl_order （冗余前缀）
字段名：
✅ user_id （snake_case）
✅ created_at （时间用 _at）
✅ is_active （布尔用 is_）
✅ amount_cents （金额单位明确）
❌ userId （camelCase 混用）
❌ time （含义不清）
❌ amount （单位不明）
主键：
✅ id （bigint / uuid，统一）
❌ order_id （表内自指用 id 即可）
外键：
✅ user_id （引用 users.id）
✅ created_by （引用人，业务语义）
Field Type Selection
Numeric
| Business Field | Recommended Type | Reason |
|---|---|---|
| Primary key / ID | bigint or uuid |
int caps at 2.1 billion, not enough |
| Amount | bigint(smallest unit / cents) or numeric(19,4) |
Never use float / double |
| Count | integer |
Generally sufficient |
| Rating / ratio | numeric(5,2) |
Controllable precision |
Strings
| Business Field | Recommended Type | Reason |
|---|---|---|
| Short identifier (≤32 characters) | varchar(64) |
Leave buffer |
varchar(255) |
RFC 5321 upper limit | |
| URL | varchar(2048) or text |
Depending on actual use |
| Long text | text |
No length limit (PG) |
| Rich text / HTML | text |
Same as above |
| Tag array | text[]（PG）/ JSON（MySQL） |
Avoids a separate join table (for small data volumes) |
Time
| Field | Recommended Type | Note |
|---|---|---|
| created_at / updated_at | timestamp with time zone |
Always UTC, frontend converts to local |
| Date (birthday / deadline) | date |
Without time |
| Duration (seconds) | integer |
Do not use interval (incompatible across databases) |
Enums
-- 推荐：用 varchar + CHECK 约束（演进灵活）
status varchar(32) NOT NULL CHECK (status IN ('draft','submitted','paid','cancelled'))
-- 不推荐：PostgreSQL ENUM 类型
-- 优点：类型安全
-- 缺点：增删值困难、跨库不兼容
JSON
-- 适合：低频查询的扩展字段、配置、metadata
metadata jsonb DEFAULT '{}' NOT NULL
-- 不适合：核心查询字段、需要排序聚合的字段
-- 反例：把 status 塞 metadata，每次查询都要 metadata->>'status'
Primary Key Strategy
方案 A：自增整数（bigint）
优点：简单、有序、占空间小
缺点：暴露规模、可枚举（防爬）
适合：内部系统
方案 B：UUID v4
优点：不可枚举、分布式友好、无热点
缺点：占 16 字节、无序导致 B-tree 写放大
适合：分布式系统
方案 C：UUID v7 / ULID（推荐）
优点：时间有序 + 不可枚举 + 分布式友好
缺点：较新（2024 年标准化）
适合：新项目
方案 D：业务前缀 + 随机 ID
Stripe 风格：`cus_abc123`、`ord_xyz789`
优点：可读、可识别类型
缺点：自定义实现
适合：对外 API
Relationship Modeling
One-to-many (most common)
-- 订单属于用户
CREATE TABLE orders (
id bigserial PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users(id),
...
);
-- 索引子表的外键（必加）
CREATE INDEX idx_orders_user_id ON orders(user_id);
Many-to-many
-- 用户和角色的多对多关系
CREATE TABLE user_roles (
user_id bigint NOT NULL REFERENCES users(id),
role_id bigint NOT NULL REFERENCES roles(id),
granted_at timestamptz DEFAULT now(),
granted_by bigint REFERENCES users(id),
PRIMARY KEY (user_id, role_id)
);
Polymorphic (use with caution)
反模式（多态外键）：
comments(target_type, target_id) -- 没有 FK 约束，难维护
替代：每种类型独立外键
comments(post_id, user_id, ...)
post_comments / user_comments 单独表
Field Design Checklist
| Item | Checkpoint | Counterexample |
|---|---|---|
| Naming | Business semantics + consistent with API | data / info / time |
| Type | Match database capabilities and future growth | int for order numbers, float for amounts |
| Nullability | Whether NULL has business meaning | Default NULL (unclear semantics) |
| Default value | Affects historical data and migration | Adding a default value causes a full table lock |
| Enum | Extension strategy | PG ENUM is hard to add values to |
| Time | Create, update, delete, archive | Missing updated_at, no timezone |
| Audit | Operator, source, tenant, trace ID | When something goes wrong, the cause cannot be found |
| Length | String fields must have a limit | text is unlimited → DoS risk |
| Uniqueness | Business uniqueness → database UNIQUE | Deduplication only at the code layer |
Multi-tenant design
-- 每张租户数据表必有 tenant_id
CREATE TABLE orders (
id bigserial PRIMARY KEY,
tenant_id bigint NOT NULL REFERENCES tenants(id),
user_id bigint NOT NULL REFERENCES users(id),
...
-- 唯一性：复合（含 tenant_id）
CONSTRAINT uq_order_number UNIQUE (tenant_id, order_number)
);
-- 行级安全（PostgreSQL RLS，可选兜底）
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.tenant_id')::bigint);
See details consistency-multitenancy/SKILL.md。
Soft delete strategy
方案 A：deleted_at（软删除时间戳）
优点：可恢复、可审计
缺点：破坏唯一约束（同 email 的活跃用户和已删除用户）
解决：UNIQUE (email) WHERE deleted_at IS NULL （部分索引）
方案 B：归档表
优点：核心表干净、性能好
缺点：跨表查询历史复杂
方案 C：硬删除 + 审计日志表
优点：核心表清爽
缺点：不可"恢复"
推荐：核心业务用方案 A + 部分索引，审计敏感用方案 C
Audit fields (recommended for every table)
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
created_by bigint REFERENCES users(id),
updated_by bigint REFERENCES users(id),
version integer NOT NULL DEFAULT 1 -- 乐观锁
Normalization vs denormalization
默认规范化（3NF）：
- 不重复存储
- 修改一处不漏
- 节省空间
反规范化（合理冗余）：
- 冗余字段：order.user_email（避免 join 拿邮箱）
- 冗余汇总：user.order_count（避免每次 count）
- 物化视图：报表场景
权衡：
读多写少 → 可以反规范化
写多读少 → 严格规范化
报表 → 用物化视图 / 数据仓库分离
Supporting templates
templates/schema-design-template.md— Complete schema design document (background + ER + tables + constraints + indexes + migration + risks)templates/ddl-template.sql.md— DDL standard template
Quality self-check
□ 每张表是否有明确业务实体或关系含义
□ 每个字段是否有类型、空值、默认值、说明
□ 必须唯一的业务事实是否有 UNIQUE 约束
□ 关系是否有 FK 约束或明确的应用层约束说明
□ 枚举、状态、软删、审计字段是否明确
□ 三个时间字段（created_at/updated_at/业务时间）是否齐全
□ 多租户字段（tenant_id）+ 复合唯一约束
□ DDL 与目标数据库类型匹配
□ JSON 字段不承载核心查询
□ 主键策略明确（int/uuid/ULID/业务前缀）
□ 字符串字段都有长度上限
□ 字段命名风格统一（snake_case / camelCase 二选一）
□ 是否列出待确认问题和迁移影响
Common pitfalls
- Create tables only based on page fields——Ignoring the business lifecycle, high refactoring cost
- Uniqueness only in code——Concurrency / retries / imports produce dirty data
- JSON carries core queries——
metadata->>'status'full table scan - Default values cause full table locks——MySQL/PG lock the table when adding NOT NULL DEFAULT
- API/ORM/DB field names are inconsistent——Repeated rework during integration
- Amounts use float——Precision loss, finance doesn't reconcile
- Time without timezone——Time confusion for users across regions
- PG ENUM type——Hard to add values, painful cross-database migration
- Missing updated_at——No clues when troubleshooting
- Soft delete breaks unique constraints——The same email cannot be registered again
- Reflexive use of char(255)——Storing 1 character still takes 255
- Oversized text with no length limit——Attack surface / DoS
- Missing audit fields——When something goes wrong, the operator cannot be found
- Polymorphic foreign key——No FK constraint, hard to maintain
Collaboration with other skills
上游：
api-designer → 提供 API 契约 / 资源模型
product-manager → 提供业务实体 / 生命周期
ui-ux-designer → 提供页面字段需求
下游：
index-access-pattern → 根据访问模式补索引
migration-rollout → 把 DDL 转成可上线迁移
consistency-multitenancy → 补事务、租户、生命周期约束
query-review → 验证 SQL 与 schema 配合
backend-engineer → 实现 ORM 模型
Related references
references/schema-design-guide.md— DDD entity identification, normalization theory, PostgreSQL/MySQL type comparison, big-tech modeling paradigms (Stripe / GitHub / Discord), evolution strategies