Database Design – Handling Multiple Associative Tables

You have a many-many (aka link or junction) table between the 2

EmployeeCompany

Columns:

• EmployeeID
• CompanyID

Keys:

• Primary key is (EmployeeID, CompanyID)
• Add a unique index (CompanyID, EmployeeID)
• EmployeeID has FK to Employeetable
• CompanyID has FK to Company table

Do not store a CSV in a column: this is bad practice, can't enforce data integrity, can't search it efficiently, has no meaning etc

Note: A column called "ID" is very ambiguous so this is one case where you prefix with the table name

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.