Mysql – Should I use a three-way associative table

database-designerdinnodbMySQL

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: groups, user_types, and resources.

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 groups and 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.

Best Answer

If I have followed correctly, the two options is whether to have one or two intermediate table for the relationship between groups, user_types and resources. The answer, from a purely design point of view depends on whether that relationship is a strong or weak one. In other words, if groups_user_types (lets call it authorizations) is something that should exist if one of the other entities do not, and if an authorization is shared between resources or not. Let's see an example:

"manual1" can be accessed by:

  • painters in group a
  • foremen in group a
  • painters in group b

manual2 can be accessed by:

  • painters in group a

  • foremen in group a

    resources
---------
id|name
 1|manual1
 2|manual2

user_types
----------
id|name
 1|painters
 2|foremen

groups
------
id|name
 1|a
 2|b

groups_user_types
-----------------
id|group_id|user_type_id
 1|       1|           1
 2|       1|           2
 3|       2|           1

groups_user_types_resources
---------------------------
resource_id|groups_user_type_id
          1|                  1
          1|                  2
          1|                  3
          2|                  1
          2|                  2

You know your application better than I do, but I do not see groups_user_types as a strong entity (no attributes, no reasons to exist if groups_user_types_resources disappears). Maybe I am wrong, and permissions is a big part of our application, and you will give each resource a named authorisation that can be shared between resources. But if not, you can change the last two tables into:

    groups_user_types_resources
    ---------------------------
    resource_id|group_id|user_type_id
              1|       1|           1
              1|       1|           2 
              1|       2|           1
              2|       1|           1
              2|       1|           2

Again, this depends on how strong groups_user_types is and if those permissions are shared between resources or not. Are the permissions for resource 1 the same entity that the same permissions for resource 1. Will you have lots of resources with the exact same permissions? Are there permissions that won't be allowed? Only you can answer that.

Related Question