Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

EventFellows's avatar

Database setup with many-to-many-to-many relationship (users & roles & company accounts)

I am struggeling with the database setup of a membership site - I'd like to hear your idea on how you would approach it.

The core of the challenge is a threefold relation between users - roles and (company) accounts:

Here are the details in words: Any given USER can have one or more ROLES on one or more ACCOUNTS.

You can picture an ACCOUNT as a company account where different people have access to under different roles. A user can also have access to different company accounts under different roles (e.g. John can have role of 'account_owner' on Acme account AND have a 'contributor'-role on AMCE2 account )

To me this seems like a 'many-to-many-to-many' relationship.

Here are the details in code: USERS have personal user accounts (containing a person's login credentials etc.) so there is a users table.

        // User Accounts Table
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name'); // John Doe, Jane Doe, ...
            // user credentials, email, password, ...
            $table->timestamps();
        });

There is a list of given ROLES, so there is a roles table:

        // Roles table
        Schema::create('roles', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name'); // account_owner, contributor, ...
            // permissions, ...
            $table->timestamps();
        });

And lastly I would believe there needs to be an accounts table:

        // Company Accounts Table
        Schema::create('accounts', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name'); // Acme Ltd., Company ABC, ...
            // company information, account type, ...
            $table->timestamps();
        });

So in a typical many-to-many setup i could only connect 2 of these tables. This is where I get stuck on what would be the best way to go about it.

  • just add a 3rd column to the pivot table making it a "threefold pivot table" (user_id, role_id and account_id)?
  • adding 3 pivot tables to connect 2 tables each?

I am curious on what you think!

0 likes
8 replies
martinbean's avatar
martinbean
10 years ago
Best Answer
Level 80

@EventFellows If users can belong to many accounts then you’re right in that this is a many-to-many relationship. Assuming a user can only belong to a company once, then you would put the role in the pivot table:

Schema::create('account_user', function (Blueprint $table) {
    $table->integer('account_id')->unsigned();
    $table->integer('user_id')->unsigned();
    $table->integer('role_id')->unsigned();

    $table->foreign('account_id')->references('id')->on('accounts');
    $table->foreign('user_id')->references('id')->on('users');
    $table->foreign('role_id')->references('id')->on('roles');
});

class User extends Authenticatable
{
    public function accounts()
    {
        return $this->belongsToMany(Account::class)->withPivot('role_id');
    }
}
EventFellows's avatar

@martinbean unfortunately a user can have different roles on the same company, too. For example someone could be a role of 'billing_manager' with rights to edit the billing details AND have a role of 'content_contributor' with rights to e.g. create articles but not the role of 'publisher' with rights to publish existing articles.

But looking at your code this should also work and would just be represented as 2 rows in the pivot table where the role_id is different, right?

lostdreamer_nl's avatar

@EventFellows, right You can use multiple rows on the pivot table. but because the delete (sync / attach / detach) functions dont use the ID of the pivot table (but the junction fields of the 2 tables) you will probably have to override a few methods or do your attaching / detaching manually with DB::table(...)->insert()

peet's avatar

Thank you @EventFellows . I have more or less the same setup, with different names. I made the names match for the purpose of the thread. How do I access the data in the pivot?

I want to make a page that lists each account name and the user's permission role name next to the corresponding account name. For example, for user John Doe: Accounts:

Acme: Admin

Acme2: PowerUser

Acme3: Standard User

Right now, I am able to get the role id by accessing the pivot. eg.

Acme: 1 (where 1 means admin)

But how do I get the Role name, preferably in the same query?

In my Account Controller

 $user = Auth::user();
 $accounts = $user->accounts;
 return view('accounts')->with('accountslist', $accounts);

In my accountslist view

  @if ($accounts->count())
     @foreach($accounts as $account)
  <li>{{$account->name}}: {{$account->pivot->role_id}}</li>
Cronix's avatar

You passed it to the view as $accountslist, not $accounts.

peet's avatar

Thanks for the quick response @Cronix , that was a mistake I made when I changed my variables and view names from what I actually use to 'account' to match the original question. My AccountController actually looks like this:

$user = Auth::user(); $accounts = $user->accounts; return view('accountslist')->with('accounts', $accounts);

and my Accountslist view works : it displays both the account name and account's user's role id. How do I access the role id's name?

Thank you again.

Please or to participate in this conversation.