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.