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.
Nest an enum inside the value object or entity it belongs to (e.g. Parse_Error::Reason) when it is used exclusively by that type; only hoist it to namespace scope once a second type needs it too.

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.

Wire Model¶

A Wire Model — commonly called a Data Transfer Object (DTO) — represents data as it crosses a process boundary: API request/response, message/event payload, or a serialized file.
Optimized for serialization/deserialization, not for business logic: a Wire Model is a plain data carrier with no domain behavior — that belongs in the aggregate (see Logical Model above).
Translate DTOs to and from domain objects at the process boundary; never let domain code depend on a Wire Model's shape, and never pass an unconverted DTO past the infra→usecase boundary.
The common case is plain request/response: the whole payload is read or written as a single unit, with no ordering constraints beyond the schema itself.
Version wire formats explicitly — a type/schema discriminator or a schema_version field — so a reader can identify the shape it is looking at without external knowledge.

Streaming-Capable Wire Models¶

Add these constraints only when a Wire Model is read or written incrementally rather than as one whole payload — an event stream, an NDJSON export, or a config file rewritten atomically:

A field derived from the rest of the payload (checksum, fingerprint, signature) must come last and must exclude itself from its own computation input; it can never summarize data that includes itself.
No forward references: a field may only refer to data already written or read earlier in the same stream, never to something that appears later.
Put type/version discriminators first, not last, so a reader can validate or dispatch before consuming the rest.
Give each record a clear boundary (delimiter or length prefix) so a reader can process it unit by unit instead of buffering the whole stream in memory.
Keep framing (length, delimiters) at the protocol level; never turn it into a business field inside the Wire Model's own schema.
Treat an emitted field as append-only — never patch a field already written; a value that depends on something not yet known belongs in a trailer, not the header.

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.

Use natural keys as the join criterion when loading data from the source system.
Assign fresh surrogate keys in the target system; never copy source surrogate keys.
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.
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.
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
Nest an enum inside its owning value object/entity (e.g. Parse_Error::Reason) when it is used exclusively by that type; only hoist it to namespace scope once a second type needs it
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
Wire Models (DTOs) carry data across a process boundary only; no domain behavior; translate to/from domain objects at the boundary; version wire formats with a type/schema discriminator or schema_version field
Streaming Wire Models: self-referential fields (checksum/fingerprint) go last and exclude themselves from their own input; type/version discriminator goes first; no forward references; each record needs a clear boundary; framing stays at the protocol level, never a business field
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)
Never use a mutable natural key as a foreign-key target, even if unique — keep the surrogate PK as the FK target
Migrations: use natural keys as the join criterion; assign fresh surrogate keys in the target system
Never copy source surrogate keys during migration; store a needed one in a separate external_id column