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

Question

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

Answer 1

I would consider having "auto-approved" tags for the cases where a Level 2 or 3 user creates a request.

My presumption is that you are tracking:

• who creates
• who approves at level 2
• who approves at level 3

So your status could then go through the following:

• User_submit
• Approved_Level2 or Auto_Approved_L2
• Approved_Level3 or Auto_Approved_L3

That would allow you to keep track of cases where things are auto-approved, but still maintain the same / similar code paths for all work tickets. So that should meet your requirements and store meaningful information. Presuming you care about cases between an actual approval and an auto approval.

So I'm voting for a modified version of case 1.

Answer 2

Option 2 of course.

Why would you want to store extra garbage data?

It will just confuse things, and adds NO value.