Database Design Patterns for Laravel Applications

Database Design Matters

Poor database design creates performance problems that are expensive to fix later. Thinking carefully about schema design upfront saves significant pain down the road.

Normalization vs Denormalization

// Normalized: No duplication, joins required
// orders -> order_items -> products

// Denormalized: Duplication, faster reads
// orders with product_name, product_price copied

// Choose based on read/write ratio and query patterns

Polymorphic Relationships

// Laravel's polymorphic relationships
Schema::create('comments', function (Blueprint $table) {
    $table->id();
    $table->morphs('commentable'); // commentable_type, commentable_id
    $table->text('body');
    $table->timestamps();
});

// Post, Video, and Photo can all have comments
class Comment extends Model
{
    public function commentable(): MorphTo
    {
        return $this->morphTo();
    }
}

Soft Deletes and Audit Trails

// Soft deletes preserve data
Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->softDeletes();
    $table->timestamps();
});

// Audit trail for changes
Schema::create('order_history', function (Blueprint $table) {
    $table->id();
    $table->foreignId('order_id');
    $table->string('field');
    $table->text('old_value')->nullable();
    $table->text('new_value');
    $table->foreignId('changed_by');
    $table->timestamp('changed_at');
});

JSON Columns for Flexibility

// Store flexible data without schema changes
Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->json('attributes'); // color, size, etc.
    $table->timestamps();
});

// Query JSON columns
Product::where('attributes->color', 'red')->get();

Indexing Strategy

// Index columns used in WHERE, JOIN, ORDER BY
Schema::table('orders', function (Blueprint $table) {
    // Single column index
    $table->index('status');

    // Composite index for common query pattern
    $table->index(['customer_id', 'created_at']);

    // Unique constraint with index
    $table->unique(['order_number', 'year']);
});

Partitioning Large Tables

// For very large tables, partition by date
// In MySQL:
DB::statement('
    ALTER TABLE events
    PARTITION BY RANGE (YEAR(created_at)) (
        PARTITION p2023 VALUES LESS THAN (2024),
        PARTITION p2024 VALUES LESS THAN (2025),
        PARTITION p2025 VALUES LESS THAN (2026)
    )
');

Conclusion

Good database design balances normalization with query performance. Index thoughtfully, use appropriate data types, and plan for growth. The schema is the foundation everything else builds on.