Schema Design and Data Modeling

Design Decisions That Compound

Your database schema is the foundation everything else is built on. A well-designed schema makes queries simple and fast. A poorly-designed one creates technical debt that compounds with every new feature.

Normalization: Eliminating Redundancy

Normalization is the process of organizing data to reduce redundancy. The key principles:

• First Normal Form (1NF) — Each column holds a single value (no comma-separated lists, no JSON arrays for data you need to query).
• Second Normal Form (2NF) — Every non-key column depends on the entire primary key, not just part of it.
• Third Normal Form (3NF) — No non-key column depends on another non-key column. If a posts table has author_name, that's a 3NF violation — the name depends on the author_id, not the post.

In practice: if you're storing the same data in multiple places, you have a normalization problem. Put it in one place and reference it with foreign keys.

When to Denormalize

Normalization optimizes for writes (change data in one place). Denormalization optimizes for reads (avoid expensive JOINs).

Common denormalization patterns in Laravel:

• Counter caches — Store comments_count on the posts table instead of counting on every query. Use withCount() or the $withCount property.
• Materialized views — Pre-computed aggregations stored in a separate table, refreshed periodically.
• JSON columns — Store metadata that doesn't need to be queried relationally. Laravel supports $casts = ['options' => 'array'].

Start normalized. Denormalize only when you have measured performance problems. Premature denormalization creates update anomalies and data inconsistencies.

Polymorphic Relationships

Laravel's polymorphic relationships let a model belong to multiple types of models via a single association. A comments table can hold comments for both posts and videos:

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

The commentable_type column stores the class name (App\Models\Post or App\Models\Video), and commentable_id stores the ID.

Polymorphic relationships are powerful but have tradeoffs: you can't use foreign key constraints (since the ID could reference different tables), and the type column adds complexity to queries.

Migration Discipline

In production, migrations are immutable history. Never edit a migration that has already been run in production. Instead:

• Create a new migration to modify the schema.
• Use $table->string('column')->nullable() for new columns to avoid breaking existing rows.
• Add default values for new columns that can't be null.
• Test your migration can run against the current production schema, not just a fresh database.

Key Takeaways

• Normalize by default — one source of truth, referenced by foreign keys.
• Denormalize only when you have measured performance problems.
• Polymorphic relationships are flexible but sacrifice foreign key constraints.
• Treat production migrations as immutable. Always create new migrations to modify existing tables.