# Relational Data Modeling

> Corrects the wrong defaults a model has when designing a relational schema — the DDL decisions an experienced engineer makes differently. Use when creating or reviewing tables, migrations, ER models, or ORM schema definitions. Covers identity and keys (surrogate vs natural, identity vs serial, uuidv7, composite keys that make cross-tenant references impossible), relationships (polymorphic foreign keys the database cannot enforce, referential actions, unindexed FK columns, disjoint subtypes), invariants the engine can prove instead of application code (EXCLUDE, partial unique indexes, CHECK limits, deferrable cycles, NOT VALID), types (timestamptz, exact money, range types, enum vs lookup table), derived and encoded data (generated columns, JSONB as an escape hatch), and time (events vs in-place updates, soft-delete flags that silently disable constraints, temporal keys). NOT for query tuning, index selection for read paths, or connection pooling.

- Skill: `pproenca/relational-data-modeling` (Agent Skill, multi-file: 34 files)
- Install (CLI): `npx skillmds add pproenca/relational-data-modeling`
- Raw SKILL.md: https://api.skillmd.com/api/skills/pproenca/relational-data-modeling/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- Author: pproenca (https://skillmd.com/u/pproenca)
- Updated: 2026-09-09
- Page: https://skillmd.com/skills/pproenca/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

| # | Category | Prefix | Covers |
|---|----------|--------|--------|
| 1 | Identity and Keys | `key-` | What a row is; the choice every foreign key depends on |
| 2 | Relationships and Cardinality | `rel-` | Keeping references declarable to the database, not just intended |
| 3 | Constraints as the Model | `cons-` | Which mechanism can actually hold which invariant |
| 4 | Types and Domains | `type-` | The cheapest constraint available, chosen for meaning not habit |
| 5 | Derived and Encoded Data | `norm-` | What every deliberate copy costs, and what the database can't see inside |
| 6 | Time, History and Lifecycle | `time-` | What happens to a row when the world changes |

## Quick Reference

### 1. Identity and Keys

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

### 2. Relationships and Cardinality

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

### 3. Constraints as the Model

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

### 4. Types and Domains

- [`type-timestamptz-for-instants`](references/type-timestamptz-for-instants.md) — `timestamp` stores a wall clock with no zone; a birthday is not an instant
- [`type-text-over-varchar`](references/type-text-over-varchar.md) — `varchar(255)` is a MySQL artifact; a length limit is a `CHECK` or a domain
- [`type-numeric-with-currency`](references/type-numeric-with-currency.md) — floats drift, `money` is locale-dependent, and currency is never implicit
- [`type-range-not-two-columns`](references/type-range-not-two-columns.md) — two loose columns admit inverted rows and block every temporal constraint
- [`type-enum-lookup-or-check`](references/type-enum-lookup-or-check.md) — `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`](references/norm-derived-needs-a-mechanism.md) — no constraint can hold two copies equal; name the mechanism or don't copy
- [`norm-encoded-composite-values`](references/norm-encoded-composite-values.md) — generate the reference code from its parts; a parsed column can't be constrained
- [`norm-jsonb-for-open-shapes`](references/norm-jsonb-for-open-shapes.md) — if you can enumerate the keys and filter on one, they are columns

### 6. Time, History and Lifecycle

- [`time-events-not-in-place-updates`](references/time-events-not-in-place-updates.md) — an in-place balance destroys the audit trail and serialises the account
- [`time-soft-delete-breaks-constraints`](references/time-soft-delete-breaks-constraints.md) — `deleted_at` disables unique constraints and foreign keys silently
- [`time-validity-as-a-range`](references/time-validity-as-a-range.md) — `WITHOUT OVERLAPS` makes two simultaneous versions unrepresentable
- [`time-valid-vs-transaction-time`](references/time-valid-vs-transaction-time.md) — 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`](references/cons-check-is-single-row.md) for
   the map of invariant shapes to mechanisms.

- [Section definitions](references/_sections.md) — category structure and ordering
- [Rule template](assets/templates/_template.md) — for adding new rules
- [AGENTS.md](AGENTS.md) — auto-built table of contents across all rules

## Reference Files

| File | Description |
|------|-------------|
| [references/_sections.md](references/_sections.md) | Category definitions and ordering |
| [assets/templates/_template.md](assets/templates/_template.md) | Template for new rules |
| [metadata.json](metadata.json) | Version and source references |