2

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.

1

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.

  • Group A might only have painters while group B might have both painters and foremen - you can set which user_types can be used by each group. Then, when you create a resource, you can specify which user_types in each group can use it. So I think I need the two tables. Does that make any sense? – Matt Aikens Sep 15 '14 at 16:15
1

I think that your question is really about relationships of degree 3, sometimes called "ternary relationships". There is a diagram here that illustrates a ternary relationship between course, subject, and teacher.

Whether such a three way relationship exists or not in your case depends on your subject matter, and your analysis of it.

A three way association table allows you to express a three way relationship. Such a thing can certainly be done. The fact table in a star schema often serves as the hub in a multidimensional relationship. Whether this meets your design goals or not depends on what you are trying to do.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.