User Roles Many to Many

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.