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?
