Database and Storage: Where Data Comes to Rest

Part three of the full-stack series. Your code gets rewritten; your data is forever. A framework migration is a weekend; a botched schema migration on a live table is a war story. The database is the layer you most want to get right early, because it’s the hardest to change later. This is how I approach schema, queries, and file storage in a Laravel + MySQL stack.

Model the data, not the screen

The classic beginner mistake is designing tables to match a UI. The screen changes every quarter; the relationships in your domain don’t. Model the nouns and how they relate:

Get the relationships right and the queries fall out naturally. Get them wrong and you’ll fight the schema forever with duplicated data and update anomalies. Normalize first; denormalize later, deliberately, only where a measured read path demands it.

Indexes: the difference between 5ms and 5 seconds

An index is a lookup structure so the database doesn’t scan every row. The rule of thumb: index the columns you filter, join, and sort on. A foreign key you WHERE against without an index is a full table scan waiting to happen.

Schema::create('posts', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->index();
    $table->string('slug')->unique();        // unique = index + constraint
    $table->timestamp('published_at')->nullable()->index();
    $table->timestamps();
});

The cost: indexes make writes slightly slower and take disk. The benefit: reads go from seconds to milliseconds. On a read-heavy app that’s the whole ballgame — but don’t index everything blindly; each one is write overhead you pay forever.

The N+1 query: the silent performance killer

This is the bug I see most in code review. Loop over posts, touch post.author inside the loop, and you fire one query per post — 1 query becomes 101.

// N+1: one query for posts, then one per post for the author
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->author->name;   // a query every iteration
}

// Fixed: eager load — two queries total, regardless of count
$posts = Post::with('author')->get();

It hides perfectly in development with 10 rows and detonates in production with 10,000. Catch it with Laravel’s preventLazyLoading() in local/dev so a lazy load throws instead of silently shipping. This one fix routinely turns a 2-second page into 50ms.

Migrations are how schema changes safely

Never edit a production database by hand. Migrations are version-controlled, reviewable, repeatable schema changes that run the same in every environment — the database equivalent of the version control you already use for code:

public function up(): void
{
    Schema::table('posts', function (Blueprint $table) {
        $table->string('status')->default('draft')->after('body')->index();
    });
}

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

On a big live table, even an “innocent” ALTER can lock writes — plan those during low traffic, and for huge tables reach for an online-schema-change tool rather than a raw migration.

Files don’t belong in the database

Storing images or PDFs as blobs in MySQL bloats the database, slows backups, and wastes the query engine on bytes it can’t index. Files belong in object storage — S3, DigitalOcean Spaces, or any S3-compatible bucket — with only the path in the database:

$path = $request->file('avatar')->store('avatars', 's3');
$user->update(['avatar_path' => $path]);
// serve via a CDN-backed URL, covered in the caching post

The database stays lean and fast; the files sit on storage built for them and get served through a CDN.

Caveats and best practices

• Back up, and test the restore. A backup you’ve never restored is a hope, not a backup. More in the availability post.
• Use transactions for multi-step writes. Money moving between accounts must be all-or- nothing. DB::transaction(fn () => …) or it didn’t happen.
• Foreign-key constraints in the database, not just the app. App-only integrity is one buggy script away from orphaned rows. Let the database enforce it too.
• Watch your slow query log. It tells you exactly which queries need an index, with real production data — no guessing.

Conclusion

Schema   → model relationships, normalize first
Indexes  → filter/join/sort columns; not everything
N+1      → eager load (with()); prevent lazy loading in dev
Migrate  → version-controlled, reversible, low-traffic for big ALTERs
Files    → object storage + CDN, path in the DB

The database outlives every other layer, so the early decisions compound — model honestly, index deliberately, and keep files where they belong. Next: auth and permissions, where we decide who gets to touch all this data.