Search code examples
mysqldatabasedatabase-designrdbms

Mapping strings to relations

I'm creating a system where tasks can be assigned to different users. The problem is that tasks are mapped through a string column called recipient, that in the end maps to a collection of users. The contents of this column could look like this:

  • has:tasks-update,tasks-access - Users that have the tasks-update and tasks-access Permission.
  • role:administrator - Users that have the administrator role.

Right now I'm resolving it problematically. This is somewhat easy when I have to figure out who has access to a specific task, but cumbersome when a user needs to know what tasks are "assigned" to them. Right now I'm resolving each recipient column to see if the user is included, this is unfortunately not very feasible as it comes with a huge performance cost.

I already have indices on the appropriate columns to speed the look-ups up.

A solution to this, was that I would resolve the recipients when the recipient was changed and then place the relationships between users and tasks in an intermediate table. While this lets me quickly look up the tasks a user is assigned to, it also becomes problematic since now I need to keep track of (for example) each time a user has been given the administrator role and now synchronize this to the intermediate table.

I was hoping I could get some insight into solving this issue without sacrificing performance like I am right now, but also not have to synchronize all the time.

Solution

  • Storing a list of anything as a string in a singular column can lead to all sorts of problems down the line

    As you have encountered already.. any relational look-up, insert, update or delete operations on the list will first require some form of parsing of the existing list

    It is worth noting that any indexes on this column will likely NOT be usable by the engine for these tasks, as indexes on string based columns (other than FULL TEXT) are only really useful when searching the start of the string

    For example, 

    SELECT * 
  FROM site_user 
 WHERE recipients LIKE '%tasks-update%'

    Will not be able to use an index on the recipients column

    A suggestion

    I would split out your current lists into new tables, like

    1. role - id, name, …
      • e.g. {3, 'administrator',… }
    2. permission - id, name, …
      • e.g. {5, 'tasks-access',… }
      • e.g. {9, 'tasks-update',… }
    3. site_user - id, name, role_id, …
      • e.g. {7, 'Jeff', 3,… }
    4. site_user_permission - id, site_user_id, permission_id, …
      • e.g. {1, 7, 5,… }
      • e.g. {2, 7, 9,… }

    Where from the example records, 'Jeff' is an 'administrator' and has been assigned the 'tasks-update', and 'tasks-access' permissions

    Lookups should be easily achievable using JOINs, and stay consistent when data is added or removed. Data integrity can be maintained by adding appropriate foreign keys and unique indexes

    N.B. Without specific examples of the operations that are causing you issues, or more details on how you intend to use user roles and permissions, it is difficult to do more than make general suggestions