Composite PK for organization, role, permission
I am trying to implement a DB schema in which I have users that belong to one organization or more, and for each organization they should be able to get one or more roles. For example, user 1 can be regular user for organizaiton 1 and be an admin, manager for organization 2.
This is my schema:
Option 1

My issue is how I would access this data and retrieve permissions for a specific user belonging to a specific organization. As far I understand this is not yet possible with Eloquent since I am using Composite PKs. I guess I would have to create queries that use lots of joins using the Query Builder but I will lose a lot of nice features of Eloquent!
In order to use Eloquent I think I can use the following options but I feel like they are hacks and not good practices!
Option 2

Here I would have a three way pivot table and instead of using Eloquent many-to-many I would create a Model for the OrganizationUserRole and make three belongsTo relations.
OR
Option 3

Here I would just have to create the needed Eloquent models and apply the belongsToMany for organization_user and roles.
Option 2 and 3 still let me use Eloquent, but is this a good way of designing a schema?
Any feedback?
Please or to participate in this conversation.