Constraint Violating Yak Guru
Posted - 06/29/2012 : 15:50:32
| Greetings Experts,
This is a bit long. It is for a reason. Please be patient with me 'cus I need your help and expertise.
I have a routing project with 4 sections.
The first section has employee info such as first name, last name, email address, title, dept, office#, address, city, state, zip.
All these are prepopulated from Active Directory. This works fine.
The second section deals with what request the emp is trying to make. Such requests include, VPN Access, Operating System, Desktop vs Laptop preference, whether the request is urgent or routine in terms of priority.
These fall into 4 cateogories. VPN Access falls under Remote Access category. Operating System falls under OS (whether XP, Vista, win7, earlier versions not support) Whether desktop or laptop request falls under PC preference.
Then there is the Priority Category with Urgent Request or Routine Request radio button options.
Section 3 is simply Supervisor Approvals. The names are dropdowns dynamically populated from Active Directory.
Finally, Last person to approve or reject request is the Department Head. Up to 4 people play this role.
Their names are also dynamically populated into a dropdown from Active Directory.
Now, I do know that before writing a code, db design must done first.
However, no real code is written other than just screen design.
Here is where I am trying to be absolutely sure that I do it right before proceeding.
My thinking so far is that I have just 3 tables.
All the stuff from Active Directory and others are inserted into one table called main.
The way it is supposed to work is that a requestor completes filling out his/her name.
Chooses his/her request, be it vpn connectivity or new pc or os; then select priority.
Finally, chooses his/her direct supervisor from the Approver dropdownlist and clicks "Send Request"
When his/her supervisor receives an email about this request, there is a link with an id for this particular request.
When the supervisor clicks the link, the section that is already completed by requestor will be grayed out or disabled so they can't be modified.
This information I just provided is ONLy relevant for db design purposes as I will handle the graying out and disabling of requestor's content programmatically.
I am only providing it if it helps to add some fieldname if needed to determine filled section.
This table has following attributes:
empId int PK identity
Justification nvarchar(3000) //What is justification for making request?
//Remote Access category Radio button choice
//OPerating System Radio button choice
//PC Preference Radio button choice
//Priority Radio button choice
//Request Approval //fullname
//Department Head Final Approval //fullname
//Approved? I not sure what to do here
//Rejected? This goes back to requestor but I am not sure how to design this
ApprovalId int PK identity
EmpId int FK (from Request table)
RejectId int PK identity
EmpId int FK (from Request table)
Reason nvarchar(255) //Reason it was rejected. Need to route it back to requestor
I am sure I am missing some pieces.
Can you please help me look over this?
Thanks a lot and sorry for long thread.
Aged Yak Warrior
Posted - 07/01/2012 : 23:13:08
| I'd go something like this:
Employee (from AD)
(as you have it minus the last 2. Also only populated once/regularly, not once per request)
- Approver/Manager should be here too.
- Request Type
- Priority (FK to option table)
- OS (FK to O/S option table)
- PC category (FK)
- ApproverRequested FK to another employee ID(not sure about this one - why do you pick your own approver?)
- RejectedBy (FK to employeeID - again, not sure if this is how it works or you just have space for text)
- ApprovedBy (FK to employeeID - again, not sure if this is how it works or you just have space for text)
But it's hard to say - it sounds like there are some funny practices in your process flow that I don't understand. Anyway, have a look at the above - it might make you look at it in a different light.