---
name: designing-database-schemas
description: Designs relational database schemas — tables, primary keys, column types, constraints, naming, and normalization decisions. Use when the user asks to design or review a database schema, create tables for a new feature or app, choose primary keys or column types, model entities and relationships, or decide about normalization or soft deletes. Do not use for writing application queries, tuning existing query performance, writing migration files, or modeling NoSQL documents — separate skills cover those.
---

<!--
Author: Simon-Pierre Boucher
Contact: contact@spboucher.ai
-->

# Designing Database Schemas

## When to use / when NOT to use
- **Use for:** designing or reviewing tables, keys, types, constraints, relationships, and normalization for a relational database.
- **Do NOT use for:** query authoring (`writing-sql-queries`), performance tuning (`optimizing-sql-performance`), migration mechanics (`managing-database-migrations`), document/key-value modeling (`modeling-nosql-data`).

Default dialect: PostgreSQL.

## Core rules

1. **Model the domain honestly.** One table per entity, one row per instance of that entity; name the grain in a comment if it is not obvious.

2. **Start at 3NF; denormalize only after a measured bottleneck.** Duplicate a column or add a materialized view only when `EXPLAIN ANALYZE` on a real query proves the join is the problem — never on a hunch.

3. **Default primary key: surrogate `bigint GENERATED ALWAYS AS IDENTITY`.** Use `uuid` (v7 if available) when IDs are generated client-side, exposed publicly, or merged across databases. Natural keys only for genuinely immutable identifiers (ISO country code) — emails and usernames change.
   - ✅ `id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY`
   - ❌ `email text PRIMARY KEY`

4. **Types carry meaning:**
   - ✅ `timestamptz` — ❌ `timestamp` (naive, timezone bugs)
   - ✅ `numeric(12,2)` or integer cents for money — ❌ `float`/`real` (rounding errors)
   - ✅ `text` + `CHECK`/enum — ❌ `varchar(255)` cargo-cult limits
   - ✅ `boolean` — ❌ `char(1)` Y/N flags

5. **Constraints are documentation that cannot go stale.** Every column `NOT NULL` unless NULL has a defined meaning; `CHECK` for invariants (`price_cents >= 0`); `UNIQUE` for business uniqueness; every relationship a real `FOREIGN KEY` with an explicit `ON DELETE` decision (no default cascades by accident).

6. **Index every foreign key at creation time.** PostgreSQL does not do this automatically; unindexed FKs cause full-table scans on joins and cascaded deletes.

7. **One naming convention, applied everywhere:** `snake_case`, singular table names (`user_order`), PK `id`, FK `<table>_id`, timestamps `created_at`/`updated_at`. If the existing schema uses plural, match it — consistency beats preference.

8. **Soft deletes are a tradeoff, not a default.** `deleted_at timestamptz NULL` keeps history but every query and every `UNIQUE` constraint must account for it (use partial unique indexes). Choose hard delete + audit table when history, not resurrection, is the need.

## Workflow

1. List entities, relationships (1-1, 1-N, N-N), and the grain of each table.
2. Draft `CREATE TABLE` statements applying rules 3–7; junction tables for N-N.
3. Add constraints for every stated business rule.
4. Validate: run the DDL in a scratch database, then insert one valid row and one row violating each constraint — every violation must fail. Fix and re-run until it does.
5. Deliver DDL plus a one-line rationale per non-obvious decision.

## Edge cases & failure modes
- **Existing schema present** → inspect it first and match its conventions; flag (don't silently fix) inconsistencies.
- **Polymorphic references** ("commentable_id + type") → prefer one FK column per target table or a supertype table; plain polymorphic columns can't have FK constraints.
- **Multi-tenancy** → decide row-level (`tenant_id` on every table, composite indexes leading with it) vs schema-per-tenant before writing DDL.
- **Very wide tables** (>30 columns) → usually two entities in disguise; split by update frequency or ownership.

## References
DDL templates, junction/soft-delete/audit patterns, dialect notes: see [references/patterns.md](references/patterns.md).