Mapping strings to relations

Question

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.

Answer 1

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

Answer 2

The tried and good approach, complying to normal forms would be to have task_type and role tables. You of course have a user table and since a user can have many roles and privileges, you will need a user_role and a user_privilege table to handle the many-to-many relations. An easy way to handle the problems is to have some numbers representing some privileges and roles, like 1 for administrator and 2, 3, 5, 7, 11, 13, 17 and so on for other privileges. Having a similar number for a role as a primary key would ease the role matching problem. For example, let's consider the case when you have a privilege with code 7. If you search for roles with the id divisible with this code, then you will get 7 (data_read, for example) and 1 (administrator).

Answer 3

You need for sure a relation table between users and tasks and of course in this relation you have also to flag if user is adminstrator or not. This is the best way for design the structure of your application instead of merging information into a single columns which cause performance/complexity issue. Go ahead with this approach ,your work will benefit from this.