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

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

As you may guess, these three models have common property: 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 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. You only need one table called audios, a type column that will separate them, and a json column (or text) called properties to hold its properties that differ in 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->string(’type’);
$table->json(’properties’);
});

Once you make your table, you will need to make 3 Models: Podcast, Song and Soundtrack, that would extend an abstract Model called Audio. The magic will be in the global scope.

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();
    static::addGlobalScope('podcast', function (Builder $builder) {
$builder->where('type', 'podcast');
});
}

From there, you can use the properties column to hold your data, like the show, the album or the movie names, or a full array of custom properties. That will depend on your application, but personally I use JSON when I need a bunch of unfilterable data, and leave in a column the properties that should be filtered with SQL.

You can also use an hypothetical IsAudioFile trait and use it in the other 3 classes.

Abstract Class or Trait, these must contain all the common methods for the classes that extend it. Since you can override methods and properties, it’s okay if one Model needs something particularly different.

Personally I go with first option unless I have some packages or script that doesn’t like to deal with abstract classes automatically. As always, you can do what you want.

The advantages?

Well, let’s start enumerating:

  • If you need to add another model, called “AudioLog”, you can just simple create an Eloquent Model and add the Global Scope to filter by the audiolog type. No need to edit the database.
  • Since it’s one table, you don’t need to heavily rewire relationships. You can use polymorphic relationships.
  • 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.