database-design

name	database-design
description	Design database schemas with Laravel migrations, Eloquent relationships, and indexing strategies. Use when creating migrations, designing table schemas, defining relationships between models, optimizing queries with indexes, or planning database architecture. Triggers on migration, database schema, table design, foreign key, index, relationship, Eloquent, or query optimization.

Database Design with Laravel

Migration Best Practices

Naming Conventions

Laravel uses snake_case naming for migration files. The artisan generator infers the table name from the migration name:

# Creating tables
php artisan make:migration create_orders_table
php artisan make:migration create_order_items_table

# Modifying tables
php artisan make:migration add_status_to_orders_table
php artisan make:migration add_tracking_number_to_orders_table
php artisan make:migration rename_title_to_name_on_products_table
php artisan make:migration drop_legacy_column_from_users_table

One Change Per Migration

Each migration should do one logical thing. This makes rollbacks predictable:

// Good: single responsibility
// create_orders_table migration
public function up(): void
{
    Schema::create('orders', function (Blueprint $table) {
        $table->id();
        $table->foreignId('user_id')->constrained()->cascadeOnDelete();
        $table->foreignId('customer_id')->constrained();
        $table->string('order_number')->unique();
        $table->enum('status', ['pending', 'confirmed', 'shipped', 'delivered', 'cancelled'])
            ->default('pending');
        $table->decimal('subtotal', 10, 2);
        $table->decimal('tax', 10, 2)->default(0);
        $table->decimal('total', 10, 2);
        $table->text('notes')->nullable();
        $table->timestamp('shipped_at')->nullable();
        $table->timestamp('delivered_at')->nullable();
        $table->timestamps();
        $table->softDeletes();
    });
}

public function down(): void
{
    Schema::dropIfExists('orders');
}

Always Implement down()

The down() method must reverse exactly what up() does. This allows safe rollbacks:

// add_status_to_orders_table
public function up(): void
{
    Schema::table('orders', function (Blueprint $table) {
        $table->string('status')->default('pending')->after('total');
    });
}

public function down(): void
{
    Schema::table('orders', function (Blueprint $table) {
        $table->dropColumn('status');
    });
}

Standard Column Helpers

Always use the built-in helpers for consistency:

$table->id();                    // BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
$table->uuid('id')->primary();  // UUID primary key alternative
$table->ulid('id')->primary();  // ULID primary key alternative
$table->timestamps();            // created_at and updated_at TIMESTAMP NULLABLE
$table->softDeletes();           // deleted_at TIMESTAMP NULLABLE
$table->rememberToken();         // remember_token VARCHAR(100) NULLABLE

---

Column Types Reference

Migration Method	MySQL Type	PostgreSQL Type	Purpose
$table->id()	BIGINT UNSIGNED AI PK	BIGSERIAL PK	Auto-incrementing primary key
$table->uuid('id')	CHAR(36)	UUID	UUID column
$table->ulid('id')	CHAR(26)	CHAR(26)	ULID column
$table->string('name')	VARCHAR(255)	VARCHAR(255)	Short text (names, emails)
$table->string('code', 10)	VARCHAR(10)	VARCHAR(10)	Short text with max length
$table->text('body')	TEXT	TEXT	Long text (descriptions)
$table->mediumText('content')	MEDIUMTEXT	TEXT	Medium-length content
$table->longText('payload')	LONGTEXT	TEXT	Very long content (JSON blobs)
$table->integer('qty')	INT	INTEGER	Standard integer
$table->bigInteger('views')	BIGINT	BIGINT	Large integer
$table->unsignedInteger('qty')	INT UNSIGNED	INTEGER	Non-negative integer
$table->tinyInteger('level')	TINYINT	SMALLINT	Small integer (0-127)
$table->boolean('active')	TINYINT(1)	BOOLEAN	True/false flag
$table->decimal('price', 10, 2)	DECIMAL(10,2)	NUMERIC(10,2)	Exact decimal (money)
$table->float('rating')	FLOAT	REAL	Approximate decimal
$table->date('birth_date')	DATE	DATE	Date without time
$table->dateTime('starts_at')	DATETIME	TIMESTAMP	Date with time
$table->timestamp('sent_at')	TIMESTAMP	TIMESTAMP	Timestamp
$table->time('opens_at')	TIME	TIME	Time without date
$table->year('vintage')	YEAR	INTEGER	Year only
$table->json('metadata')	JSON	JSONB	JSON data
$table->enum('status', [...])	ENUM	VARCHAR + CHECK	Enumerated values
$table->binary('photo')	BLOB	BYTEA	Binary data
$table->ipAddress('visitor')	VARCHAR(45)	INET	IPv4 or IPv6 address
$table->macAddress('device')	VARCHAR(17)	MACADDR	MAC address

Column Modifiers

$table->string('email')->unique();            // Unique constraint
$table->string('nickname')->nullable();       // Allow NULL
$table->string('status')->default('active');  // Default value
$table->integer('position')->unsigned();      // Unsigned (non-negative)
$table->text('bio')->nullable()->after('email'); // Column ordering (MySQL)
$table->string('slug')->comment('URL slug');  // Column comment
$table->string('legacy')->virtualAs("CONCAT(first_name, ' ', last_name)"); // Generated column

---

Index Strategies

When to Add Indexes

Add indexes on columns that appear in:

• WHERE clauses (equality and range lookups)
• JOIN conditions (foreign keys)
• ORDER BY clauses (sorting)
• GROUP BY clauses (aggregation)

Do NOT index columns that:

• Have very low cardinality (e.g., a boolean with 50/50 distribution on a small table)
• Are rarely queried
• Are on tables with very few rows

Creating Indexes

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained();
    $table->string('status');
    $table->timestamp('created_at');

    // Single column index
    $table->index('status');

    // Composite index (column order matters — leftmost prefix rule)
    $table->index(['user_id', 'status', 'created_at']);

    // Unique constraint (also creates an index)
    $table->unique('order_number');

    // Composite unique
    $table->unique(['user_id', 'product_id']);
});

Composite Index Column Order

The leftmost prefix rule means a composite index (user_id, status, created_at) can satisfy queries that filter on:

• user_id alone
• user_id AND status
• user_id AND status AND created_at

It will NOT help queries that filter on:

• status alone (needs its own index)
• created_at alone

Put the most selective column first, and the range column last.

Full-Text Indexes

$table->fullText('body');                      // Single column
$table->fullText(['title', 'body']);            // Multi-column

// Querying
Product::whereFullText('description', 'wireless headphones')->get();
Product::whereFullText(['title', 'description'], 'wireless headphones')->get();

Adding Indexes to Existing Tables

// add_index_to_orders_table migration
public function up(): void
{
    Schema::table('orders', function (Blueprint $table) {
        $table->index('status', 'orders_status_index');
        $table->index(['user_id', 'created_at'], 'orders_user_created_index');
    });
}

public function down(): void
{
    Schema::table('orders', function (Blueprint $table) {
        $table->dropIndex('orders_status_index');
        $table->dropIndex('orders_user_created_index');
    });
}

---

Foreign Key Conventions

Standard Foreign Keys

// Shorthand (Laravel convention: column name infers table)
$table->foreignId('user_id')->constrained()->cascadeOnDelete();

// Explicit table reference
$table->foreignId('author_id')->constrained('users')->cascadeOnDelete();

// Nullable foreign key
$table->foreignId('category_id')->nullable()->constrained()->nullOnDelete();

On Delete Behaviors

Method	SQL	Behavior
cascadeOnDelete()	ON DELETE CASCADE	Delete child rows when parent is deleted
nullOnDelete()	ON DELETE SET NULL	Set foreign key to NULL (column must be nullable)
restrictOnDelete()	ON DELETE RESTRICT	Prevent parent deletion if children exist
noActionOnDelete()	ON DELETE NO ACTION	Same as RESTRICT in most databases

On Update Behaviors

$table->foreignId('user_id')->constrained()->cascadeOnUpdate()->cascadeOnDelete();

Choosing the Right Delete Behavior

• cascadeOnDelete: Order -> OrderItems (items are meaningless without the order)
• nullOnDelete: Post -> author_id (keep the post even if the user is deleted)
• restrictOnDelete: User -> Orders (prevent deleting users who have orders)

---

Eloquent Relationships

1. One-to-One: hasOne / belongsTo

// Migration: create_profiles_table
$table->id();
$table->foreignId('user_id')->unique()->constrained()->cascadeOnDelete();
$table->string('bio')->nullable();
$table->string('avatar_url')->nullable();
$table->timestamps();

// User model
public function profile(): HasOne
{
    return $this->hasOne(Profile::class);
}

// Profile model
public function user(): BelongsTo
{
    return $this->belongsTo(User::class);
}

// Usage
$user->profile;
$user->profile()->create(['bio' => 'Developer']);
$profile->user;

2. One-to-Many: hasMany / belongsTo

// Migration: create_order_items_table
$table->id();
$table->foreignId('order_id')->constrained()->cascadeOnDelete();
$table->foreignId('product_id')->constrained();
$table->integer('quantity');
$table->decimal('unit_price', 10, 2);
$table->timestamps();

// Order model
public function items(): HasMany
{
    return $this->hasMany(OrderItem::class);
}

// OrderItem model
public function order(): BelongsTo
{
    return $this->belongsTo(Order::class);
}

// Usage
$order->items;
$order->items()->where('quantity', '>', 1)->get();
$order->items()->create([...]);

3. Many-to-Many: belongsToMany

// Migration: create_product_tag_table (pivot)
$table->id();
$table->foreignId('product_id')->constrained()->cascadeOnDelete();
$table->foreignId('tag_id')->constrained()->cascadeOnDelete();
$table->timestamps();
$table->unique(['product_id', 'tag_id']);

// Product model
public function tags(): BelongsToMany
{
    return $this->belongsToMany(Tag::class)->withTimestamps();
}

// Tag model
public function products(): BelongsToMany
{
    return $this->belongsToMany(Product::class)->withTimestamps();
}

// Usage
$product->tags()->attach([1, 2, 3]);
$product->tags()->detach([2]);
$product->tags()->sync([1, 3, 5]);       // replaces all
$product->tags()->syncWithoutDetaching([4]); // adds without removing

4. Has-Through: hasOneThrough / hasManyThrough

// Country -> User -> Post
// Country model
public function posts(): HasManyThrough
{
    return $this->hasManyThrough(Post::class, User::class);
}

// Access all posts from a country without manually joining
$country->posts;

5. Polymorphic: morphOne / morphMany / morphTo

// Migration: create_comments_table
$table->id();
$table->morphs('commentable'); // creates commentable_type and commentable_id columns
$table->foreignId('user_id')->constrained();
$table->text('body');
$table->timestamps();

// Post model
public function comments(): MorphMany
{
    return $this->morphMany(Comment::class, 'commentable');
}

// Video model
public function comments(): MorphMany
{
    return $this->morphMany(Comment::class, 'commentable');
}

// Comment model
public function commentable(): MorphTo
{
    return $this->morphTo();
}

// Register morph map in AppServiceProvider::boot()
Relation::enforceMorphMap([
    'post' => Post::class,
    'video' => Video::class,
]);

6. Many-to-Many Polymorphic: morphToMany / morphedByMany

// Migration: create_taggables_table
$table->id();
$table->foreignId('tag_id')->constrained()->cascadeOnDelete();
$table->morphs('taggable');
$table->unique(['tag_id', 'taggable_type', 'taggable_id']);

// Post model
public function tags(): MorphToMany
{
    return $this->morphToMany(Tag::class, 'taggable');
}

// Video model
public function tags(): MorphToMany
{
    return $this->morphToMany(Tag::class, 'taggable');
}

// Tag model
public function posts(): MorphedByMany
{
    return $this->morphedByMany(Post::class, 'taggable');
}

public function videos(): MorphedByMany
{
    return $this->morphedByMany(Video::class, 'taggable');
}

---

Pivot Tables

Extra Columns on Pivot

// Migration
Schema::create('order_product', function (Blueprint $table) {
    $table->id();
    $table->foreignId('order_id')->constrained()->cascadeOnDelete();
    $table->foreignId('product_id')->constrained();
    $table->integer('quantity')->default(1);
    $table->decimal('unit_price', 10, 2);
    $table->timestamps();
    $table->unique(['order_id', 'product_id']);
});

// Order model
public function products(): BelongsToMany
{
    return $this->belongsToMany(Product::class)
        ->withPivot('quantity', 'unit_price')
        ->withTimestamps();
}

// Accessing pivot data
foreach ($order->products as $product) {
    echo $product->pivot->quantity;
    echo $product->pivot->unit_price;
}

// Attaching with extra data
$order->products()->attach($productId, [
    'quantity' => 2,
    'unit_price' => 29.99,
]);

Custom Pivot Model

// app/Models/OrderProduct.php
use Illuminate\Database\Eloquent\Relations\Pivot;

class OrderProduct extends Pivot
{
    protected $casts = [
        'unit_price' => 'decimal:2',
    ];

    public function getLineTotalAttribute(): float
    {
        return $this->quantity * $this->unit_price;
    }
}

// Order model
public function products(): BelongsToMany
{
    return $this->belongsToMany(Product::class)
        ->using(OrderProduct::class)
        ->withPivot('quantity', 'unit_price')
        ->withTimestamps();
}

---

Soft Deletes

Setup

// Migration
$table->softDeletes(); // adds deleted_at TIMESTAMP NULLABLE

// Model
use Illuminate\Database\Eloquent\SoftDeletes;

class Order extends Model
{
    use SoftDeletes;
}

Query Behavior

Order::all();              // excludes soft-deleted (default)
Order::withTrashed()->get(); // includes soft-deleted
Order::onlyTrashed()->get(); // only soft-deleted

$order->trashed();         // check if soft-deleted
$order->restore();         // restore soft-deleted record
$order->forceDelete();     // permanently delete

Unique Constraint with Soft Deletes

A unique constraint on email will conflict with soft-deleted records. Solutions:

// Option 1: Partial unique index (PostgreSQL only)
DB::statement('CREATE UNIQUE INDEX users_email_unique ON users (email) WHERE deleted_at IS NULL');

// Option 2: Include deleted_at in composite unique (MySQL)
$table->unique(['email', 'deleted_at']);

// Option 3: Nullify the unique field on soft delete
public static function booted(): void
{
    static::softDeleted(function (User $user) {
        $user->updateQuietly(['email' => $user->email . '::deleted::' . $user->id]);
    });
}

When to Use Soft Deletes

Use soft deletes when:

• Records must be auditable (orders, invoices, financial data)
• Records might need to be restored
• Related data references the record and you need referential integrity

Avoid soft deletes when:

• Data is truly disposable (logs, temporary records)
• Table will grow very large and soft-deleted records add overhead
• You are using the table as a queue or buffer

---

Database Transactions

Automatic Transaction with Retry

use Illuminate\Support\Facades\DB;

$order = DB::transaction(function () use ($request) {
    $order = Order::create([
        'user_id' => auth()->id(),
        'total' => 0,
    ]);

    foreach ($request->items as $item) {
        $product = Product::lockForUpdate()->findOrFail($item['product_id']);

        if ($product->stock < $item['quantity']) {
            throw new InsufficientStockException($product);
        }

        $product->decrement('stock', $item['quantity']);

        $order->items()->create([
            'product_id' => $product->id,
            'quantity' => $item['quantity'],
            'unit_price' => $product->price,
        ]);
    }

    $order->update(['total' => $order->items->sum(fn ($i) => $i->quantity * $i->unit_price)]);

    return $order;
}, attempts: 3); // retries on deadlock

Manual Transaction Control

DB::beginTransaction();

try {
    $order = Order::create([...]);
    $order->items()->createMany([...]);

    DB::commit();
} catch (\Exception $e) {
    DB::rollBack();
    throw $e;
}

Savepoints (Nested Transactions)

DB::transaction(function () {
    Order::create([...]);

    DB::transaction(function () {
        // This creates a savepoint
        // If this inner block fails, only this portion rolls back
        Payment::create([...]);
    });
});

---

Query Optimization

Eager Loading (Preventing N+1)

// Bad: N+1 problem (1 query for orders + N queries for customers)
$orders = Order::all();
foreach ($orders as $order) {
    echo $order->customer->name; // lazy-loads customer each iteration
}

// Good: Eager load (2 queries total)
$orders = Order::with('customer')->get();

// Nested eager loading
$orders = Order::with('customer', 'items.product')->get();

// Constrained eager loading
$orders = Order::with(['items' => function ($query) {
    $query->where('quantity', '>', 1)->orderBy('unit_price', 'desc');
}])->get();

// Lazy eager loading (when you already have the collection)
$orders = Order::all();
$orders->load('customer');

Prevent Lazy Loading in Development

// app/Providers/AppServiceProvider.php
public function boot(): void
{
    Model::preventLazyLoading(! app()->isProduction());
}

This throws an exception whenever a relationship is lazy-loaded, forcing you to use eager loading everywhere.

Subquery Selects

// Add computed data without loading the relationship
$users = User::query()
    ->addSelect([
        'last_order_at' => Order::select('created_at')
            ->whereColumn('user_id', 'users.id')
            ->latest()
            ->take(1),
    ])
    ->get();

Aggregate Functions on Relationships

// withCount
$orders = Order::withCount('items')->get();
echo $orders->first()->items_count;

// withSum, withAvg, withMin, withMax
$orders = Order::withSum('items', 'quantity')
    ->withAvg('items', 'unit_price')
    ->get();
echo $orders->first()->items_sum_quantity;
echo $orders->first()->items_avg_unit_price;

Chunking Large Datasets

// Process records in chunks (memory-efficient)
Order::where('status', 'pending')->chunk(500, function ($orders) {
    foreach ($orders as $order) {
        $order->processReminder();
    }
});

// Lazy collection (even more memory-efficient for iteration)
Order::where('status', 'pending')->lazy()->each(function ($order) {
    $order->processReminder();
});

// Chunk by ID (safer for updates)
Order::where('status', 'pending')->chunkById(500, function ($orders) {
    foreach ($orders as $order) {
        $order->update(['reminded_at' => now()]);
    }
});

When to Use Raw Queries

Use raw queries sparingly and only when Eloquent cannot express the logic efficiently:

// Complex aggregation
$stats = DB::select(<<<'SQL'
    SELECT
        DATE(created_at) as date,
        COUNT(*) as total_orders,
        SUM(total) as revenue,
        AVG(total) as avg_order_value
    FROM orders
    WHERE created_at >= ?
    GROUP BY DATE(created_at)
    ORDER BY date DESC
SQL, [now()->subDays(30)]);

// Using selectRaw within Eloquent
$orders = Order::query()
    ->selectRaw('DATE(created_at) as date, COUNT(*) as count')
    ->groupByRaw('DATE(created_at)')
    ->get();

Database Query Logging

// Enable query log in development
DB::enableQueryLog();

// ... run queries ...

dd(DB::getQueryLog()); // see all executed queries with bindings and time

Useful Artisan Commands

# Run all pending migrations
php artisan migrate

# Rollback last batch
php artisan migrate:rollback

# Rollback all and re-run
php artisan migrate:fresh          # drops all tables, runs all migrations
php artisan migrate:fresh --seed   # also runs seeders

# Check migration status
php artisan migrate:status

# Generate a migration
php artisan make:migration create_orders_table

# Generate a model with migration, factory, seeder, controller, and form requests
php artisan make:model Order -mfscR