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
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.
1
Option 2 of course.
Why would you want to store extra garbage data?
It will just confuse things, and adds NO value.
4