I have three main tables:
users
=====
user_id
user_email
organizations
=============
organization_id
organization_name
organization_phone_number
roles
=====
role_id
role_name
Users and organizations have a many:many relationship: a single user can belong to multiple organizations, and the same organization can have multiple users.
Users and roles sort of also have a many:many relationship: a single user can have multiple roles, and a single role can belong to multiple users. However, roles are organization-specific. Meaning, if User 1 belong to Org 1, they will have 1+ roles within the "domain" of that organization. If they also belong to Org 2, they will have 1+ completely different roles for that organization.
Hence, given the following list of roles: Admin, Dog, Cat, Lion, Bear, Fish, and Frog, you may have: User 1 belongs to Org 1, where he/she is an Admin. But User 1 might also belong to Org 2, where he/she is a Cat, Lion and a Frog.
I'm looking for the right table structure to represent these restrained many:many relationships.
It's not just as simple as adding a users_x_organizations and users_x_roles crosswalk tables, like so:
users_x_organizations
=====================
id
user_id
org_id
users_x_roles
=============
id
user_id
role_id
This isn't sufficient because it doesn't represent the user's list of roles per organization. It would only allow me to represent that User 1 belongs to both Org 1 and Org 2, and that, between all of their membering organizations, they are an Admin, Cat, Lion and a Frog. It doesn't tell me that, for Org 1, they're just an Admin, etc. That's where I'm drawing a mental blank, any ideas?
You basically have two choices. You can separate out the roles to be specific for each organization or you can have common roles across all organizations. For instance, would the "admin" role have the same role_id regardless of organization? Or, would there be a separate "admin" role for each organization.
In the first case (where roles are shared among organizations), your roles table would be users_x_organizations_x_roles, with three foreign keys. Whether you need users_x_organizations becomes a question about requirements. You could have a "role" that says "belongs to". Then the users_x_organizations is really:
select user_id, org_id
from users_x_organizations_x_roles uor
where role_id = 'belongs_to';
Or perhaps having any role would be sufficient:
select distinct user_id, org_id
from users_x_organizations_x_roles uor;
In the second case (where roles are organization-specific), your roles table would be users_x_roles and the question is how to guarantee relational integrity. That is, how to guarantee that the roles for a user match the organizations for the same user.
The general method is to use either a constraint or a trigger. Doing cross-database constraints is either not supported or a bit painful in most databases. So, the integrity could be guaranteed by a trigger. An alternative method (which is what I would do) is to wrap all data modification code in stored procedures and do the validation explicitly in stored procedure code.
Which method is better depends on your application and the interplay between organizations and roles. If most or all roles are shared among organizations, then the first method might be preferred. If roles are highly organization-specific, then the second method might be preferred.