Data Modeling

Model the domain, not the UI, not the database, not the API. The domain model is upstream of everything - get it wrong and every layer inherits the mistake.

Reference: reference/modeling-patterns.md - Normalization levels, relationship patterns, soft delete, audit trails, ID types, polymorphic associations, temporal data.

Step 1: Identify Entities

An entity is a thing with identity that changes over time.

Ask These Questions

• What are the "nouns" in the domain? (User, Order, Product, Payment)
• Which things have a lifecycle? (Created → Active → Closed)
• Which things need to be referenced by other things? (Foreign keys)
• Which things are always accessed together? (Same aggregate)

Entity vs Value Object

	Entity	Value Object
Identity	Has a unique ID	No ID, defined by its values
Lifecycle	Changes over time	Immutable
Example	User, Order, Invoice	Address, Money, DateRange
Storage	Own table/document	Embedded in parent entity

Entity: User (id: 123, name: "Alice", email: "[email protected]")
  - Identity matters: User 123 is User 123 even if name changes

Value Object: Money (amount: 5000, currency: "USD")
  - Identity doesn't matter: any Money(5000, "USD") is interchangeable

Step 2: Map Relationships

Relationship	Pattern	Example
One-to-One	FK on either side, or embed	User ↔ Profile
One-to-Many	FK on the "many" side	User → Orders
Many-to-Many	Junction table	Student ↔ Course (via Enrollment)
Self-referential	FK to same table	Employee → Manager (Employee)
Polymorphic	Type column + ID, or separate tables	Comment on Post or Photo

Relationship Questions

• Ownership: If A is deleted, should B be deleted too? (cascade)
• Cardinality: One-to-one, one-to-many, many-to-many?
• Optionality: Is the relationship required or optional?
• Direction: Who needs to find whom? (This determines where indexes go)

Step 3: Define Aggregates

An aggregate is a cluster of entities that are always consistent together. The aggregate root is the entry point.

Aggregate: Order
  Root: Order
  Children: OrderItem, OrderDiscount
  Rule: Total = sum of (item prices - discounts)
  Invariant: Total must never be negative

→ You load/save the entire Order aggregate atomically
→ You NEVER modify OrderItems without going through Order
→ Other aggregates reference Order by ID only

Aggregate Design Rules

1. Small aggregates. An aggregate that contains 1000 items is too big. Split.
2. Consistency boundary. Everything inside must be consistent. Across aggregates = eventual consistency.
3. Reference by ID. Don't embed other aggregates. Reference them by ID.
4. One transaction per aggregate. Modifying two aggregates in one transaction = design smell.

Step 4: Choose IDs

ID Type	Pros	Cons	Best For
Auto-increment (INT)	Simple, small, sortable	Not distributed-safe, predictable	Single-database systems
UUID v4	Globally unique, no coordination	Large (16 bytes), random (bad for B-tree)	Distributed systems
UUID v7	Globally unique + time-sortable	Relatively new	Modern distributed systems
ULID	Time-sortable, URL-safe	Less standard	APIs where URL appearance matters
Prefixed ID (usr_abc123)	Human-readable, type-safe	Custom generation	User-facing APIs
Composite key	Natural meaning	Complex JOINs	Junction tables, time-series

Recommendation: UUID v7 or prefixed IDs for new systems. Auto-increment if single database and no API exposure.

Step 5: Validate Against Access Patterns

Before finalizing the model, list every way the data will be read:

ACCESS PATTERNS:
1. Get user by ID → users table, PK lookup
2. List orders for a user → orders WHERE user_id = X
3. Search products by name → products WHERE name LIKE '%X%' (full-text search?)
4. Get order with all items → orders JOIN order_items
5. Dashboard: orders per day → orders GROUP BY date (materialized view?)
6. Get user's recent activity → needs cross-entity query (denormalize?)

For each pattern:

• Can the model serve this efficiently?
• Does it require a JOIN? How many?
• Does it need an index? What kind?
• Should anything be denormalized for read performance?

If an access pattern is awkward, the model is wrong. Adjust the model, don't work around it.

Step 6: Handle State Machines

Most entities have a lifecycle. Model it explicitly.

Order States:
  DRAFT → PLACED → PAID → SHIPPED → DELIVERED
                ↘ CANCELLED

Allowed Transitions:
  DRAFT → PLACED (when submitted)
  PLACED → PAID (when payment confirmed)
  PLACED → CANCELLED (when cancelled before payment)
  PAID → SHIPPED (when shipped)
  SHIPPED → DELIVERED (when delivery confirmed)

Invalid Transitions (reject):
  DELIVERED → DRAFT (can't go back)
  CANCELLED → PAID (can't pay cancelled order)

Rules:

• Enumerate all states explicitly
• Define allowed transitions
• Validate transitions in code (reject invalid state changes)
• Store timestamps for each transition (placed_at, paid_at, shipped_at)

Model Documentation Template

## Entity: [Name]

### Fields
| Field | Type | Required | Notes |
|-------|------|----------|-------|
| id | UUID v7 | Yes | Primary key |
| ... | ... | ... | ... |

### Relationships
- Has many [Entity] (via [field])
- Belongs to [Entity] (via [field])

### States
[state diagram if applicable]

### Access Patterns
1. [Pattern] - [how it's served]

### Invariants
- [Business rule that must always be true]

Common Mistakes

Mistake	Problem	Fix
Model mirrors the UI	UI changes → model changes	Model the domain, not the screens
God entity	One entity with 40 fields, handles everything	Split into aggregates
Anemic model	Entities are just data bags, logic lives elsewhere	Put behavior with the data it operates on
Premature denormalization	Duplicated data, inconsistency risk	Normalize first, denormalize for measured performance needs
Missing state machine	State transitions unconstrained	Explicit states + allowed transitions
Stringly typed	Status as free-text string	Use enums with exhaustive handling