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:

-------------- ------------- ---------------- -----------------------
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:

-------------- ------------- ---------------- -----------------------
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
  • The original reason I asked this question was to figure out a way to simplify my client-side validation. Right now I'm going with Case 2 above, but I need additional validation to allow the Request to be submitted with "APPROVED_LEVEL2" status for certain users. Otherwise, I'd have to submit with "USER_SUBMIT" then programmatically "re-submit" it with "APPROVED_LEVEL2". – Eric Belair Sep 10 '12 at 13:42

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
    +1, for keeping track of approval history. However, the current model may require changing to separate the application table from the approvals. Who knows, may be there would be level 4 one day. – NoChance Sep 7 '12 at 22:08

Option 2 of course.

Why would you want to store extra garbage data?

It will just confuse things, and adds NO value.

  • (1) adds value if auditing is a requirement, but at least in the question it wasn't mentioned. Also then another field approved_by would be needed. – Benjamin Bannier Sep 7 '12 at 14:14
  • How does logging an action that never happened add auditing value? – Morons Sep 7 '12 at 14:16
  • Hu, where did you read that? The example for (1) does only show what happened, e.g. request 3 hasn't yet been approved by level 3. – Benjamin Bannier Sep 7 '12 at 14:18
  • I read his questions as this "If a Level 3 user submits a request and self-approves it, should I log approvals for Level 1 & 2 Automatically?" – Morons Sep 7 '12 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.