database-design

name	database-design
description	Relational database design and SQL best practices for building software applications. Covers schema modeling, normalization, data types, keys, constraints, indexing, query performance and EXPLAIN, transactions, isolation and locking, migrations, and PostgreSQL/MySQL-specific gotchas and antipatterns. Use when designing a database schema, creating or altering tables, choosing data types or keys, adding indexes, writing or optimizing SQL queries, debugging slow queries or deadlocks, planning a migration, or choosing between Postgres and MySQL. Triggers on "design a schema", "create table", "add an index", "normalize", "foreign key", "slow query", "EXPLAIN", "N+1", "migration", "deadlock", "Postgres", "MySQL", "SQL best practices".
license	MIT
metadata	{"version":"1.0.0","author":"Chris Graffagnino"}

Database Design

A practical operating manual for designing relational schemas and writing correct, performant SQL when building software applications. Defaults target PostgreSQL 16+ and MySQL 8.0+ (InnoDB); differences are called out explicitly.

When to use this skill

Use it whenever you are about to touch persistence: creating or altering tables, choosing data types or keys, adding indexes, writing non-trivial queries, designing transactions, planning a migration, or diagnosing a slow query or deadlock. If the task is "store this data" or "make this query faster," this skill applies.

How to use this skill

1. Identify which phase you are in: model → constrain → index → query → operate. Each phase has a checklist below.
2. Work the checklist for that phase. Do not skip the constraint phase — it is the cheapest correctness you will ever buy.
3. For depth on any topic, open the matching file in references/ (listed at the bottom). Keep this file as the index; the references hold the detail.
4. State your target engine and version before giving engine-specific advice. Behavior differs between Postgres and MySQL, and between MySQL versions.

---

Core principles

1. Correctness lives in the database, not the application. Constraints, foreign keys, and the right data types are enforced for every writer — application code, migrations, manual fixes, and the next service someone bolts on. App-layer validation is a UX nicety, not a guarantee.
2. Model the data, then fit the queries. Start from a normalized model (aim for 3NF). Denormalize only against a measured, named query that the normalized model can't serve fast enough — and write down why.
3. Every table needs a key. A primary key that identifies a row, and constraints that make illegal states unrepresentable.
4. Indexes are a trade. They speed reads and cost write throughput, storage, and planning time. Add them for real access paths, not speculatively.
5. Measure before optimizing. EXPLAIN (ANALYZE, BUFFERS) (Postgres) or EXPLAIN ANALYZE (MySQL 8.0.18+) is the source of truth. Guesses about query plans are usually wrong.
6. Migrations are forward-only and online. Assume the table has production data and concurrent traffic. A migration that locks a large table or rewrites it in place is an outage.

---

Phase 1 — Model (schema design)

Goal: a schema that represents the domain truthfully and makes illegal states hard to express.

• List entities and relationships before writing DDL. One concept per table.
• Normalize to 3NF as the default: every non-key column depends on the key, the whole key, and nothing but the key. This removes update anomalies.
• Choose a primary key. Prefer a surrogate key (bigint identity, or UUIDv7 for distributed ID generation) unless a natural key is genuinely stable and immutable. Never key on something users can edit (email, username).
• Pick data types deliberately — the type is your first constraint:
  • Money: numeric/DECIMAL, never float/double (rounding errors) and never Postgres money.
  • Timestamps for real instants: timestamptz (Postgres) / TIMESTAMP (MySQL, UTC). Never timestamp without time zone for an instant.
  • Text: text/varchar without an arbitrary length cap unless the domain demands one. Avoid char(n).
  • Booleans: a real boolean, not 'Y'/'N' or 0/1 integers.
  • Enumerated sets: a lookup table with an FK (portable, queryable) or a native enum (compact, rigid to change).
• Make columns NOT NULL by default; allow NULL only when "unknown/absent" is a real, distinct state. NULL has three-valued logic that breaks =, NOT IN, and aggregates in surprising ways.
• Name consistently: snake_case, singular-or-plural table names (pick one), <table>_id for foreign keys.

→ Detail, normalization walkthrough, key strategies, and type tables: references/schema-design.md

Phase 2 — Constrain (integrity)

Goal: the database rejects bad data regardless of who writes it.

• Primary key on every table.
• Foreign keys for every relationship, with an explicit ON DELETE action (RESTRICT/CASCADE/SET NULL) chosen on purpose. In MySQL this requires InnoDB.
• Unique constraints for every real-world uniqueness rule (one email per user, one SKU per product).
• Check constraints for domain rules (price >= 0, status IN (...), end_at > start_at).
• Defaults for columns that have a sensible default, so inserts can't forget them.
• Decide where multi-row invariants live: a constraint, a trigger, or a transaction with the right isolation level (see Phase 4).

→ Constraint patterns, ON DELETE semantics, generated columns: references/schema-design.md

Phase 3 — Index (access paths)

Goal: every important query has a supporting index; nothing is over-indexed.

• Index foreign key columns — joins and cascading deletes need them (MySQL auto-indexes FKs; Postgres does not).
• Index columns used in WHERE, JOIN ON, and ORDER BY for hot queries.
• For multi-column predicates, build composite indexes in the right order: equality columns first, then the range/sort column. Column order matters — (a, b) serves WHERE a=? and WHERE a=? AND b=?, but not WHERE b=? alone.
• Consider covering indexes (include all selected columns) to enable index-only scans for hot read paths.
• Use partial/filtered indexes (Postgres WHERE, or a generated-column trick in MySQL) when queries always filter on a subset (e.g. WHERE deleted_at IS NULL).
• Don't index low-selectivity columns alone (a boolean, a status with two values) — the planner will skip them.
• After adding indexes, re-run EXPLAIN and confirm the planner uses them.

→ Index types (B-tree/GIN/GiST/BRIN), column ordering, and the index/write trade-off: references/indexing-and-performance.md

Phase 4 — Query (correctness + performance)

Goal: queries that return the right answer and use a sane plan.

• Read the plan: EXPLAIN (ANALYZE, BUFFERS) (Postgres) / EXPLAIN ANALYZE (MySQL). Look for sequential scans on big tables, bad row estimates, and nested loops over large inputs.
• Kill N+1 queries — one query per parent row is the most common app-level performance bug. Use a join or a single WHERE id = ANY(...)/IN (...).
• Be explicit with JOINs; know the difference between INNER, LEFT, and a missed join that fans out rows.
• Watch NULL semantics: NOT IN (subquery) returns nothing if the subquery yields a NULL. Prefer NOT EXISTS.
• Paginate with keyset/seek pagination (WHERE id > :last ORDER BY id LIMIT n) for deep pages, not OFFSET (which scans and discards).
• Don't wrap indexed columns in functions in the WHERE clause (WHERE lower(email) = ? defeats a plain index — index the expression instead).
• Run ANALYZE / keep statistics fresh so the planner has good estimates.

→ Plan reading, join strategies, antipattern catalog: references/indexing-and-performance.md and references/antipatterns.md

Phase 5 — Operate (transactions, concurrency, migrations)

Goal: correct behavior under concurrency, and safe schema change on live data.

• Choose an isolation level deliberately. Default is READ COMMITTED (Postgres) / REPEATABLE READ (MySQL InnoDB). Use SERIALIZABLE when you need invariants across rows and are ready to retry on serialization failures.
• Keep transactions short. Long transactions hold locks, bloat MVCC, and cause deadlocks.
• Acquire locks in a consistent order across the codebase to avoid deadlocks; be ready to catch and retry on deadlock/serialization errors.
• For migrations: add columns as nullable or with a non-blocking default, backfill in batches, then add constraints NOT VALID + VALIDATE (Postgres) to avoid long table locks. In MySQL prefer ALGORITHM=INPLACE/online DDL or a tool like gh-ost/pt-online-schema-change for large tables.
• Never do a blocking ALTER on a large hot table without checking the lock and rewrite behavior for that specific operation and engine version first.

→ Isolation levels, locking, MVCC, deadlocks, and safe-migration recipes: references/transactions-and-concurrency.md and references/migrations-and-operations.md

---

Reviewing existing code

Use this when the task is to audit existing DDL, a migration, or a query rather than design something new. It runs the five phases as a review lens: read what exists, find the gap, name the fix, cite the reference. State the target engine and version first — many findings depend on it.

Scope the review. Identify what you're looking at — table/schema DDL, a migration, a query, or an ORM model — and load the matching reference(s) before judging.

Walk the phases as checks. For each, report concrete findings (file/line where possible), not generic advice:

• Model — Does every table have a primary key? Are types right (numeric for money, timestamptz/UTC for instants, real boolean)? Any NULL columns that should be NOT NULL? Surrogate vs. natural key chosen sensibly? → references/schema-design.md
• Constrain — Foreign keys present for every relationship, each with a deliberate ON DELETE? Unique constraints for real-world uniqueness rules? Check constraints for domain rules? Integrity enforced in the DB, not just the app? → references/schema-design.md
• Index — FK columns indexed (Postgres won't do it for you)? Indexes for the hot WHERE/JOIN/ORDER BY paths? Composite indexes in equality-then-range order? Any redundant, unused, or low-selectivity indexes to drop? → references/indexing-and-performance.md
• Query — Run EXPLAIN (ANALYZE, BUFFERS) / EXPLAIN ANALYZE on hot queries. Seq scans on big tables? N+1 patterns? NOT IN over a nullable subquery? Functions wrapping indexed columns? OFFSET deep-pagination? → references/indexing-and-performance.md, references/antipatterns.md
• Operate — Transactions short and isolation chosen on purpose? Lock-ordering consistent? For migrations: is the DDL online/non-blocking for the table's size and engine, or will it lock a hot table? → references/transactions-and-concurrency.md, references/migrations-and-operations.md

Report findings by severity. Lead with correctness/data-integrity risks (missing constraints, wrong types, unsafe migrations), then performance, then style/consistency. For each finding give: what's wrong, why it bites, the concrete fix, and the reference. Cross-check against references/antipatterns.md before finishing — it catalogs the common ones with fixes.

---

Engine quick-reference

Concern	PostgreSQL	MySQL (InnoDB)
Default isolation	READ COMMITTED	REPEATABLE READ
Auto-index on FK	No (add it yourself)	Yes
JSON	jsonb (indexable, GIN)	JSON (functional indexes)
Full-text	built-in tsvector	FULLTEXT indexes
Auto PK type	bigint GENERATED ... AS IDENTITY	BIGINT AUTO_INCREMENT
Online schema change	NOT VALID/CONCURRENTLY, mostly online	online DDL or gh-ost/pt-osc
Case sensitivity	identifiers fold to lowercase	depends on collation/OS

→ Full per-engine gotcha lists: references/postgres.md and references/mysql.md

---

Common failure → fix

• Slow query → run EXPLAIN ANALYZE; look for seq scans on large tables and bad estimates; add/adjust an index or rewrite the predicate. See references/indexing-and-performance.md.
• Deadlocks → shorten transactions, acquire locks in a consistent order, add retry logic. See references/transactions-and-concurrency.md.
• "Works in app, corrupt in DB" → missing constraint/FK; add it at the database layer. See references/schema-design.md.
• Migration locked the table → wrong DDL strategy for the size/engine; use the online recipes. See references/migrations-and-operations.md.
• Wrong results with NOT IN / aggregates → NULL three-valued logic; switch to NOT EXISTS, add NOT NULL. See references/antipatterns.md.

---

References (progressive disclosure)

Open the file that matches your task; each is a deeper, self-contained guide.

• references/schema-design.md — modeling, normalization, keys, data types, constraints, generated columns.
• references/indexing-and-performance.md — index types, composite/covering/partial indexes, EXPLAIN, query rewriting.
• references/transactions-and-concurrency.md — isolation levels, locking, MVCC, deadlocks, retry patterns.
• references/migrations-and-operations.md — online schema change, backfills, backups, partitioning, scaling.
• references/antipatterns.md — schema, query, and application antipatterns to avoid (and the fix for each).
• references/postgres.md — PostgreSQL-specific best practices and "Don't Do This" gotchas.
• references/mysql.md — MySQL/InnoDB-specific best practices, locking model, and gotchas.
• references/canonical-sources.md — the authoritative books, docs, and sites to consult, with a recommended reading order.