I am working on a Database schema where the scenario should be:
- There are role based
User
s;Role 1
,Role 2
,Role 3
andRole 4
- There is a
Request
model (or class, or table) - User with role
Role 1
will create a newRequest
- This
Request
needs to be executed by a singleUser
from eachRole
. Execution means passing on aRequest
from oneUser
to an other and entering comments. Role 1
will decide whichUser
from eachRole
will execute theRequest
- The workflow of execution will be predefined, for now suppose
Role 1
>Role 2
>Role 3
>Role 4
and then again >Role 1
. The workflow will be cyclic. - But a
User
associated with a particularRole
to execute aRequest
may change in an other cycle - I need to track the history with who executed a
Request
with what comments - Workflow should be changeable
I don't know whether I am clearly able to explain my requirement or not. But will request you to try to understand and/or discuss this question with me and help creating me this schema.
The current Db schema which I am thinking of, but is not very useful and scalable:
Request table
- id
- name
- some_other_stuff
- currently_assigned_to
Assignment table
- id
- request_id
- role_1
- role_2
- role_3
- role_4
Workflow table
- id
- request_id
- executed_by
- comment
The Assignment
table will handle which User
has to execute the Request
based on which Role
they are assigned to. Role 1
User
will execute and the Request
will be passed to Role 2
User
and further on.
The Workflow
table will save the history.
Your help is really appreciated.
Best Answer
OK - here's my first pass at answering your question.
If a user has role1 in a given assigment and can have another role in a different assigment, then my schema will have to change. If necessary, this can be looked at. I think that some of your requirements will require more than SQL to fulfill - you will probably have to start looking at triggers.
You haven't mentioned your RDBMS - my schema is for PostgreSQL. For example MySQL doesn't have the capabilities of PostgreSQL, but you can implement many things triggers and stored procedures. I would strongly recommend PostgreSQL over MySQL if you're going for an Open Source database. Firebird is also a far more capable RDBMS than MySQL. CHECK CONSTRAINTs, REGEXes, CTEs and analytic functions - not implemented (or very poorly so - REGEXes) in MySQL, present in the others.
There are a couple of extra tables in my schema - let me know if this corresponds to your needs. I'm assuming that a request will be broken into assigments - no need to have 4 roles per assignment - the information on who performed each assignment will be in the workflow tables
You could eliminate the workflow table by putting a user_id there and a boolean for completed - maybe simpler? Though having a small assignment table and moving completed assignments to the workflow table might have favourable performance impact.
Remove the tables if they exist.