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
