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: 316
        
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:
- Record all three statuses for every request ...or...
- 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