I need some input on my current db design plan

Greetings mates,


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:

Tablename: Request
empId int PK identity
firstname nvarchar(50)
lastname nvarchar(50)
title nvarchar(50)
dept nvarchar(50)
division nvarchar(50)
streetAddress nvarchar(50)
City nvarchar(50)
State nvarchar(50)
Zip nvarchar(50)
Email nvarchar(50)
Phone nvarchar(50)
requestDate DateTime
Justification nvarchar(3000) //What is justification for making request?


//Remote Access category Radio button choice
RemoteAccess nvarchar(50)

//OPerating System Radio button choice
OS nvarchar(50)

//PC Preference Radio button choice
PCPreference nvarchar(50)

//Priority Radio button choice
priority nvarchar(50)

//Request Approval //fullname
Approvername nvarchar(50)

//Department Head Final Approval //fullname
depthead nvarchar(50)

//Approved?  I not sure what to do here

//Rejected? This goes back to requestor but I am not sure how to design this

Table: Approval
ApprovalId int PK identity
EmpId int FK (from Request table)
ApprovedBy nvarchar(50)
DateNeeded dateTime
DateApproved datetime

Table: Rejected
RejectId int PK identity
EmpId int FK (from Request table)
RejectedBy nvarchar(50)
DateRejected DateTime
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.