Data Modeling

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: "alice@x.com")
  - 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 |

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: pvnarp
• Source: pvnarp/agent-skills
• License: MIT

Install and usage instructions live in the source repository linked above.