Schema Design

Schema Design

Design the data model that sits beneath the API and beside the domain language. architecture picks the datastore type; domain-modeling sharpens the vocabulary; this skill designs the actual shape of the data — entities, relationships, indexes, constraints, and how the model scales. A schema designed reactively (added-to-as-features-arrive) becomes a performance and correctness liability that is expensive to fix later.

When to use

• Designing entities and relationships for a new feature or bounded context
• Choosing normalization level, indexing strategy, or partitioning for a new model
• Reviewing a proposed schema for correctness, performance, or future evolution
• Triggers on "data model", "schema design", "database design", "ER model", "数据模型", "表结构设计", "数据库设计"

Scope: assumes a relational datastore (Postgres/MySQL/SQLite). For document stores (MongoDB), key-value (DynamoDB), or graph stores, the embedding-vs-referencing and sharding decisions differ — use architecture for datastore selection, then adapt these patterns.

Not for: choosing the datastore type (use architecture); ubiquitous-language glossary (use domain-modeling); API request/response shapes (use api-design); changing an existing schema in production (use deprecation-migration).

Steps

1. Map entities and relationships

Start from the domain model (domain-modeling output / CONTEXT.md) and translate nouns into entities, verbs into relationships. For each entity, identify its identity (primary key), its attributes, and its lifecycle (created, mutated, archived, deleted).

• Draw an ER diagram (Mermaid erDiagram — see ${CLAUDE_PLUGIN_ROOT}/references/mermaid-diagrams.md)
• Cardinality for every relationship: one-to-one, one-to-many, many-to-many
• Resolve many-to-many with junction tables; never hide them in a comma-separated column

Verify: every entity in the domain model is represented; every relationship has documented cardinality.

2. Choose normalization level

Normalize by default (3NF / BCNF) — it eliminates anomalies and keeps the model honest. Denormalize deliberately, only when a measured read pattern justifies the write complexity and consistency risk. Document every denormalization as a trade-off: what read it optimizes, what write it complicates, how consistency is maintained.

• Foreign keys for every relationship (enforce referential integrity at the DB level)
• Surrogate keys (UUID/serial) over natural keys unless the natural key is truly immutable
• NULL semantics: every nullable column has a documented meaning — "unknown" vs. "none" vs. "not yet"

Verify: every FK is enforced; every denormalization is documented with its trade-off.

3. Design indexes

Index for the queries, not the columns. Start from the access patterns (how will this data be read?), then create indexes that serve them. Every index has a cost — write amplification and storage — so justify each one against a concrete query.

• Composite indexes ordered by selectivity and equality-before-range
• Covering indexes for hot read paths (include columns to avoid table lookups)
• Partial / filtered indexes for common WHERE predicates (e.g. WHERE deleted_at IS NULL)
• Unique indexes to enforce business invariants (one active subscription per user)

Verify: every index maps to a named query; EXPLAIN on each access pattern uses an index, not a seq scan.

4. Define constraints and invariants

The database is the last line of defense for data integrity. Push invariants into constraints — they are enforced regardless of application bugs:

• NOT NULL unless the column genuinely can be absent
• CHECK constraints for range/format invariants (price ≥ 0, status in allowed set)
• UNIQUE constraints for business keys (email, slug, active-session-per-user)
• Foreign key actions: ON DELETE / ON UPDATE (RESTRICT / CASCADE / SET NULL) chosen deliberately

Verify: every business invariant is backed by a constraint, not just application logic.

5. Plan for scale and evolution

Design the model to survive growth without a painful migration:

• Partitioning strategy for large tables (range by date, hash by tenant) — decide before the table hits 100M rows, not after
• Avoid hot writes to a single sequence (use scattered keys or per-tenant sequences at scale)
• Soft delete (deleted_at timestamp) for audit-relevant data; hard delete for ephemeral data — choose per entity, document why
• Forward-compatible types: timestamptz (not timestamp), UUID or bigint (not int for IDs that may exceed 2B)

Verify: partitioning decision is documented; ID types won't overflow; soft/hard-delete policy is stated per entity.

6. Document

Output: docs/design/SCHEMA.md — the data model document: ER diagram, entity definitions with columns/types/constraints, index plan mapped to access patterns, partitioning strategy, and any denormalization trade-offs. Reference ADRs for significant model decisions.

Verify

• ER diagram drawn (Mermaid); every entity and relationship documented
• Normalization level stated; every denormalization documented with trade-off
• Foreign keys enforced at DB level; referential integrity does not rely on application code
• Indexes mapped to named queries; EXPLAIN confirms index usage on access patterns
• Business invariants backed by CHECK / UNIQUE constraints
• ID types won't overflow; partitioning strategy stated for growth tables
• docs/design/SCHEMA.md produced with ER diagram + entity/index/constraint plan

Red flags: comma-separated lists in a column; missing foreign keys; indexing every column (or no indexes); SELECT * driving the schema; int IDs on tables that will grow; NULL with no documented meaning; relying solely on application code for integrity; no partitioning plan for a table that will exceed memory.

References

• ${CLAUDE_PLUGIN_ROOT}/references/engineering-principles.md — shared discipline (surface assumptions, enforce simplicity, verify don't assume)
• ${CLAUDE_PLUGIN_ROOT}/references/mermaid-diagrams.md — Mermaid erDiagram syntax for entity-relationship diagrams
• references/schema-patterns.md — normalization trade-offs, index patterns, partitioning strategies, soft-delete policies