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!