Relational Data Modeling

Relational Data Modeling

The decisions a relational schema forces, and how to settle them so the database enforces what it can and the application is left with only what it must. Every rule names the wrong default it corrects; there is no rule for what a capable model already gets right.

Examples are PostgreSQL 18, and every DDL statement in this skill was executed against 18.4 — including the failure cases, to confirm the constraints reject what they claim to reject. Roughly a third of the rules depend on mechanisms MySQL and SQLite do not have (EXCLUDE, partial unique indexes, range types, WITHOUT OVERLAPS, deferrable constraints, NOT VALID); those rules say so. The judgment rules transfer to any relational engine.

When to Apply

Use this skill when:

• Writing or reviewing CREATE TABLE / ALTER TABLE, a migration, or an ORM schema definition (Prisma, Drizzle, Django models, ActiveRecord, Ecto, SQLAlchemy)
• Designing an entity-relationship model, or naming what a row is — the point where key choices become expensive to reverse
• The user says "should this be one table or two", "how do I model many-to-many", "the schema allows bad data", "we need history", "we're adding multi-tenancy", or "can the database enforce this"
• A bug turns out to be a schema that permitted the bad state — duplicates that a unique constraint should have caught, orphans a foreign key should have blocked, overlapping bookings, two rows flagged as default
• Adding constraints to a table that already has rows and traffic
• Reviewing a schema generated by an ORM or a scaffolding tool, which is where polymorphic associations, reflexive surrogate keys, and blanket soft-delete flags arrive from

This skill is NOT for:

• Query tuning, execution plans, or choosing indexes for a read path — this covers only the indexes that constraints and foreign keys require
• Connection pooling, replication, or operational tuning
• Non-relational stores, where the trade-offs it argues from do not hold

Rule Categories

Quick Reference

1. Identity and Keys

• key-primary-key-is-real-identity — a surrogate hides the natural key; you still owe the table a UNIQUE
• key-identity-not-serial — serial is legacy; GENERATED ALWAYS AS IDENTITY blocks the desync that breaks inserts
• key-uuidv7-for-client-generated — v4 randomises every B-tree insert; use uuidv7(), and only when a UUID is earned
• key-propagate-scoping-key — carry the tenant into child keys so a cross-tenant reference cannot be written
• key-partition-axis-binds-the-key — for tables that grow with traffic, the partition axis is a key decision, not later tuning

2. Relationships and Cardinality

• rel-no-polymorphic-fk — type + id makes referential integrity undeclarable; use an exclusive arc or a supertype
• rel-relationship-as-entity — name it if it has facts and a lifecycle; keep it a keyed link table if it doesn't
• rel-choose-referential-action — CASCADE means composition; on a ledger it is data loss
• rel-index-referencing-side — PostgreSQL does not index the child column; MySQL does, and the habit transfers
• rel-one-to-one-is-one-table — a split buys a join and enforces nothing unless storage or access control differs
• rel-subtypes-through-the-key — UNIQUE (id, kind) plus a pinned discriminator proves the variants are disjoint

3. Constraints as the Model

• cons-not-null-by-default — a CHECK that evaluates to NULL passes, and UNIQUE stops constraining
• cons-exclude-for-non-overlap — the app-level overlap check is a read-then-write race; EXCLUDE is not
• cons-partial-unique-index — "at most one active X" is a UNIQUE ... WHERE, not a trigger
• cons-check-is-single-row — wrapping a query in a function makes a CHECK that silently stops holding
• cons-deferrable-for-cycles — defer to COMMIT instead of making a column nullable forever
• cons-not-valid-then-validate — what makes constraints addable to a live table at all

4. Types and Domains

• type-timestamptz-for-instants — timestamp stores a wall clock with no zone; a birthday is not an instant
• type-text-over-varchar — varchar(255) is a MySQL artifact; a length limit is a CHECK or a domain
• type-numeric-with-currency — floats drift, money is locale-dependent, and currency is never implicit
• type-range-not-two-columns — two loose columns admit inverted rows and block every temporal constraint
• type-enum-lookup-or-check — CHECK by default, lookup table when values carry attributes, enum is a one-way door

5. Derived and Encoded Data

• norm-derived-needs-a-mechanism — no constraint can hold two copies equal; name the mechanism or don't copy
• norm-encoded-composite-values — generate the reference code from its parts; a parsed column can't be constrained
• norm-jsonb-for-open-shapes — if you can enumerate the keys and filter on one, they are columns

6. Time, History and Lifecycle

• time-events-not-in-place-updates — an in-place balance destroys the audit trail and serialises the account
• time-soft-delete-breaks-constraints — deleted_at disables unique constraints and foreign keys silently
• time-validity-as-a-range — WITHOUT OVERLAPS makes two simultaneous versions unrepresentable
• time-valid-vs-transaction-time — two axes only when you must reproduce a past belief; usually you must not

How to Use

Read a reference file when its decision comes up — the quick reference above is enough to route. Each rule states the wrong default it corrects and why, then gives a canonical example.

Two rules of thumb tie the categories together and are worth applying even outside a specific rule:

1. Before adding a column, name what makes two rows the same row. That sentence is a constraint you owe the table.
2. Before writing a validation in application code, ask which constraint could hold it instead. If the answer is "none", that is worth knowing explicitly — see cons-check-is-single-row for the map of invariant shapes to mechanisms.

• Section definitions — category structure and ordering
• Rule template — for adding new rules
• AGENTS.md — auto-built table of contents across all rules

Reference Files