Advise on database structure (three columns for many-to-many relationship)

I've been in similar situations a few times and have always been able to solve it with scopes.

for instance, using a wildcard subdomain and using a subdomain prefix for each company:

• https://company1.my-awesome-app.com
• https://company2.my-awesome-app.com

When a request comes in, a middleware checks the subdomain and checks if the user has a role for this company.

If not: redirect back to the main domain with an error message.

if the user does have a role, you cache the current company somewhere (could be a runtime config option (config('company.current', $company->id)) or using the session.

You can then put a global scope on your Role model like:

    protected static function boot()
    {
        parent::boot();

        static::addGlobalScope(function (Builder $builder) {
            if(config('company.current'))
                $builder->where('company_id', config('company.current'));
        });
    }

When a user now hits company1.my-awesome-app.com the $user->roles will only return the role for the current company (still in a collection though).

For the partner option, I do not know what it is you are trying to get to, but maybe the Role object could be morphed to a Company or Partner object, so you would have a 'role_user' table with the fields:

• user_id
• role_id
• type_type (App\Company or App\Partner)
• type_id (id of the company / Partner)

Thanks a lot, this is very helpful! Smart to specific make the distinction in a middleware, I was also thinking about having subdomains.

One question though, I think (but please correct me if I am wrong) that in your example a role specifically belongs to a company. But my roles already go cross companies, so the distinction should be on the pivot table, with custom variables, right?

Something like this:

return $this->belongsToMany('App\Role')->wherePivot('company_id', config('company.current'));

Or can I also make a global scope for this? Although this line of code is already small.

PS. Not tried the full solution yet

PPS. I think I am going to solve the Company vs. Partner roles, by having two separately tables for roles (App\OrganizationRole and App\PartnerRole)

You're correct, I read over that part.

In that case something like this could be used:

if (config('company.current')))
    return $this->belongsToMany('App\Role')->wherePivot('company_id', config('company.current'));

    return $this->belongsToMany('App\Role'); 

Only using that where() clause when the current company is set.