Database schema for passing requests to role based users I am working on a Database schema where the scenario should be:


There are role based Users; Role 1, Role 2, Role 3 and Role 4
There is a Request model (or class, or table)
User with role Role 1 will create a new Request
This Request needs to be executed by a single User from each Role. Execution means passing on a Request from one User to an other and entering comments.
Role 1 will decide which User from each Role will execute the Request
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 particular Role to execute a Request 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.
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.

DROP TABLE IF EXISTS workflow;
DROP TABLE IF EXISTS assignment;
DROP TABLE IF EXISTS request;
DROP TABLE IF EXISTS my_user;
DROP TABLE IF EXISTS role;


Remove the tables if they exist.

CREATE TABLE role  -- caution, may be a reserved word in some RDBMSs.
(
  role_id SERIAL CONSTRAINT role_pk PRIMARY KEY,
  role_name VARCHAR(10) NOT NULL,
  role_description VARCHAR(100) NOT NULL
);

CREATE TABLE my_user  -- user is a reserved word in PostgreSQL.
(
  user_id SERIAL  CONSTRAINT user_pk PRIMARY KEY NOT NULL, 
  user_name VARCHAR(25) CONSTRAINT user_name_nn NOT NULL,
  user_role INT,                   -- NOT NULL not necessary due to FK constraint 
  CONSTRAINT user_role_fk FOREIGN KEY (user_role)
      REFERENCES role (role_id) ON UPDATE CASCADE
);


CREATE TABLE request
(
  request_id SERIAL CONSTRAINT request_pk PRIMARY KEY, 
  request_name VARCHAR(20) CONSTRAINT rq_name_nn NOT NULL, 
  request_user_id INT CONSTRAINT rq_user_nn NOT NULL,
  CONSTRAINT req_user_fk FOREIGN KEY (request_user_id)
      REFERENCES my_user (user_id) ON UPDATE CASCADE
);


CREATE TABLE assignment
(
  assignment_id SERIAL CONSTRAINT assignment_pk PRIMARY KEY,
  assignment_req_id INT CONSTRAINT assgt_req_id_nn NOT NULL,
  assignment_role INT CONSTRAINT assgt_role_id_nn NOT NULL,
  CONSTRAINT asst_req_fk FOREIGN KEY (assignment_req_id)
      REFERENCES request (request_id) ON UPDATE CASCADE 
);


CREATE TABLE workflow
(
  workflow_id SERIAL CONSTRAINT workflow_id PRIMARY KEY,
  workflow_req_id INT CONSTRAINT wkflw_req_id_nn NOT NULL,
  workflow_user_id INT CONSTRAINT wkflw_user_id_nn NOT NULL,
  CONSTRAINT wkflw_req_fk FOREIGN KEY (workflow_req_id)
     REFERENCES request (request_id) ON UPDATE CASCADE,
  CONSTRAINT wkflw_user_fk FOREIGN KEY (workflow_user_id)
     REFERENCES my_user (user_id) ON UPDATE CASCADE
);