0

I'm in the middle of creating an ASP.NET MVC 5 web app using windows SQL server as my DB setup.

The app has a very simple purpose: Allow a user to submit a form to be signed by multiple people, traveling "up" the chain of command to be approved by each person. After the final approved the form travels back "down" to the requester.

Now I understand of course that within a database context, the form isn't going anywhere at all; variable are changing making the form alternately visible/invisible to different people.

But what I want to know is: what is the best way to implement this? Specifically, the best way to "track" forms that are traveling the chain.

As I have it right now, I have a model of a request form (containing info like date of request, reason for it, etc.), and that's all dandy. But I'm hung up on the BEST WAY to implement the chain. I was thinking of having two extra columns: CurrentApprover and Approved. CurrentApprover is a foreign key representing the primary key of the person that needs to approve your form next, and Approved represents whether the form has been fully approved.

This is simple, and it would work in practice. However it may limit me down the road in manipulating the pipeline or such. Maybe I could have a "manifest" table which contains all active requests and their details? I like this idea better now that I type it out.

Does anyone have a suggestion?

Also, 2 quick questions if someone would be so kind:

1) This app will be processing thousands of forms each year, and though I know tables do fine with millions of entries, I was wondering where there would be any advantage/best practice reason to have different tables for different company divisions (there are 3 distinct divisions, but the forms are the exact same and the process for approval is the exact same).

2) If a form is foreign keyed to its submitter, and the user data table for the submitter contains info that is being passed up with the request form, do I still need to/ should I include those fields explicitly in the request form table, or is it sufficient/better to just query those values when needed. I feel like the latter is better, because a form has a submitter ID (employee ID per-say), and I can just use that to look up the employee's user info when I need it.

Thank you very much!

Improve this question
1
  • 1) Do not make a table per division - this just complicates your code by needing dynamic SQL to choose the right table or by making three copies of every module that interacts with it. A division should be identified by a predicate in a where clause, not a table name - splitting them up will be especially painful when reporting across all three (or more, if you add divisions later). 2) Do not store redundant data, unless you need to track a submitter's information at the time of submission separately from their current information. Even then, you could version it (or use temporal in 2016).
    – Aaron Bertrand
    Commented Nov 13, 2015 at 5:10

1 Answer 1

Reset to default
-1

I am working on a similar process and found this very useful site that proved helpful for my processing issues. This details a request and approval process that can be changed to fit other applications.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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