---
name: data-modelling
description: Relational data modelling for PostgreSQL - entities and relationships, normalisation to 3NF and deliberate denormalisation, surrogate uuid vs natural keys, many-to-many join tables, soft delete tradeoffs, temporal and history modelling, JSONB boundaries, multi-tenancy models, audit columns, modelling for reads vs writes, and dimensional modelling basics with facts and dimensions. Load when designing or changing a schema, choosing keys, modelling history or tenants, deciding between JSONB and columns, or shaping analytics tables.
---

# Data modelling

Builds on `sql-conventions`. Constraints are covered in `data-integrity`; changing live schemas in `database-migrations`; index design in `query-optimisation`.

## Entities and relationships
- Model nouns from the domain as tables, one per concept, with one fact stored in one place. Name relationships by cardinality: one-to-one, one-to-many, many-to-many.
- Put the foreign key on the "many" side, `NOT NULL` unless the relationship is optional. Index every foreign key you join or filter on.
- Choose `ON DELETE` deliberately: `RESTRICT` by default, `CASCADE` only for true ownership (order lines), `SET NULL` for optional links.
- Write down invariants (uniqueness, allowed states, ownership) before choosing columns.

## Normalisation
- Target 3NF: every non-key column depends on the key, the whole key, and nothing but the key. Remove repeating groups and derived values stored beside their source.
- Denormalise only for a measured read problem, and record why. Keep a single source of truth and a refresh path (trigger, job, or materialised view).
- Store derived counters or totals only with a plan to keep them correct under concurrency.

## Keys
- Surrogate primary keys by default: `uuid` (generated by the database or app; time-ordered variants reduce index fragmentation, verify support in the installed version) or `bigint` identity. Do not expose sequential ids where enumeration matters.
- Keep natural keys (email, SKU, slug) as `UNIQUE` constraints, not primary keys; they change.
- Composite primary keys suit join tables. Keep key types consistent across referencing tables.

## Many-to-many
- Use a join table with a composite primary key on both foreign keys, plus an index for the reverse direction.
- Put relationship attributes (role, joined_at, quantity) on the join table; promote it to a full entity with its own id once it has behaviour.
```sql
CREATE TABLE project_members (
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  user_id    uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role       text NOT NULL,
  PRIMARY KEY (project_id, user_id)
);
CREATE INDEX ix_project_members_user_id ON project_members (user_id);
```

## Soft delete
- Prefer hard delete plus an archive or audit table when history is needed. Soft delete (`deleted_at timestamptz`) leaks into every query, unique constraint, and index.
- If used, make unique constraints partial (`WHERE deleted_at IS NULL`), hide rows through a view or one query layer, and define retention and purge. Prisma has no built-in soft delete; centralise the filter.

## Temporal and history
- Choose per need: audit log (who changed what), versioned rows (`valid_from`, `valid_to`, one current row), or append-only event tables.
- Use `tstzrange` with an exclusion constraint to prevent overlapping validity periods. Store point-in-time facts (price at purchase) on the transaction row rather than joining to current data.

## JSONB boundaries
- Use JSONB for sparse, schema-variable, or opaque payloads (webhooks, settings). Use real columns for anything filtered, joined, constrained, or aggregated routinely.
- Index with GIN or expression indexes only for known query shapes; validate shape at the edge or with `CHECK`. If a key gets queried often, promote it to a column.

## Multi-tenancy
- Shared tables with a `tenant_id` on every row: simplest, needs `tenant_id` in keys, composite foreign keys, indexes, and every query. Row-level security can enforce it in the database; test policies.
- Schema per tenant: stronger isolation, migration cost grows with tenant count. Database per tenant: strongest isolation, highest operational cost.
- Default to shared tables unless isolation or compliance demands more.

## Audit columns
- Add `created_at timestamptz NOT NULL DEFAULT now()` and `updated_at` to mutable tables; add `created_by` and `updated_by` where accountability matters. Maintain `updated_at` in one place (trigger or ORM hook), not by hand.

## Reads vs writes
- Model for writes in the normalised core; serve heavy reads with views, materialised views, summary tables, or read replicas. Accept staleness explicitly.

## Analytics basics
- Facts are measurable events at a declared grain (one row per order line). Dimensions are descriptive context (date, customer, product).
- Use a star layout: a narrow fact table of keys and measures, joined to denormalised dimensions. Never mix grains in one fact table. Keep analytics separate from the transactional schema where load demands (`data-import-export`).