Naming Things in Databases: 4 Tips

Usually, when creating Models and Migrations, you should stick to Laravel's naming convention and use English words. Then, Laravel will try to guess names. In this lesson, I will advise you on choosing/customizing names in Laravel DB.

Break Default Table Names Convention? Careful.

For example, if you call your Model Role, Laravel will know its table should be roles. But what if names are different?

You can set the table names on the Model in the protected $table property. So, if you have a singular role for the table name, you would set it in the Role Model.

class Role extends Model
{
    protected $table = 'role'; 
 
    // ...
}

When using a belongs-to-many relationship, a pivot table should be created with the two tables' singular names in alphabetical order. For the users and roles relationship, it would be role_user and would contain foreign keys to both tables.

Usually, foreign key fields are named with the singular tables name and a prefix of _id. The migration for the role_user table would look like this:

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

The constrained() method will guess the table's name and use the id field for the constrained table. However, sometimes, foreign keys can be different based on the application. For example, you have a Team Model and want to set the team owner on the Teams table. If you use user_id for the field, it wouldn't be clear what it stands for.

The field could be named owner_id, but users are in the users table. In this case, we can set the constrained table to the users in the constrained() method first parameter.

Schema::create('teams', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->foreignId('owner_id')->constrained('users'); 
});

Another similar example is from the open-source project firefly-iii. You might want to shorten DB table/column names if they are too long. The rt stands for recurrences_transactions.

// ...
 
Schema::create(
    'rt_meta',
    static function (Blueprint $table): void {
        $table->increments('id');
        $table->timestamps();
        $table->softDeletes();
        $table->integer('rt_id', false, true); 
 
        $table->string('name', 50);
        $table->text('value');
 
        $table->foreign('rt_id')->references('id')->on('recurrences_transactions')->onDelete('cascade'); 
    }
);
 
// ...

Of course, you would then set the table name in the Model using the $table property.

Another example is from the open-source project koel, where you might want to name the column differently when the prefix is repeated.

Schema::create('playlist_playlist_folder', static function (Blueprint $table): void {
    $table->string('folder_id', 36)->nullable(false); 
    $table->string('playlist_id', 36)->nullable(false);
});
 
Schema::table('playlist_playlist_folder', static function (Blueprint $table): void {
    $table->foreign('folder_id')
        ->references('id')
        ->on('playlist_folders')
        ->cascadeOnDelete()
        ->cascadeOnUpdate();
 
    $table->foreign('playlist_id')
        ->references('id')
        ->on('playlists')
        ->cascadeOnDelete()
        ->cascadeOnUpdate();
 
    $table->unique(['folder_id', 'playlist_id']);
});

Prefix "is_xxxxx" for Boolean Fields

Quite often, I see fields in databases named active or encrypted, like in this example:

Schema::create('piggy_banks',
    static function (Blueprint $table): void {
        $table->increments('id');
 
        // ... more fields
 
        $table->boolean('active')->default(0);
        $table->boolean('encrypted')->default(1);
    }
);

If you look at the migration, it's clearly a Boolean field. But if a developer finds that column mentioned somewhere in the code, it may not be clear:

• Is "active" a yes/no, or some ENUM with values?
• Is "encrypted" a yes/no, or a STRING of encrypted value?

For boolean fields, the unofficial standard is to add the "is_" prefix for clarity:

$table->boolean('is_active')->default(0);
$table->boolean('is_encrypted')->default(1);

From the names of "is_xxxxx", the developer clearly understands the meaning: "is it xxxxx or not". A boolean.

Suffix "xxxxx_at" for Timestamps

Another common unofficial standard, especially in Laravel, is to save not just the value of a Boolean, whether it is active or not, but also the fact of when it was activated.

For clarity, you should NOT name the field like this:

$table->timestamp('activated')->nullable();

A common suffix is "xxxxx_at" at the end:

$table->timestams(); // created_at + updated_at
$table->timestamp('activated_at')->nullable();

In other words, following the same naming conventions, such as created_at and updated_at, will make the code clearer to future readers.

Don't Use Reserved SQL Words

A few times in my career, I've seen column names like order or from. Don't.

If you do something like this:

Then run this SQL query:

select * from role_user order by order

Obviously, you will get an error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order' at line 1"

And, of course, the solution is to use backticks for all the columns. So, this SQL query will run with no errors:

select * from role_user order by `order`

And actually, Laravel always adds backticks for us automatically!

When we run this:

User::orderBy("id")->get();

The actual query is this:

SELECT * FROM `users` ORDER BY `id` asc

But still, just to be on the safe side, I strongly advise avoiding such obviously reserved words.

So, instead of an order field, I would advise something like position, or even adding a prefix like sort_order - example from the krayin/laravel-crm project.

This was a short lesson about how to name tables when using Laravel with Eloquent. But, be very careful when giving non-standard names for tables and DB columns. You might forget to set them somewhere, and other developers will first look at the Laravel naming.