DB Design

Good OLTP design balances integrity, write paths, query patterns, and evolution—not “third normal form everywhere.”

When to Offer This Workflow

Trigger conditions:

• Greenfield service schema or major new domain
• Performance or integrity issues from ad-hoc tables
• Multi-tenant isolation questions

Initial offer:

Use six stages: (1) domain & access patterns, (2) entities & relationships, (3) keys & constraints, (4) normalization trade-offs, (5) indexing & performance, (6) operations & evolution). Confirm RDBMS and scale expectations.

Stage 1: Domain & Access Patterns

Goal: List critical queries and writes: QPS, joins, filters, hot rows.

Exit condition: Top access paths ranked by business importance.

Stage 2: Entities & Relationships

Goal: ER model; cardinality; optional vs required relationships.

Practices

• Clear table names; avoid opaque “data” blobs unless documented

Stage 3: Keys & Constraints

Goal: Primary keys (surrogate vs natural); foreign keys with explicit ON DELETE policy; unique constraints for business rules.

Multi-tenant

• tenant_id on rows that need isolation; composite keys or indexes as appropriate

Stage 4: Normalization Trade-offs

Goal: Normalize to reduce update anomalies; denormalize read hotspots with documented trade-offs.

Stage 5: Indexing & Performance

Goal: Indexes serve real queries; watch write amplification and index bloat.

Stage 6: Operations & Evolution

Goal: Migration strategy (expand/contract); backup/restore; PII columns flagged.

Final Review Checklist

• [ ] Access patterns drive schema
• [ ] Keys, FKs, and constraints explicit
• [ ] Multi-tenant isolation if applicable
• [ ] Normalization decisions justified
• [ ] Index plan aligned with queries
• [ ] Migration and ops considerations noted

Tips for Effective Guidance

• NULL semantics and defaults matter for bugs and migrations.
• Pair with db-migrate for online schema changes.

Handling Deviations

• Document stores: embed vs reference with consistency story.