DB Design - Verification - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

DB Design - Verification

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2012-06-29 : 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:

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.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-01 : 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
- EmployeeID
- Justification
- DateNeeded
- 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?)

Rejected
- ID
- RequestID
- Reason
- DateTime
- RejectedBy (FK to employeeID - again, not sure if this is how it works or you just have space for text)

Approved
- ID
- RequestID
- DateTime
- 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources