Skip to content

Data Modeling Guidelines

Logical Model

  • Align the model with domain language (DDD): aggregates, entities, value objects.
  • An aggregate is a consistency boundary — enforce invariants within it, never across aggregates.
  • Value objects are immutable and identified by their value, not an ID.
  • Entities have identity that persists across state changes.
  • Model behavior, not just data: place domain logic inside aggregates, not in services.
  • Aggregates are pure domain objects — no I/O, no persistence, no external calls. Repositories (infra layer) handle load/save.
  • Avoid anemic models (plain data bags with no behavior).
  • Name types, fields, and relations using the ubiquitous language of the domain — no technical abbreviations.

Physical Model

Relational (SQL)

  • Every table has a surrogate primary key (id); use UUIDs for distributed systems, auto-increment otherwise.
  • Name tables as singular nouns (order, customer), columns as snake_case.
  • Add a created_at / updated_at timestamp to every mutable table.
  • Normalize to 3NF by default; denormalize only when read performance requires it and you can justify it.
  • Declare foreign keys and let the DB enforce referential integrity.
  • Index every foreign key and every column used in WHERE, ORDER BY, or JOIN predicates.
  • Avoid NULL where possible — NULL means "unknown", not "empty" or "zero".
  • Never store multiple values in a single column (no comma-separated lists).
  • Migrations are forward-only; never modify existing migration files.

Document Stores

  • Embed data that is always read together and only written by one aggregate.
  • Reference (by ID) data that is shared across aggregates or grows unbounded.
  • Design documents around read patterns, not write patterns.
  • Keep documents small — avoid unbounded arrays inside a document.
  • Include a schema_version field for forward-compatible migrations.

Key/Value Stores

  • Design keys to be self-describing and hierarchical: <namespace>:<entity>:<id> (e.g., session:user:42).
  • Set a TTL for all ephemeral data (sessions, caches, rate-limit counters).
  • Never store relational data in a key/value store; use it for caching, sessions, and simple counters.
  • Document the key schema in the codebase alongside the access code.

Keys: Business Keys and Technical Keys

Definitions

Term Description
Natural key (also: business key) A key whose value is meaningful in the business domain (e.g. IBAN, ISBN, email address, product code). It is derived from domain attributes and may be visible to users and external systems.
Surrogate key An artificially generated identifier with no business meaning. It exists solely for persistence and referential integrity inside the technical system.

Stability

  • Natural keys can change: email addresses are updated, product codes are reformatted when a catalogue is merged, VAT numbers differ across countries. Treat any natural key as mutable unless the domain contract explicitly guarantees immutability (e.g. ISBN).
  • Surrogate keys are stable by construction — once assigned they never change, regardless of business rule changes.
  • Never use a mutable natural key as a foreign-key target. If you must enforce uniqueness on a natural key, add a UNIQUE constraint on the natural-key column while keeping the surrogate PK as the FK target.

Which key goes where?

Context Use
Database FK references Surrogate key — stable, compact, fast joins
Public REST / GraphQL API UUID (surrogate) or a provably immutable natural key (e.g. ISBN). Never expose auto-increment integers.
Event / message identifiers UUID — must be globally unique and safe to generate without DB round-trip
Human-readable references (order numbers, ticket IDs shown to users) Natural key or a formatted surrogate (e.g. ORD-2024-00042) — NOT the raw DB PK
Cross-system integrations Natural key — it is the shared vocabulary between systems

Auto-increment integers must never appear in public-facing URLs or API responses: they reveal the table's row count and enable trivial enumeration attacks (IDOR — Insecure Direct Object Reference).

Key types for surrogate keys

Auto-increment (SERIAL / IDENTITY)

  • Sequential integers generated by the database.
  • Compact (4 or 8 bytes), excellent B-tree index locality (always appended at the rightmost leaf page).
  • Generated in a single centralized source (the DB sequence) — impossible to pre-generate in application code or across shards.
  • Reveals scale when exposed externally.
  • Use when: single-database architecture, IDs stay internal and are never exposed in APIs or events.

UUID v4 (random)

  • 128-bit random value, globally unique, safe to generate anywhere without DB coordination.
  • No ordering information → random writes scatter across B-tree pages, causing page splits, index fragmentation, and increased buffer-pool pressure on large tables.
  • Use when: distributed generation is required but index performance is not critical (small tables, document stores, event IDs).

UUID v7 (time-ordered)

  • 128-bit value: 48-bit Unix timestamp (ms) + 80 bits of random data.
  • Monotonically increasing within the same millisecond → near-sequential B-tree inserts, matching the performance profile of auto-increment while remaining globally unique.
  • Sortable by creation time without a separate created_at column (though created_at should still be present for readability and query clarity).
  • The time prefix is not a secret, but the random suffix makes the full ID unguessable.
  • Available in PostgreSQL 17+ (gen_random_uuid() returns v4; use uuid_generate_v7() from pgcrypto or application-side generation for v7).
  • Use when: distributed systems or any system where IDs are exposed in APIs. Default choice for new services.

Decision guide

Scenario Recommended key type
Single database; IDs never leave the DB Auto-increment
IDs exposed in APIs or events UUID v7 (preferred) or UUID v4
Distributed / multi-shard writes UUID v7
Cross-database data merge required UUID v7 (generated application-side)

Data migration and key handling

During migrations between systems or database schemas, natural keys are the shared vocabulary — surrogate keys are meaningless outside the system that created them.

  1. Use natural keys as the join criterion when loading data from the source system.
  2. Assign fresh surrogate keys in the target system; never copy source surrogate keys.
  3. If the source system's surrogate key is needed for audit or debugging, store it in a dedicated external_id (or legacy_id) column — do not reuse it as the PK.
  4. If no stable natural key exists, introduce one before the migration (e.g. a domain-meaningful code column) rather than hoping the surrogate key transfer will work.
  5. In event-driven architectures, publish the natural key in domain events so downstream consumers can correlate across systems without depending on internal surrogate keys.

Directives

  • Align model with DDD: aggregates, entities, value objects; aggregates are pure domain objects (no I/O, no persistence)
  • Place domain logic inside aggregates; avoid anemic models; use ubiquitous language for all names
  • SQL: surrogate PK (UUID for distributed); created_at/updated_at on every mutable table; declare foreign keys; index FK and WHERE/ORDER BY columns; forward-only migrations
  • Never store multiple values in one column; avoid NULL where possible
  • Document stores: embed data read together; include schema_version; design documents around read patterns
  • Key/value: keys are <namespace>:<entity>:<id>; always set TTL for ephemeral data
  • Keys: surrogate PKs are stable FK targets; use UUID v7 for any ID exposed in APIs or events, auto-increment only for internal single-DB IDs; never expose auto-increment integers in public APIs (IDOR risk); use natural keys as the join criterion during data migration