Laravel: A single table for each Model type

Should you repeat yourself? Or you should repeat yourself a little?

Photo by Keagan Henman on Unsplash

(Updated 7/29/19)

Sometimes we have in our application two (or more) Models that share the same attributes, but they differ in only one or two properties. Let’s say — taking into account my Podcast application that I still haven’t finished — that we have a Podcast model, a Song model, and a Soundtrack model.

As you may guess, these three models have common base: each one is a single audio file with bitrate, filename, format, length, tags, etcetera. At the same time, they have different properties incompatible with one another:

• The Podcast have hosts, guests, and belongs to a show,
• The Song has an artist, a track number, a composer, and belongs to an album
• The Soundtrack has an author, composer, and belongs to a movie.

The average angry joe would say that each model would belong to a different table, repeating the same columns for each, and then adding the columns that are specific for each model. Thanks to the Laravel Eloquent ORM, you don’t need to even think about that.

Solution: One table to rule them all

You don’t need to make a table for each model. Instead, you can create a common table for these, and separate each model by a column that describes their type.

In my example of Podcast, Songs and Soundtracks, we need one table called audios, a type column that will separate them, and a json column (or text) called properties to hold the specific properties of each one. You can tell Laravel to index this column to allow better performance when filtering the type of Audio, or do a composite index.

Schema::create('audios', function (Blueprint $table) {
    $table->bigIncrements('id');
    // ... Common Properties
    $table->string(’type’);
    $table->json(’properties’);
    // Do a composite index
    $table->index(['id', 'type']);
});

Once you make your table, you will need to make 3 Models: Podcast, Song and Soundtrack, that would extend an abstract Model called Audio. You can use Artisan CLI for that, so hit the terminal and type:

php artisan make:model Podcast
php artisan make:model Song
php artisan make:model Soundtrack

The magic will be in the global scope.

Globalscoping our way out

The Global Scope in each model will separate the Model from the main table by just adding a where clause, filtering the string that identifies the model. In the case of Podcast, the query will say “all audios where the type is podcast”.

public function boot()
{
    parent::boot();
    // Automatically filter the Podcast from the table
    static::addGlobalScope('podcast', function (Builder $builder) {
        $builder->where('type', 'podcast');
    });
}

When creating a model, we will add in each Model a simple Eloquent Event: when we create a Podcast, Song or Soundtrack, we will add the type to the attributes. So we need to add that code.

public function boot()
{
    parent::boot();
    // Automatically filter the Podcast from the table
    static::addGlobalScope('podcast', function (Builder $builder) {
        $builder->where('type', 'podcast');
    });
    // Save the type when creating this model
    static::creating(function ($podcast) {
        $podcast->forceFill([
            'type' => 'podcast',
        ]);
    });
}

You can put that code wherever you want, but for me it make sense in the same class as part of the booting of the model.

Custom properties

Remember those specific columns that only work for each type of model? That’s why the properties exists. This column to hold your data, like the show, the album or the movie names, or a full array of custom properties.

While an array or JSON will depend on your application database compatibility, personally I use JSON when I need a bunch of unfilterable data. The rest of columns can be filtered with SQL. Depending on your database engine, you may even filter by JSON.

Common properties

To avoid repeating the same scopes, accessors and mutators, helpers methods, and what else, you have two options to stay in the DRY side of the force:

• create an abstract class called Audio that the Podcast, Song and Soundtrack will extend,
• or create a trait called Hearable and use it in the other 3 models.

Abstract Class or Trait, these must be compatible with these models. Since you can override methods and properties, it’s okay if one Model needs something particularly different.

If you ask me, an Abstract class is always a good option if you plan to have everything in one place, while a Trait may do if you need a lot of specific logic for each model. As always, you can do what you feel that makes more sense for your application instead of just following what some other dude said.

The advantages?

Well, let’s start enumerating:

1. If you need to add another model, called AudioLog, you can just simply create an Eloquent Model and add the Global Scope and attribute filling. No need to edit the database.
2. Since it’s one table, you don’t need to heavily rewire relationships. You can even use polymorphic relationships without any hassle.
3. Using a JSON column allows you to set whatever you want inside. Latest versions of MySQL, MariaDB, PostgreSQL and SQL Server allow to even filter by JSON values.

And with that you should be ready to go. No need to create more tables that you can chew, or overthink models.