How should I track approval workflow when users at every security level can create a request?

Posted by Eric Belair on Programmers See other posts from Programmers or by Eric Belair
Published on 2012-09-07T14:01:40Z Indexed on 2012/09/07 15:49 UTC
Read the original article Hit count: 226

I am writing a new application that allows users to enter requests.

Once a request is entered, it must follow an approval workflow to be finally approved by a user the highest security level.

So, let's say a user at Security Level 1 enters a request. This request must be approved by his superior - a user at Security Level 2. Once the Security Level 2 user approves it, it must be approved by a user at Security Level 3. Once the Security Level 3 user approves it, it is considered fully approved.

However, users at any of the three Security Levels can enter requests. So, if a Security Level 3 user enters a request, it is automatically considered "fully approved". And, if a Security Level 2 user enters a request, it must only be approved by a Security Level 3 user.

I'm currently storing each approval status in a Database Log Table, like so:

STATUS_ID (PK) REQUEST_ID    STATUS           STATUS_DATE
-------------- ------------- ---------------- -----------------------
1              1             USER_SUBMIT      2012-09-01 00:00:00.000
2              1             APPROVED_LEVEL2  2012-09-01 01:00:00.000
3              1             APPROVED_LEVEL3  2012-09-01 02:00:00.000
4              2             USER_SUBMIT      2012-09-01 02:30:00.000
5              2             APPROVED_LEVEL2  2012-09-01 02:45:00.000

My question is, which is a better design:

  1. Record all three statuses for every request ...or...
  2. Record only the statuses needed according to the Security Level of the user submitting the request

In Case 2, the data might look like this for two requests - one submitted by Security Level 2 User and another submitted by Security Level 3 user:

STATUS_ID (PK) REQUEST_ID    STATUS           STATUS_DATE
-------------- ------------- ---------------- -----------------------
1              3             APPROVED_LEVEL2  2012-09-01 01:00:00.000
2              3             APPROVED_LEVEL3  2012-09-01 02:00:00.000
3              4             APPROVED_LEVEL3  2012-09-01 02:00:00.000

© Programmers or respective owner

Related posts about Workflows

Related posts about data