0

I want to create a table for tasks in my database. There are a number of roles accounts can have in my app—admin, operator, user. All these roles can create tasks, and admin and operator can close the task and mark it completed. Both the "task creator" and "task resolver" can add comments while creating/closing the task. I'm confused about how to approach designing the schema for this use case. I have two options in mind:

Option 1

+------------------------------------------------+
|                      tasks                     |
+--------------------+---------------------------+
| id                 | primary key               |
+--------------------+---------------------------+
| status             | text                      |
+--------------------+---------------------------+
| creatorUserId      | refers to id in users     |
+--------------------+---------------------------+
| creatorOperatorId  | refers to id in operators |
+--------------------+---------------------------+
| creatorAdminId     | refers to id in admins    |
+--------------------+---------------------------+
| creatorRole        | user or operator or admin |
+--------------------+---------------------------+
| creatorComment     | text                      |
+--------------------+---------------------------+
| resolverOperatorId | refers to id in operators |
+--------------------+---------------------------+
| resolverAdminId    | refers to id in admins    |
+--------------------+---------------------------+
| resolverRole       | operator or admin         |
+--------------------+---------------------------+
| resolverComment    | text                      |
+--------------------+---------------------------+

Option 2

+----------------------------------------------------------+
|                           tasks                          |
+-----------------+----------------------------------------+
| id              | primary key                            |
+-----------------+----------------------------------------+
| status          | text                                   |
+-----------------+----------------------------------------+
| creatorId       | refers to id in users/operators/admins |
+-----------------+----------------------------------------+
| creatorRole     | user or operator or admin              |
+-----------------+----------------------------------------+
| creatorComment  | text                                   |
+-----------------+----------------------------------------+
| resolverId      | refers to id in operators/admins       |
+-----------------+----------------------------------------+
| resolverRole    | operator or admin                      |
+-----------------+----------------------------------------+
| resolverComment | text                                   |
+-----------------+----------------------------------------+

TLDR: Should I have separate columns for IDs of different roles or would it make my life easier to just have a common creatorId column and use the creatorRole to determine what ID it refers to?

0

1 Answer 1

0

Option 2 is much better because task is common between those users, you can store the extra data of the users in their respective table and easily get with your creatorId. Now it depends if your create multiple views via joining task to the users table by the creatorId or just write a query which gets the data on the runtime.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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