Laravel Database Structure: 11 Mistakes Developers Make

In Laravel projects, a lot of issues come from non-Laravel mistakes, database structure is one of those. In this tutorial, we will cover the most typical mistakes devs make when structuring DB in Laravel.

For example, have you ever encountered an issue with fields not being nullable? Or maybe you had problems with relationships being incorrect type?

Don't worry - you are not alone! Let's cover the most typical mistakes and how to solve them.

Mistake 1: Not Using Foreign Keys

When defining the relationships, some developers forget to add foreign key definitions to the database. Let's look at an example:

Schema::create('clients', function (Blueprint $table) {
    $table->id();
    $table->string('first_name');
    // ...
});
Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('client');
    // ...
});

As you can see, no ->foreign() here.

This works fine for the most part, but once we have some clients and orders in our system - deleting a client will cause an issue:

If we look at our database, we can see that we still have an order with our deleted client:

This issue happened because our database has no idea what to do in that scenario. All it thinks of - is that we have a number in our client column, which is not tied to anything.

You can solve this issue with foreign keys, as this will tell the database to check if the client exists before doing anything with the client_id column. Let's look at an example:

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('client_id');
    $table->foreign('client_id')->references('id')->on('clients');
    // ...
});

Now we will do the same action - attempting to delete our client:

This time, our deletion has failed, but our Orders table is still working:

Why did this happen? Well, in the database, we have a constraint added. This limits what action can happen with our parent resource.

In our case, we can only delete a Client after first deleting all their orders. Of course, you need to handle this error with a try-catch block, but this provides a better development experience.

Even if you forget about this - Database has your back! It might break the user experience, but this will prevent bigger collapses in your system.

Notice: Foreign Key Column Names

There is one small thing to note here - column naming. In our example, we used client, which is okay to use, but standard practice is to use the singular form of the table name suffixed with _id. So, in our case, it would be:

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('client_id');
    $table->foreign('client_id')->references('id')->on('clients');
    // ...
});

Or, shorter:

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('client_id')->constrained();
    // ...
});

While this is a personal preference - it does help you to understand what the column is for. If you have client - you might think it's some identifier for the client (especially in bigger databases), but if you have client_id - you know it's a foreign key to the clients table. A quick look at a database like this - points us in the right direction:

Mistake 2: Incorrect Column Types

Another common issue with Database design is wrong column types, like creating an int field but needing to add floats later on.

One specific case is quite common - varchar fields with comma-separated values instead of using relationships. Let's look at an example:

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->string('client_id');
    // ...
});

This seems fine, right? Well, it probably is okay until you have something like this:

What do we have here? We have a list of related IDs in the format of a string. We have seen this used as a way to have one-to-many relationships.

For example, we would expect our order to have multiple Clients. But this is different from how it works. This is just a string, and if you try to use it as a relationship:

Model

// ...
 
public function client()
{
    return $this->belongsToMany(Client::class);
}
 
// ...

You will get an error like this:

Even if you try to...