Database Design

Database Design

Database design turns a domain into a schema that stays fast, correct, and easy to evolve for years. This skill covers the full pipeline — requirements analysis, entity modeling, normalization, keys, constraints, indexes, and safe migrations — plus deciding when a document, wide-column, or graph store beats a relational database.

Quick Reference

Core Workflow

Work through these steps in order — each informs the next, and skipping requirements analysis is the most common source of bad schemas.

1. Gather requirements. List entities, attributes, and relationships. Note cardinalities and whether each read is CRUD (single-row) or reporting (aggregate). Ask about scale, growth, retention, and compliance (e.g. GDPR erasure).
2. Model entities and relationships. Draw an ER sketch. Normalize to 3NF by default; denormalize only later, with a measured reason.
3. Choose keys. Prefer surrogate BIGINT IDENTITY primary keys unless a natural key is genuinely stable. Decide UUID vs bigint now — changing it later is painful.
4. Add constraints. NOT NULL, UNIQUE, CHECK, defaults, and foreign keys belong in the database, not just in the ORM.
5. Design indexes. Start with the indexes backing PK/FK/UNIQUE constraints, then add query-driven indexes for the hot paths found in step 1.
6. Validate with EXPLAIN. Run EXPLAIN (ANALYZE, BUFFERS) against realistic data. Fix the plan, not the schema, before considering denormalization.
7. Plan migrations. Versioned migrations in code; expand-contract for anything touching a live table. Never hand-edit production.

Requirements Analysis

Collect these before writing a single CREATE TABLE:

CRUD vs reporting reads want opposite schema shapes: CRUD queries touch few rows, narrow columns, and normalized tables; reporting wants few tables, wide pre-joined rows, and precomputed aggregates. If both matter, keep the OLTP schema normalized and serve reporting from a separate path (materialized views, a read replica, or a warehouse). Cardinality:

Normalization (1NF–3NF)

Normalization removes redundancy so each fact is stored and updated in exactly one place.

Normalize to 3NF by default. Denormalize deliberately — copy or aggregate data to serve a specific hot read — only when a measured query needs it. The costs are real: write amplification (every write maintains the copies), risk of drift, and backfill work whenever the derivation logic changes. Full worked example from spreadsheet to 3NF: normalization-and-keys.md.

Keys

Natural vs surrogate:

Foreign keys and referential actions:

UUID vs bigint (full comparison in normalization-and-keys.md): bigint is 8 bytes, sequential, and cache-friendly — prefer it for high-write OLTP. UUIDv7 (time-ordered) is the choice for distributed or client-generated IDs where a central sequence is impossible. Avoid random UUIDv4 as a PK on hot tables: random inserts thrash the B-tree and bloat indexes.

Indexing

A B-tree index is an ordered copy of one or more columns that lets the engine find rows without scanning the whole table — O(log n) equality lookups and efficient range scans. Design indexes for your queries, not for every column.

• Composite index column order matters. Put equality columns first, then range/ORDER BY columns: (customer_id, created_at) serves WHERE customer_id = ? AND created_at > ?. A leading range column makes the later columns useless for lookups.
• Covering indexes include every column the query touches (INCLUDE (status)) so the engine can answer from the index alone (index-only scan).
• Partial indexes carry a WHERE clause — e.g. WHERE status = 'pending' — keeping a hot subset small: faster scans, fewer writes to maintain.
• When an index hurts: every INSERT/UPDATE/DELETE maintains every index on the table (write amplification); indexes consume disk and buffer cache; unused indexes still cost writes. Remove indexes no query uses.
• EXPLAIN is the source of truth. EXPLAIN (ANALYZE, BUFFERS) on a slow query shows Seq Scan vs Index Scan vs joins and where time actually goes. Reading guide: indexing-and-query-tuning.md.

Constraints

Constraints make the database enforce your invariants, so app bugs and concurrent writers cannot corrupt data:

Why they belong in the DB rather than the ORM: enforced under concurrency, enforced for every code path (imports, ad-hoc SQL, future apps), and they document the schema's contract. ORM-level validation is a convenience; DB constraints are the guarantee.

Transactions and Isolation

ACID transactions keep multi-step writes atomic. The isolation level is a consistency-vs-throughput tradeoff:

Locking strategies:

• Pessimistic: SELECT ... FOR UPDATE locks rows so no one else modifies them until commit. Simple to reason about; hurts under contention.
• Optimistic: read a version/rowversion column and UPDATE only if unchanged (WHERE version = ?), retrying on conflict. Scales better; needs retry logic.

Rule of thumb: start at read committed; raise the level only for a concrete anomaly (e.g. financial invariants); prefer optimistic locking over long-held locks.

Migrations

Schema changes are code: versioned, reviewed, tested, and applied exactly once in order. Tools: Alembic (Python/SQLAlchemy), Prisma Migrate (TypeScript), Flyway (SQL-first, JVM/CLI).

# Alembic
alembic revision --autogenerate -m "add orders table"
alembic upgrade head

# Prisma
prisma migrate dev --name add_orders

# Flyway (versioned SQL files, e.g. V2__add_orders.sql)
flyway migrate

Expand–contract (a.k.a. expand–migrate–contract) is the zero-downtime pattern for anything touching a live table:

1. Expand — add the new column/table as additive (nullable or new); deploy code that writes both old and new.
2. Backfill — populate the new structure in bounded batches without blocking writes.
3. Contract — switch reads, then writes, then drop the old structure once nothing references it.

-- 1. Expand: add nullable column, dual-write in the app
ALTER TABLE orders ADD COLUMN total_cents BIGINT;

-- 2. Backfill in batches
UPDATE orders SET total_cents = /* recompute from items */
WHERE id BETWEEN :start AND :end AND total_cents IS NULL;

-- 3. Contract: flip reads, then drop the old column
ALTER TABLE orders ALTER COLUMN total_cents SET NOT NULL;
-- later: ALTER TABLE orders DROP COLUMN legacy_total;

Backfilling runs in bounded batches (WHERE id > :last_id ORDER BY id LIMIT 1000) so it neither locks the table for minutes nor floods the WAL. Never backfill a large table with a single unbounded UPDATE. Full guide with locking gotchas: migrations-zero-downtime.md.

SQL vs NoSQL

Start from the workload, not the hype:

A pragmatic middle path: keep the relational DB as the system of record and add specialized stores (search, cache, analytics warehouse) around it — don't fork the domain model across stores unnecessarily.

Practical Patterns

-- N+1 anti-pattern: 1 query for orders + N queries for order_items. Fix: one query
SELECT o.*, oi.*
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.customer_id = $1;

Worked Example: Small E-Commerce

Domain: customers, products (each in many categories), orders (many items, each item is one product). Requirements: order CRUD, product browsing by category, "recent orders for a customer" reporting, and a search box.

erDiagram
    CUSTOMER ||--o{ "ORDER" : places
    "ORDER" ||--|{ ORDER_ITEM : contains
    PRODUCT ||--o{ ORDER_ITEM : "is ordered as"
    PRODUCT ||--o{ PRODUCT_CATEGORY : has
    CATEGORY ||--o{ PRODUCT_CATEGORY : has
    CUSTOMER {
        bigint id PK
        text email UK
        text name
        timestamptz created_at
    }
    "ORDER" {
        bigint id PK
        bigint customer_id FK
        text status
        bigint total_cents
        timestamptz created_at
    }
    ORDER_ITEM {
        bigint order_id PK,FK
        bigint product_id PK,FK
        int quantity
        bigint unit_price_cents
    }
    PRODUCT_CATEGORY {
        bigint product_id PK,FK
        bigint category_id PK,FK
    }
    PRODUCT {
        bigint id PK
        text sku UK
        text name
        text description
        tsvector search_vector
    }
    CATEGORY {
        bigint id PK
        text slug UK
    }

Key decisions:

• Keys: surrogate bigint PKs everywhere; sku, email, slug as UNIQUE natural keys for lookups.
• Normalization: 3NF — product and category are separate with a junction table. orders.total_cents is a deliberate denormalized aggregate, recomputed in the same transaction as its items.
• Constraints: CHECK quantity > 0 and unit_price_cents >= 0; CHECK on status; FK ON DELETE RESTRICT for products/categories, CASCADE for order items; the junction's composite PK already enforces uniqueness.
• Indexes:

-- Hot paths
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC); -- "recent orders" (equality first, then sort)
CREATE INDEX idx_order_items_order ON order_items (order_id);                      -- order detail
CREATE INDEX idx_products_search ON products USING gin (search_vector);            -- search box (tsvector kept fresh by a trigger)
CREATE INDEX idx_orders_status_created ON orders (status, created_at)
    WHERE status = 'pending';                                                      -- partial: tiny fulfillment queue

The composite (customer_id, created_at) serves both WHERE customer_id = ? and ORDER BY created_at DESC. The partial index stays tiny because only pending orders enter the queue.

Anti-Patterns to Avoid

• No requirements analysis — building tables before knowing queries, cardinalities, and read patterns.
• Premature denormalization — copying data "for performance" before EXPLAIN shows a problem.
• Mutable natural keys as PK (email, username) — churn breaks every referencing row.
• Random UUIDv4 PKs on hot tables — random inserts fragment the B-tree and bloat indexes.
• Index on every column — each index costs writes; unused ones are pure overhead.
• Constraints only in the ORM — race conditions and raw SQL bypass validation.
• Soft deletes without a plan — deleted_at silently breaks UNIQUE constraints, and every query needs WHERE deleted_at IS NULL.
• SELECT * in production queries — defeats covering indexes and widens scans.
• Giant or hand-applied migrations — schema changes must be small, versioned, and reversible.
• EAV (entity-attribute-value) tables — "flexible" schemas that make every query painful.

When to Use / Not Use

Use this skill when:

• Designing a new schema from requirements or a domain description
• Reviewing an existing schema for normalization, key, constraint, or index problems
• Deciding between SQL and NoSQL for a workload
• Planning a migration, backfill, or zero-downtime schema change
• Tuning slow queries with EXPLAIN

Do NOT use when:

• The task is only writing queries against an existing, settled schema (no design decisions)
• You need a runbook for a specific DB's operations (backups, failover, tuning knobs) — that is ops, not design
• Deep implementation work inside a NoSQL store (DynamoDB partition keys, Cassandra compaction) — this skill covers choosing the store, not its internal best practices
• The user just needs boilerplate ORM models generated from an already-decided schema