DB Schema: Models, Migrations, Attributes

In this first lesson, we will create our DB structure with Migrations and Models. We have that structure from the client, so we must implement it. But it won't be that simple. There will be a few caveats along the way.

Plan of this lesson

• Create Models and Migrations
• Solve the problem with word "Travel(s)" irregular plural form in Laravel
• Create a Travel Model Accessor for the "number_of_nights" field
• Create a Tour Model price() Attribute for float/integer conversion
• Convert primary key integer IDs to UUIDs

The final result of this lesson will be this visual DB schema generated from a real database:

I'm a big fan of starting the project by building a DB schema because it gives a good feeling about the whole project scope, and raises important potential questions to the client that should be asked as early as possible cause, otherwise, they would lead to a lot of code changes later.

Initial DB Schema from Client

The client is quite technical but not with Laravel. So they provide us with the list of tables and columns, and our task is to convert it to Laravel Migrations/Models.

This is how the client described what we need to create.

Users

• ID
• Email
• Password
• Roles (M2M relationship)

Roles

• ID
• Name

Travels

• ID
• Is Public (bool)
• Slug
• Name
• Description
• Number of days
• Number of nights (virtual, computed by numberOfDays - 1)

Tours

• ID
• Travel ID (M2O relationship)
• Name
• Starting date
• Ending date
• Price (integer, see below)

Also, here are a few points from the client's description that we need to keep in mind:

• We use UUIDs as primary keys instead of incremental IDs;
• Tours prices are integer multiplied by 100: for example, €999 euro will be 99900, but, when returned to Frontends, they will be formatted (99900 / 100).

With all that in mind, let's begin the creation process.

Obviously, we start with installing a new Laravel project, which I will call travelapi:

laravel new travelapi
cd travelapi

And now the database.

Role: Model and Migration

Laravel, by default, comes with a users DB table and Model, so we don't need to change anything there.

We need to create the table for roles and a pivot table between users and roles.

php artisan make:model Role -m

My personal preference is to create Eloquent Models with migrations right away. There are more options and classes to create, like Factories, Seeders, and more, but we will make them when we need them.

Migration file:

Schema::create('roles', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->timestamps();
});

Notice: yes, I remember the client asked for UUIDs. We will refactor all keys to the UUIDs at the end of this lesson.

app/Models/Role.php:

class Role extends Model
{
    use HasFactory;
 
    protected $fillable = ['name'];
}

Notice about $fillable and Mass Assignment

Personally, I have a habit of filling in the $fillable array immediately when the migration is created. This is needed so we would be able to fill the fields with Eloquent like Role::create(['name' => 'Admin']);.

In this case, you must list all DB columns in that array except for auto-increments and timestamps. The alternative "more lazy" approach is to specify the opposite $guarded array and provide the fields which are NOT to be filled. Usually, it's just the id field or empty array: protected $guarded = [];. I prefer the $fillable approach just because it's clearer for other developers in the future what are the Model's fields, without looking at the DB structure or Migrations.

Read more about Mass Assignment here in the official docs.

Role: Pivot Many-to-Many with User

To create a pivot table, we generate the migration:

php artisan make:migration create_role_user_table

An important part is the table name: it should be role_user and not something like user_role or user_roles.

The table name should be both tables in singular form, ordered alphabetically and separated by an underscore.

If you name a pivot table differently, you need to specify that non-standard table name everywhere where the relationships are defined.

Migration file:

Schema::create('role_user', function (Blueprint $table) {
    $table->foreignId('role_id')->constrained();
    $table->foreignId('user_id')->constrained();
});

Notice: The function constrained() is a shortcut that means "relationship with standard field/table names", which in this case is the id column of the roles table, so it's a shorter version of ->references('id')->on('roles').

Now, let's define the relationship of roles() in the default User model.

app/Models/User.php:

use Illuminate\Database\Eloquent\Relations\BelongsToMany;
 
class User extends Authenticatable
{
    // ...
 
    public function roles(): BelongsToMany
    {
        return $this->belongsToMany(Role::class);
    }
}

Notice: that return type of belongsToMany is optional, you don't have to specify it, but it's a good practice for code readability for other developers in the future. I have two YouTube videos about that topic: Example: Why We Need PHP Return Types and Parameter Types in Laravel 10: Why It's So Important

We could also define the opposite relationship users() in the Role model, but I'm a fan of creating relationship functions only when needed. So I have a feeling that we will query the roles of particular users to check their permissions, but we won't query the users of a specific role, at least for now.

Travel: Model with Irregular Plural Form

Next, we need to generate a Model and Migration for the travels DB table.

php artisan make:model Travel -m

And then we open the Migration file to fill in the fields and go "waaaait a minute": the generated table name is "travel", not "travels".

database/migrations/xxxxxx_create_travel_table.php:

public function up(): void
{
    Schema::create('travel', function (Blueprint $table) {
        $table->id();
        $table->timestamps();
    });
}

The reason is that "travel" is an irregular noun in the English language. There's no actual correct word for "travels". Weird, I know.

If we open php artisan tinker and try to pluralize its form, we have this:

> str()->plural('travel');
= "travel"

So we have a problem/situation here. Our options:

• Option 1. Leave it as it is, and just "remember" in the back of our minds that this particular table doesn't have "s" at the end
• Option 2. Or, rename the migration/table into "travels", as the client requested with their naming, and specify that table name in Eloquent Model properties.

In this course, I've chosen the second approach. So this will be our edited migration with "travels", with these fields as the client specified:

database/migrations/xxxxxx_create_travels_table.php:

Schema::create('travels', function (Blueprint $table) {
    $table->id();
    $table->boolean('is_public')->default(false);
    $table->string('slug')->unique();
    $table->string('name');
    $table->text('description');
    $table->unsignedInteger('number_of_days');
    $table->timestamps();
});

We add ->unique() to the slug field because later, we identify the record by the slug (see client's original description), so it can't repeat in multiple records.

And now, we need to specify that table name in the Model, with the $table property, alongside the $fillable that we already discussed above:

app/Models/Travel.php:

class Travel extends Model
{
    use HasFactory;
 
    protected $table = 'travels';
 
    protected $fillable = [
        'is_public',
        'slug',
        'name',
        'description',
        'number_of_days',
    ];
}

Auto-Fill Travel Slug: Eloquent Observer

There are a few "special" columns in the travels DB table: slug and number_of_nights, which both should be auto-filled. But we will process them differently.

Slug is an SEO-friendly string generated with a simple Laravel helper:

str('Some name')->slug();
// Result: "some-name"

The question is where to put that slug auto-filling code? There are a few approaches I would find valid:

• Generate it in the Controller manually
• Generate it in the Model Observer

I choose the second one, so we generate this:

php artisan make:observer TravelObserver --model=Travel

Laravel generates the file with many methods, but we don't need any of them. So we delete the methods like created(), updated(), and others and add just one method of creating(). This way, we modify the Travel record before it is saved into the DB.

app/Observers/TravelObserver.php:

namespace App\Observers;
 
use App\Models\Travel;
 
class TravelObserver
{
    public function creating(Travel $travel): void
    {
        $travel->slug = str()->slug($travel->name);
    }
}

And we need to register this Observer class in the EventServiceProvider:

app/Providers/EventServiceProvider.php:

use App\Models\Travel;
use App\Observers\TravelObserver;
 
// ...
 
public function boot(): void
{
    Travel::observe(TravelObserver::class);
}

And now the slug field would be filled in automatically.

But... wait a minute... shouldn't it be a unique field? So, this approach with the Observer would work if we didn't need that unique validation.

To achieve the uniqueness, we could, of course, tweak our Observer method to check the database, but, luckily, Laravel has a few packages that would do that for us without any Observers:

• cviebrock/eloquent-sluggable
• spatie/laravel-sluggable

Both of them are good and easy to use, just the first one is older, and I am used to it. But feel free to use another one if you wish.

So, we remove all the code above (EventServiceProvider code and the TravelObserver itself) and, instead, do this.

composer require cviebrock/eloquent-sluggable

Then, we add a few things in the Model: a Sluggable trait and a sluggable() method.

app/Models/Travel.php:

use Cviebrock\EloquentSluggable\Sluggable;
 
class Travel extends Model
{
    use HasFactory, HasUuids, Sluggable;
 
    // ...
 
    public function sluggable(): array
    {
        return [
            'slug' => [
                'source' => 'name'
            ]
        ];
    }
}

And that's it! We just need to provide which field to generate the slug from, and the package will take care of the rest. Including the unique validation: if you try to add a Travel record with the name "My travel" twice, the first slug will be "my-travel", and the second slug will be "my-travel-2".

Auto-Calculate "number of nights": Accessor Attribute

Another "special" column in the travels DB table is "number of nights". The client described it this way: Number of nights (virtual, computed by numberOfDays - 1).

The word "virtual" here may be interpreted as "not a real column", which, in the case of Eloquent, may mean Accessor.

So, in the Travel Model, we add this function:

app/Models/Travel.php:

use Illuminate\Database\Eloquent\Casts\Attribute;
 
class Travel extends Model
{
    // ...
 
    public function numberOfNights(): Attribute
    {
        return Attribute::make(
            get: fn ($value, $attributes) => $attributes['number_of_days'] - 1
        );
    }
}

This is a pretty new syntax of Attributes, that appeared in Laravel 9. There's also an older syntax for accessors/mutators which still works and will be supported in future versions, so you could use this if you prefer:

public function getNumberOfNightsAttribute()
{
    return $this->number_of_days - 1;
}

Tour: Model with Relationships

The final table we need to work on is tours. We follow the same workflow:

php artisan make:model Tour -m

Migration file:

Schema::create('tours', function (Blueprint $table) {
    $table->id();
    $table->foreignId('travel_id')->constrained('travels');
    $table->string('name');
    $table->date('starting_date');
    $table->date('ending_date');
    $table->integer('price');
    $table->timestamps();
});

Here you should notice one "non-standard" thing: I did specify ->constrained('travels'). This is related to the same pluralization "flaw" in the English language. If we don't specify that non-standard table name, Laravel will try to look for a relationship with the "travel" table which doesn't exist.

The Model is simple, with fillables:

app/Models/Tour.php:

use Illuminate\Database\Eloquent\Relations\BelongsTo;
 
class Tour extends Model
{
    use HasFactory;
 
    protected $fillable = [
        'travel_id',
        'name',
        'starting_date',
        'ending_date',
        'price',
    ];
 
    public function travel(): BelongsTo
    {
        return $this->belongsTo(Travel::class);
    }
}

As you can see, we defined the relationship here, so let's make the opposite relationship, too: I have a feeling that we will need to query Tours by a specific Travel record.

app/Models/Travel.php:

use Illuminate\Database\Eloquent\Relations\HasMany;
 
class Travel extends Model
{
    // ...
 
    public function tours(): HasMany
    {
        return $this->hasMany(Tour::class);
    }
}

Tour Price: Float/Integer Conversion

There's a well-known truth in the DB schema world: don't store money in floats. I have written a long article about it called Dealing With Money in Laravel/PHP: Best Practices, but the basic functionality is described by the client themselves:

Tours prices are integer multiplied by 100: for example, €999 euro will be 99900, but, when returned to Frontends, they will be formatted (99900 / 100);

So, when saving the Tour record, we need to perform * 100, and when getting the record, we need the / 100 operation.

The perfect candidate to describe it is the pair of Accessor/Mutator Attributes, which we partly touched on above, in the "number of nights" field. Just, this time, we will have both get/set methods described.

app/Models/Tour.php:

use Illuminate\Database\Eloquent\Casts\Attribute;
 
class Tour extends Model
{
    // ...
 
    public function price(): Attribute
    {
        return Attribute::make(
            get: fn ($value) => $value / 100,
            set: fn ($value) => $value * 100
        );
    }
}

Changing Primary Keys to UUIDs

I intentionally left this topic separately after all the other DB operations cause I wanted to talk about this consistently instead of teaching UUIDs "in-between other topics".

Currently, all our primary keys are default id fields with auto-increment behavior. The client wants UUIDs like b898564a-4ce8-4114-9067-142b437075ae, instead.

I have a separate long tutorial UUID in Laravel: All You Need To Know, but, in short, we will do these things:

• Change $table->id() to $table->uuid('id')->primary() everywhere
• Change $table->foreignId() to $table->foreignUuid() everywhere
• Change $table->morphs() to $table->uuidMorphs() in default Laravel migration for personal_access_tokens table
• Add the trait use HasUuids to all Models: this easy-to-use feature appeared in Laravel 9.30

All those changes, one by one:

Users migration:

Schema::create('users', function (Blueprint $table) {
    $table->id(); 
    $table->uuid('id')->primary(); 

Roles migration:

Schema::create('roles', function (Blueprint $table) {
    $table->id(); 
    $table->uuid('id')->primary(); 

Travels migration:

Schema::create('travels', function (Blueprint $table) {
    $table->id(); 
    $table->uuid('id')->primary(); 

Tours migration:

Schema::create('tours', function (Blueprint $table) {
    $table->id(); 
    $table->foreignId('travel_id')->constrained('travels'); 
    $table->uuid('id')->primary(); 
    $table->foreignUuid('travel_id')->constrained('travels'); 

Role_user migration:

Schema::create('role_user', function (Blueprint $table) {
    $table->foreignId('role_id')->constrained(); 
    $table->foreignId('user_id')->constrained(); 
    $table->foreignUuid('role_id')->constrained(); 
    $table->foreignUuid('user_id')->constrained(); 
});

Personal_access_tokens migration:

// This comes from default Laravel and will be used in Sanctum Auth
Schema::create('personal_access_tokens', function (Blueprint $table) {
    $table->morphs('tokenable'); 
    $table->uuidMorphs('tokenable'); 

And now, the Models.

app/Models/User.php:

use Illuminate\Database\Eloquent\Concerns\HasUuids;
 
// ...
 
class User extends Authenticatable
{
    use HasApiTokens, HasFactory, Notifiable, HasUuids;
 
    // ...

app/Models/Role.php:

use Illuminate\Database\Eloquent\Concerns\HasUuids;
 
// ...
 
class Role extends Model
{
    use HasFactory, HasUuids;

app/Models/Travel.php:

use Illuminate\Database\Eloquent\Concerns\HasUuids;
 
// ...
 
class Travel extends Model
{
    use HasFactory, HasUuids;

app/Models/Tour.php:

use Illuminate\Database\Eloquent\Concerns\HasUuids;
 
// ...
 
class Tour extends Model
{
    use HasFactory, HasUuids;

Phew, I guess that's all the changes. Let's (finally) migrate our database?

php artisan migrate

Finally, you can use an external tool to generate the visual DB schema. My favorite is DBeaver, which produces this result just by clicking on the database.

Interestingly, you can see that our project actually has three unrelated sections of the database:

• System Laravel tables
• Users and Roles
• Travels and Tours

So, we have finished our first lesson and have our database ready!

GitHub commits for this lesson:

• DB schema, models, migrations
• Remove Travel Observer in favor of Sluggable package

Links to read/watch more:

• Mass Assignment with $fillable and $guarded: Official docs
• Many-to-Many Pivot Tables: Video: Laravel Pivot Tables - Simple to Advanced Many-to-Many
• Model Observers: Official docs
• Accessor/Mutator Attribute new syntax: Video: New in Laravel 8.77: One Method for Accessors and Mutators in Eloquent
• Return Types in Methods: Video: Example Why We Need PHP Return Types and Video: Parameter Types in Laravel 10 - Why It's So Important
• Sluggable packages: cviebrock/eloquent-sluggable and spatie/laravel-sluggable. Also, Video: Laravel Str::slug() Advanced - 3 Extra Parameters
• Price in floats/integers: Long-Form Tutorial: Dealing With Money in Laravel/PHP - Best Practices
• UUIDs: Long-Form Tutorial: UUID in Laravel - All You Need To Know and Video: New in Laravel 9.30 - UUIDs as Primary Keys with a Trait. Also, Video: Laravel UUID with Foreign Keys - Speed Benchmark of Two Approaches