I have a scenario which I believe I have come up with a solution - however, I can't be sure it's a manageable one, or even correct. The scenario is as follows.
There are are three tables involved in this:
The requirement is that a user can add a resource for a specific combination of user types and groups. For instance, a given user might upload a ‘technical manual’ (
resources) that is supposed to be only accessible to ‘painters’ and ‘foremen’ (
user_types) in ‘group a’ (
groups). They might then assign the ‘manual’ to only ‘painters’ in ‘group b’, and so on.
My tentative solution to this is to have a many-to-many (M:N) relationship between
user_types using an associative table called
groups_user_types. Then, that associative table has a many-to-many relationship to
resources, using another associative table unfortunately called
groups_user_types_resources. I've also considered having an associative table that connects the three tables, but I'm not a DBA and I don't even know if this is a valid way to do it.
Next problem is seeing if my chosen ORM supports this type of relationship, but that's a question for another Stack Exchange site...
Does this relationship make sense? Is there a solution that doesn't connect an associative table to another table?
If I have not provided enough information, please let me know and I'll explain further.