In a previous post, I shared how to create user roles with one to many relationship. Another way to design user roles in your application is to create a many to many relationship. This means many users can have many roles.
It is very similar as the previous design except you won’t have foreign keys in the users or roles table. There will be a pivot table that will have a foreign key for the users and roles table. You also won’t need to have the roles table created prior to the users table. But both users and roles table need to exist before the pivot table is created.
Let’s create the migration file for the roles table.
php artisan make:migration create_roles_table
Edit this file with the following code.
public function up()
{
Schema::create('roles', function(Blueprint $table)
{
$table->id();
$table->string('name', 50)->unique();
});
}
Again, I chose not to have the timestamps columns, but you can if you wish. If you don’t want the timestamps column, you should add some code in your Role model class to let eloquent know. Your tables should look something like below.
Remember, we don’t have foreign keys on either table. We will now create a pivot table. Following naming conventions, it will be called role_user.
php artisan make:migration create_role_user_table
The up() in your migration file will contain the following code that defines the foreign keys for users and roles.
public function up()
{
Schema::create('role_user', function (Blueprint $table) {
$table->id();
$table->foreignId('role_id')
->constrained('roles');
$table->foreignId('user_id')
->constrained('users');
$table->timestamps();
});
}
Your role_user table looks like the following.
As a reminder, by default, Laravel creates INT columns as BIGINT when using id() and foreignId(). You can continue to use INT but you will have to use increments() for the primary key and the “long form” to define foreign keys that uses unsignedInteger() followed by foreign().
Next, create a model class for the role_user table.
php artisan make:model RoleUser
Edit this model class and add the following.
// app/Models/RoleUser.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class RoleUser extends Model
{
use HasFactory;
protected $table = 'role_user';
}
Defining $table is not necessary if you follow naming conventions but I tend to add it anyway.
Now we will add the many to many relationship between users and roles. To do this, add the following methods to the users and roles model classes.
// app/Models/Role.php
public function users()
{
return $this->belongsToMany(User::class, 'role_user', 'role_id', 'user_id');
}
// app/Models/User.php
public function roles()
{
return $this->belongsToMany(Role::class, 'role_user', 'user_id', 'role_id');
}
You can read more about many to many relationships at https://laravel.com/docs/master/eloquent-relationships#many-to-many. Your tables should look something like this.
With this design, users can have more than one role. For example, user1 can have an admin role and staff. User2 can be admin, staff, and manager. And so on.
