# Database Design

> Schema design, data modeling, migrations, and query correctness for relational databases (and when to use non-relational). Use when creating or altering tables, designing schemas, writing migrations, modeling money/inventory/ledgers, adding indexes, or when the user says "schema", "data model", "migration", "database design", or "normalize".

- Skill: `05-deepak-patidar/database-design` (Agent Skill)
- Install (CLI): `npx skillmds@latest add 05-deepak-patidar/database-design`
- Raw SKILL.md: https://api.skillmd.com/api/skills/05-deepak-patidar/database-design/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- Author: 05-deepak-patidar (https://skillmd.com/u/05-deepak-patidar)
- Updated: 2026-09-21
- Page: https://skillmd.com/skills/05-deepak-patidar/database-design

---


# Database Design

The schema outlives every framework, every rewrite, and every AI model that touches the codebase. Code bugs ship and get fixed; schema mistakes and corrupted data are forever. Design the schema as if the application code were hostile — because one day, some version of it will be.

## Principle: the database defends its own invariants

Any rule the business cannot tolerate being violated goes **in the schema**, not only in application code:

- `NOT NULL` by default; nullable is an explicit decision meaning "absence is a valid state" (and every reader must handle it).
- `FOREIGN KEY` for every reference. `UNIQUE` for every natural key (mobile number, invoice number per tenant, SKU per account). `CHECK` for domains (quantity >= 0, status in allowed set, rate between 0 and 100).
- Application-level checks are UX; database constraints are truth. You need both, but only one of them holds under race conditions, bad deploys, and manual fixes.

## Modeling rules that prevent the classic disasters

- **Money and quantity are exact decimals** (`NUMERIC`), never float — no exceptions, including "it's just a percentage". Store currency explicitly if there could ever be more than one.
- **Timestamps**: store UTC (`timestamptz`), render in local zone. Name columns `*_at`. Every mutable table gets `created_at`/`updated_at`.
- **IDs**: surrogate primary key (UUID or bigint — pick per project and stay consistent); enforce natural keys with UNIQUE constraints, don't make them the PK. Never expose sequential IDs where enumeration leaks business volume (invoice counts, user counts) unless numbering is a product requirement.
- **State machines as data**: a `status` column needs its legal transitions written down (in code comments/docs) and enforced in exactly one service function. If history matters, an append-only events/audit table beats overwriting.
- **Financial and inventory data is append-only at heart**: model corrections as new compensating rows (credit notes, stock adjustments with reasons), not UPDATEs that destroy what happened. If a regulator, accountant, or angry customer could ask "what was it before?", keep the before.
- **Soft delete vs hard delete** is a product decision — but referencing rows must never dangle either way; decide `ON DELETE` behavior per FK deliberately (RESTRICT is the safe default).
- **Multi-tenant**: `account_id NOT NULL` on every tenant table, composite indexes leading with it, and row-level security or a mandatory scoping mechanism so a missing WHERE clause fails closed, not open.

## Normalization: the practical rule

Normalize until it hurts (one fact, one place — eliminate update anomalies), then denormalize only with a named owner for the copy and its update path. Storing `line_total = qty × rate` is fine for immutable invoice lines (they're history, frozen at sale time — that's not denormalization, that's a snapshot). Storing a customer's "current balance" is a cache — it needs either a recomputation job or transactional dual-writes, and a way to audit drift.

## Concurrency: assume two requests arrive at once

- Any read-modify-write (stock decrement, balance update, counter, "next invoice number") needs an explicit strategy: atomic single UPDATE (`SET stock = stock - :n WHERE stock >= :n`), `SELECT ... FOR UPDATE`, or a serializable/retry pattern. "We'll be small" doesn't prevent double-submits from one user's double-click.
- Uniqueness under concurrency is a constraint + handled violation, not check-then-insert.
- Multi-row financial writes (invoice + lines + stock + ledger) are **one transaction, all or nothing** — and keep transactions short; never call external APIs inside one.

## Indexes and queries

- Index every FK, every column in frequent WHERE/ORDER BY, and tenant-scoped composites `(account_id, x)`. But each index taxes every write — add them for observed query shapes, not superstition.
- Before shipping a list endpoint: it must paginate (keyset for large/growing sets), and its query must not be N+1 (verify by looking at emitted SQL once, not by assuming the ORM is smart).
- `EXPLAIN` any query you're about to optimize; never index-guess.

## Migration discipline

- Migrations are code-reviewed, hand-verified artifacts — whether hand-written or generated, read every line and know its lock behavior before it touches production data.
- Additive first; destructive only after code no longer reads the old shape (see deployment-safety: expand → migrate → contract).
- Every migration answers: is it idempotent/re-runnable? does it run safely while old code is live? what's the down-path or restore plan? does the backfill batch (not one giant UPDATE locking the table)?

## When someone says "let's use NoSQL / JSONB for this"

JSONB/document columns are right for genuinely schemaless payloads you don't query relationally (provider webhook dumps, user-defined custom fields). They are wrong for anything with relationships, constraints, or reporting needs — that's just a schema you've decided not to enforce. Default to relational; earn your way out.