Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I have the following database structure for my application:
users
id | name | email | email_verified_at | password | remember_token
teams
id | owner_id | name | slug | description
team_user
id | team_id | user_id
roles
id | name
I have been able to create relationships between the Team and User models from the following:
Team.php
public function users() {
return $this->belongsToMany(User::class);
}
User.php
public function organisations() {
return $this->belongsToMany(Team::class);
}
A user can be part of multiple teams and should be able to have different roles within each of these teams, is it possible to achieve this by adding a role_id column to my team_user table instead of creating another Pivot table like the following:
team_user_role
id | team_id | user_id | role_id
If so, which Eloquent relationship should be created between the User within the Team and the Role? Could someone explain clearly the correct relationships as I am receiving SQL errors when trying to access the user's role from my Team model:
@foreach ($team->users as $user)
{{ $user->role }}
@endforeach
is it possible to achieve this by adding a role_id column to my team_user table instead of creating another Pivot table like the following
Yes, it is possible.
$user->roles()->where('team_id', $team->id)->first();
But to avoid this many devs do not create Role model, but just add role string to the team_user pivot table.
Also you could do a check if a method requires a certain role match that but also in your app a required team also. Unless all teams can see all methods if their role is correct.
Admittedly RBAC can get a little complex.
Admittedly RBAC can get a little complex.
People make it complex.
@bugsysha Wouldn't having a role string in the team_user pivot table cause problems in the future?
For example what if I need to rename the role from "Client" to "Customer"?
If I skip the Role model, how would I for example get all of users's emails with a role of "Administrator" if there is no Eloquent Model I can use?
I would appreciate if you could clarify some of these points as I want to make sure I don't encounter any maintainability problems in the future.
For example what if I need to rename the role from "Client" to "Customer"?
You create a migration or just run query which will update it.
// query example
UPDATE team_user SET role = 'customer' WHERE role = 'client';
// migration example
DB::table('team_user')->where('role', 'client')->update(['role' => 'customer']);
If I skip the Role model, how would I for example get all of users's emails with a role of "Administrator" if there is no Eloquent Model I can use?
I guess easiest way for you is to do following:
php artisan make:model TeamUser --pivot
// then in php
\App\TeamUser::query()->select('users.email')->join('users', 'users.id', '=', 'team_user.user_id')->where('role', 'administrator')->get()
// same applies if you use \DB facade
Bunch of ways to do it. Point is that you can even create pivot Model for that team_user table.
Which way would you recommend?
I feel like creating a pivot table for the team user's role would be good as then I would have a Model I can work with, I take it the table name would be team_user_role?
Thanks for your help btw!
Please or to participate in this conversation.