# DB Design

> Guides OLTP database schema design through six stages: domain and access patterns, entities and relationships, keys and constraints, normalization trade-offs, indexing and performance, and operations and evolution. Includes a final review checklist and tips for effective guidance.

- Skill: `luokai0/db-design` (Agent Skill, multi-file: 3 files)
- Install (CLI): `npx skillmds@latest add luokai0/db-design`
- Raw SKILL.md: https://api.skillmd.com/api/skills/luokai0/db-design/raw
- Safety review: PASS (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics, AI & ML, SQL & Databases
- Tags: Database Design, Indexing, Multi Tenant, Normalization, Oltp, Schema Design
- Author: luokai0 (https://skillmd.com/u/luokai0)
- Updated: 2026-08-22
- Page: https://skillmd.com/skills/luokai0/db-design

---


# 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.