Skip to main content

Domain Model and Table Design — Mapping and Indexes

The order of design: the domain model comes first​

One of the most important principles in DDD is the idea of domain model first.

Why design the domain model first​

When you start from table design, your attention tends to go toward the data structure. However, the essence of a business is not "how to store data" but "the business rules and behavior."

# Approach 1: Starting from the table (❌ to avoid)
"What columns does the orders table need?"
→ order_id, user_id, total_price, status, created_at, ...
→ Normalization? Indexes?

Problems:
- Business rules are not taken into account
- The design is determined by database convenience
- It becomes hard to add business rules later

# Approach 2: Starting from the domain (✅ recommended)
"What is an 'order'? What rules does it have?"
→ It has states: draft, confirmed, shipped, cancelled
→ Items cannot be changed after confirmation (invariant)
→ The total amount is calculated from the order lines (derived value)
→ The shipping address is saved as a snapshot at the time of confirmation

Benefits:
- Business rules become clear
- The domain model is expressed in business language
- Table design is merely a means of persisting the domain model

An important principle: business rules determine table design, not the other way around.

The domain-model-first process​

As a result, you gain the following benefits.

  • The domain layer can focus on business rules
  • Changes to the table structure do not affect the business logic
  • The design becomes resilient to changes in business requirements

By keeping this order, you achieve "business-driven design" rather than "database-driven design."

Example: table design for a shipping address​

Even for the same "shipping address," the table design changes depending on the business rules.

Case A: Saved as a snapshot at the time of the order

Even if the user later changes their address, the order's address should not change.

orders table
├── id
├── status
├── shipping_prefecture  ─┐
├── shipping_city         │ Embedded
├── shipping_street      ─┘
└── created_at

Case B: Referencing an address master that the user manages

The user manages their addresses, and the order references one of them.

addresses table             orders table
├── id  <───────────────── shipping_address_id
├── user_id                 ├── id
├── prefecture              ├── status
├── city                    └── created_at
└── street

The domain model and tables are not 1:1​

The domain model and table design are independent, and the repository layer absorbs the gap.

Mapping patterns​

There are several typical patterns for the correspondence between the domain model and tables. Choose the appropriate pattern for the situation.

Decision criteria for mapping patterns​

Decide which pattern to use based on the following criteria.

Decision criterionWhat to considerPattern to choose
LifecycleCan the object exist independentlyIndependent → separate table, dependent → embedded
Change frequencyIs it updated separatelySeparately → separate table, together → same table
ReusabilityIs it referenced by other entitiesReferenced → separate table, not → embedded
Data sizeIs the data volume largeLarge → separate table, small → embedded is also fine
PerformanceDoes the JOIN cost become a problemProblem → embedded, no problem → separate table

Example: the decision for a shipping address

Case A: A snapshot at the time of the order
- Lifecycle: dependent on the order (does not exist on its own)
- Change frequency: updated only together with the order
- Reusability: dedicated to that order (not referenced by others)
→ Decision: embedded (Pattern 1)

Case B: An address master that the user manages
- Lifecycle: exists independently (exists before the order)
- Change frequency: the user changes it independently
- Reusability: referenced by multiple orders
→ Decision: separate table (reference)

Pattern 1: Multiple objects → 1 table​

When embedding a value object into an entity.

[Domain Model]
Order (Entity)
└── ShippingAddress (Value Object)
    ├── prefecture
    ├── city
    └── street

      │ Mapping
      ▼

[Table]
orders
├── id
├── status
├── shipping_prefecture  ─┐
├── shipping_city         │ ShippingAddress embedded
└── shipping_street      ─┘

Pattern 2: 1 aggregate → multiple tables​

When there are multiple entities within an aggregate.

[Domain Model]
Aggregate: Order
├── Order (Aggregate Root)
├── OrderLine (Entity) x N
└── ShippingAddress (Value Object)

      │ Mapping
      ▼

[Tables]
orders                    order_lines
├── id (PK)               ├── id (PK)
├── status                ├── order_id (FK) ──────┐
├── shipping_prefecture   ├── product_id          │
├── shipping_city         ├── quantity            │
├── shipping_street       └── unit_price          │
└── created_at                                    │
       ^                                          │
       └──────────────────────────────────────────┘

Pattern 3: 1 entity → multiple tables (for technical reasons)​

You may split for technical reasons such as performance.

[Domain Model]
User (Entity)
├── id
├── email
├── name
├── biography (long text)
├── profileImage (binary)
└── settings (JSON)

      │ Split for performance
      ▼

[Tables]
users                     user_profiles
├── id (PK)               ├── user_id (PK, FK)
├── email                 ├── biography
└── name                  ├── profile_image
                          └── settings

This is a split for technical convenience, not domain convenience. In the domain model it is still a single User entity.

The repository absorbs the gap​

The repository absorbs the gap between the domain model and table design.

// Perform the mapping inside the repository
private function toEntity(OrderModel $model): Order
{
    // Convert multiple tables → 1 aggregate
    $orderLines = $model->orderLines->map(fn($line) => new OrderLine(
        new OrderLineId($line->id),
        new ProductId($line->product_id),
        $line->quantity,
        new Money($line->unit_price, 'JPY'),
    ))->toArray();

    // Convert embedded columns → value object
    $shippingAddress = new ShippingAddress(
        $model->shipping_prefecture,
        $model->shipping_city,
        $model->shipping_street
    );

    return Order::reconstruct(
        new OrderId($model->id),
        OrderStatus::from($model->status),
        $shippingAddress,
        $orderLines,
    );
}

Index design​

In table design, indexes also greatly affect performance. Beginners tend to focus on the table structure, but if you neglect index design, serious performance problems occur as the data volume grows.

What is an index​

An index is like the "table of contents" of a database. Just as a book's table of contents lets you quickly find a specific page, an index lets you search for a specific record quickly.

A search without an index (full table scan)

SELECT * FROM orders WHERE status = 'confirmed';

Without an index:
orders table (1,000,000 records)
↓ full scan (examine 1,000,000 rows in order)
↓ extract the matching records
→ takes several seconds to tens of seconds

A search with an index

SELECT * FROM orders WHERE status = 'confirmed';

With an index:
status_index (an organized table of contents)
↓ quickly find the position of 'confirmed' (B-Tree algorithm)
↓ jump directly to the matching records
→ completes in under 0.01 seconds

Why indexes are important​

AspectWithout an indexWith an index
Search speedSlows down in proportion to the data volumeFast even as the data volume grows
ScalabilityBecomes 10x slower from 10,000 to 100,000 recordsSmall impact from growth (logarithmic)
User experienceSlow screen rendering, timeoutsResults are displayed immediately

Concrete example: an orders table with 1,000,000 records

# Without an index
SELECT * FROM orders WHERE user_id = 123;
→ full table scan: 3 seconds

# With an index on user_id
SELECT * FROM orders WHERE user_id = 123;
→ index scan: 0.01 seconds

A 300x difference!

Basic index strategy​

// Define indexes in a migration
Schema::create('orders', function (Blueprint $table) {
    $table->id();  // primary key (an index is created automatically)
    $table->string('status');
    $table->foreignId('user_id')->constrained();
    $table->timestamps();

    // Single-column index: columns frequently used in WHERE clauses
    $table->index('status');  // WHERE status = 'confirmed'
    $table->index('created_at');  // ORDER BY created_at DESC

    // Composite index: used for searches/sorts on multiple columns
    // Important: the column order matters (narrow down with the first column, sort with the next)
    $table->index(['user_id', 'created_at']);
    // → optimized for WHERE user_id = ? ORDER BY created_at

    // Unique index: a column that does not allow duplicates
    $table->unique('order_number');  // the order number is unique
});

Schema::create('order_lines', function (Blueprint $table) {
    $table->id();
    $table->foreignId('order_id')->constrained()->cascadeOnDelete();
    // ↑ foreignId() automatically creates an index
    $table->foreignId('product_id')->constrained();
    $table->integer('quantity');
    $table->integer('unit_price');

    // An index is created automatically for foreign keys (MySQL, PostgreSQL, etc.)
    // This speeds up JOINs and ON DELETE CASCADE
});

Guidelines for index design​

TargetIndexReasonExample
Primary keyCreated automaticallyUniqueness guarantee and fast searchid
Foreign keyRequiredSpeeds up JOINs and ON DELETE CASCADEorder_id, user_id
Columns used in WHERE clausesRecommendedFaster searchesstatus, email
Columns used in ORDER BY clausesRecommendedFaster sortscreated_at, price
Unique constraintsRequiredFast duplicate checks and uniqueness guaranteeorder_number, email

The order of a composite index​

In a composite index, the column order is very important.

// Case 1: the order user_id, created_at
$table->index(['user_id', 'created_at']);

// Optimized for this query
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;  // ✅ fast

// Not used for this query
SELECT * FROM orders WHERE created_at > '2024-01-01';  // ❌ index not used

The leftmost-prefix rule

A composite index is used from the leftmost column in order.

$table->index(['user_id', 'status', 'created_at']);

// Patterns where it is used
WHERE user_id = 123  // ✅
WHERE user_id = 123 AND status = 'confirmed'  // ✅
WHERE user_id = 123 AND status = 'confirmed' ORDER BY created_at  // ✅

// Patterns where it is not used
WHERE status = 'confirmed'  // ❌ no user_id
WHERE created_at > '2024-01-01'  // ❌ no user_id

The trade-offs of indexes​

Indexes are not a silver bullet. Without proper design, they backfire.

BenefitDrawback
Faster SELECTsSlower INSERT/UPDATE/DELETE
Faster WHERE clausesIncreased disk usage
Faster JOINsIncreased memory usage
Faster ORDER BYThe cost of index maintenance

Problems when you create too many indexes

// Bad example: an index on every column
Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->index('status');
    $table->index('total_amount');
    $table->index('shipping_prefecture');
    $table->index('shipping_city');
    $table->index('shipping_street');  // this may be unnecessary
    $table->index('note');  // this is definitely unnecessary
    // ... 10 or more indexes in total

    // Problems:
    // - Update 10+ indexes on every INSERT
    // - Disk usage more than doubles
    // - The optimizer can no longer choose the right index
});
Best practices for index design
  1. Analyze your actual query patterns: understand the WHERE clauses, JOINs, and ORDER BYs your application uses frequently
  2. Verify with EXPLAIN: check the query's execution plan and whether the index is used
  3. Avoid excessive indexes: delete indexes that are not used
  4. Maintain regularly: re-evaluate performance as the data volume grows
// Check the query's execution plan with EXPLAIN
DB::select('EXPLAIN SELECT * FROM orders WHERE user_id = 123');

// Detect slow queries with Laravel Telescope
// Configure in config/telescope.php
Pitfalls beginners often fall into
  1. Manually creating an index for the primary key: unnecessary (created automatically)
  2. An index on every column: excessive (write performance drops)
  3. Not considering the order of a composite index: the effect is halved
  4. Developing without indexes: adding them later makes migration painful

Design appropriate indexes from the start.

Design points​

PointDescription
Design the domain model firstUnderstand the business rules, then design tables
Do not insist on 1:1Choose the mapping pattern appropriately
Absorb the gap with the repositoryThe domain layer does not know the table structure
Allow technical splittingSplitting for performance is fine
Design indexes appropriatelyIndexes that match the search patterns

Reference resources​

If you want to learn more deeply about the domain model and table design, as well as Laravel's migrations and index design, refer to the resources below.

Laravel official documentation

Design principles

  • Design the domain model first, then design the tables
  • Choose mapping patterns based on business rules
  • Design indexes based on actual query patterns

In the next chapter, we will look at transaction management.