design-relational-schema

name	design-relational-schema
description	Designs a normalized relational schema from requirements — entities, relationships, PK strategy (surrogate bigint vs natural vs UUIDv7/ULID), 1:1/1:N/M:N and inheritance modeling, 3NF/BCNF normalization, invariants encoded as UNIQUE/CHECK/FK/exclusion constraints, and deliberate read-path denormalization with stated consistency tradeoffs.
when_to_use	When starting a new database or a new table cluster and you need the logical+physical model — turning requirements/an ERD into tables, choosing keys, modeling cardinalities and inheritance, normalizing, then deciding where to denormalize. Distinct from db-migration-safety (altering a live table safely) and optimize-sql-query (speeding up a query against an existing schema).

When to Use

Reach for this skill when you're designing the shape of the data, before any table exists:

• "Model these requirements / this ERD as tables"
• "Should this PK be a UUID or a bigint? natural or surrogate? composite?"
• "How do I model users↔roles (M:N) / orders→items (1:N) / a polymorphic comment?"
• "Normalize this — I've got repeating columns / update anomalies / duplicated data"
• "Where should I denormalize for a read-heavy dashboard, and what breaks?"
• Choosing column types: enum vs lookup table, soft vs hard delete, audit columns, money/time precision

NOT this skill:

• Changing a table that already has rows/traffic (locks, backfills, rollback) → db-migration-safety
• A query against an existing schema is slow → optimize-sql-query
• You need an append-only, replayable, audit-complete domain model → design-event-sourcing-cqrs
• Computing prices/tax/rounding/FX (the math, not the column type) → money-decimal-arithmetic
• Storing/converting/comparing timestamps & DST correctly → datetime-timezone-correctness
• Shaping items/documents for a non-relational store (DynamoDB/Mongo/Cassandra) around access patterns → model-nosql-data

Steps

1. Extract entities, attributes, relationships from requirements — nouns→tables, verbs→relationships. List each entity, its attributes, and for every pair the cardinality (1:1 / 1:N / M:N) and optionality (mandatory vs nullable side). Mark each attribute's identity role: is it a candidate key (naturally unique, immutable), or descriptive? Write functional dependencies (A → B: A determines B) — they drive normalization in step 3. One table = one entity type; if an attribute is itself a list ("tags", "phone numbers"), it's a separate table, not a CSV column or jsonb dumping ground.

2. Pick a PK strategy per table — default to surrogate, choose the integer/UUID flavor deliberately.

   Strategy	Use when	Avoid when
   bigint GENERATED ALWAYS AS IDENTITY	Single-DB, internal IDs, smallest/fastest index, FK-heavy — default	IDs leak to clients/URLs and count/sequence is sensitive; multi-master inserts
   uuid v7 / ULID (time-ordered)	IDs generated client-side or across shards, exposed in URLs, need merge without collision	You can use bigint and don't expose the ID — 16B vs 8B and bigger indexes
   uuid v4 (random)	Only if unguessability matters and you accept index-locality cost	Hot insert paths — random UUIDs fragment B-tree pages and bloat WAL
   Natural key (email, ISO code, slug)	Truly immutable, single-attribute, externally governed (country.iso2, currency.code)	It can ever change or isn't guaranteed unique — a changing PK cascades through every FK
   Composite key	Junction tables ((a_id, b_id)); rows identified only by the combination	A tempting single surrogate would be simpler and the combo isn't queried as a unit

   Rules: use a surrogate bigint IDENTITY by default; reach for UUIDv7/ULID (not v4) the moment IDs cross a process boundary or are client-generated; never expose a sequential surrogate where the sequence is sensitive (use UUIDv7 instead); a natural key still deserves a UNIQUE constraint even when you also keep a surrogate PK. Never use / (legacy, ownership/permission footguns) — use .

Common Errors

• EAV ("flexible schema") tables. entity/attribute/value rows throw away typing, FKs, and constraints and turn every read into a self-join pivot. Use real typed columns; if attributes are genuinely open-ended, a single typed jsonb column beats EAV.
• Float money. price float loses cents to binary rounding — 0.1 + 0.2 ≠ 0.3. Use NUMERIC or integer minor units; defer the math rules to money-decimal-arithmetic.
• Nullable-FK soup / polymorphic (type, id). A parent_type text, parent_id bigint pair can't have a foreign key, so the DB can't stop dangling references. Use separate real FK columns + a CHECK that exactly one is non-null.
• Natural key as PK that later changes. Making email or a username the PK means a single edit cascades through every referencing FK. Keep a surrogate PK; put UNIQUE on the natural key.
• Random UUID (v4) PK on a hot insert path. Random keys scatter B-tree inserts, bloating the index and WAL. Use UUIDv7/ULID (time-ordered) when you need a UUID, or a bigint when the ID isn't exposed.
• Soft delete without filtered constraints. deleted_at plus a plain UNIQUE(email) blocks a user from re-registering a freed email, and plain FKs still "see" deleted parents. Make uniqueness and lookups partial: WHERE deleted_at IS NULL.
• Over-normalizing tiny fixed sets. A 3-value lookup table joined on every query adds a join for no benefit. A CHECK (x IN (...)) enum is fine for small, code-coupled, rarely-changing sets.
• Storing lists in a column. tags TEXT as CSV (or an unindexed array) can't be FK'd, constrained, or joined cleanly. Model it as a child/junction table.
• varchar(255) cargo-culting and naive timestamp. Arbitrary length caps cause silent truncation; timestamp without time zone loses the offset. Use text and timestamptz.
• Missing ON DELETE action. Defaulting blindly leaves you with either accidental orphans or surprise cascade deletes. Choose CASCADE/RESTRICT/ per FK on purpose.

Verify

1. 3NF check: For each table, every non-key column depends on the key, the whole key, and nothing but the key. Name any transitive (non-key → non-key) or partial dependency you allowed and justify it as a deliberate denormalization — otherwise split it.
2. Anomaly probe: Pick one update, one insert, and one delete per core entity. Confirm each touches exactly one row in one place with no way to leave the data inconsistent (no second copy to forget).
3. Constraint coverage: Every invariant you stated in step 1 maps to an actual NOT NULL/UNIQUE/CHECK/FK/exclusion/partial-index in the DDL — not to an app-layer comment. List any invariant not enforced by the DB and why.
4. Referential integrity: Every FK names an explicit ON DELETE action; no polymorphic (type, id) pair lacks a real FK; every junction table has a composite PK of its two FKs.
5. Key sanity: Every table has a PK; no natural key that can change is used as a PK; sequential surrogates aren't exposed where the sequence is sensitive; UUID columns are v7/ULID unless v4 is justified.
6. Type sanity: No money in float; timestamps are timestamptz (UTC); no CSV/array masquerading as a relationship; enums vs lookup chosen per the step-6 rule.
7. Access-pattern map: Every listed top query is served by an existing index/PK; every index supports at least one stated query (no orphan indexes); each denormalized column has a named owner-of-consistency and a stated staleness bound.

Done = the schema is at 3NF (BCNF where a determinant anomaly existed) with every stated invariant enforced by a DB constraint, every PK/FK and ON DELETE chosen deliberately, no float money / naive timestamps / EAV / polymorphic-FK soup, and an access-pattern→table/index map in which every hot read has a supporting index and every denormalization carries a written consistency tradeoff.