- name
- db-design
- description
- Deep database design workflow—entities and relationships, keys and constraints, normalization vs denormalization, indexing strategy, integrity, and operational concerns. Use when designing OLTP schemas or reviewing greenfield data models.
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_idon 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.