Link to home
Start Free TrialLog in
Avatar of sammySeltzer
sammySeltzerFlag for United States of America

asked on

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.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

I few *quick* thoughts.  Naturally further design work will be needed.

I see at least these tables:

EMPLOYEES -- sing. or plural, depending on your preferences
     [yes, this needs to be in a separate table, even if that table is only populated for request purposes]
    empId int identity PK
    firstname nvarchar(50)
    ..., --I think most of the column lengths need adjusted
    Phone nvarchar(50) --should be shorter

REQUESTS -- sing. or plural, depending on your preferences
    requestId int identity
    empId int FK -> EMPLOYEES
    dateEntered datetime
    dateNeeded datetime
    dateStarted datetime
    status nvarchar(30) --'New', 'Pending Approval', 'Approved', etc.
    remoteAccess char(1) --Remote Access category Radio button choice
    OS char(1) --OPerating System Radio button choice
    PCPreference char(1) --PC Preference Radio button choice
    priority char(1) -- Priority Radio button choice
    justification nvarchar(3000)

[to be cont.]
REQUEST_APPROVALS  [allows for multiple layers of approval]
    ApprovalId int identity PK
    requestId int FK -> REQUESTS
    sequence smallint --1=first level approver, 2=2nd level approver, etc.
    dateEntered datetime
    dateNeeded datetime
    dateStarted datetime
    approver_empId FK -> EMPLOYEES
    required bit --this person MUST approve; if all 0 for a level, ANY approver at that level is good enough
    status tinyint --0=open; 1=approved; 9=rejected
    comments nvarchar(300) -- reason for approval/rejection/other comments;
Or maybe change the table name to:


since approval or rejection are stored in the same table, just with a different status.
Avatar of sammySeltzer


Looks very good!

Thanks a lot Scott.

For some reason, as long as I have been doing the front end app, I still can't wrap my head around db design.

I am guessing this is it?

Can you please explain the reason for EMPLOYEES table even though requestor's information are gobbled from Active Directory which already has employee data?

Oh, one more thing, I don't quite understand this line

required bit --this person MUST approve; if all 0 for a level, ANY approver at that level is good
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a lot for your work. Impressive.
Many thanks
>> required bit --this person MUST approve; if all 0 for a level, ANY approver at that level is good <<

Yeah, that's kinda obscure, but it's my attempt to have max flexibility.

What I'm envisioning are these scenarios:

Some requests may have several levels/sequences of approvals.  I don't want to assume a fixed number of levels.  A table design lets me use unlimited levels.  "sequence" is 1 for the first approval needed, 2 for the next approval needed, etc..

Some requests will have a specific list of specific people.  For example, MgrA must approve, then MgrBigCheese.  Very straightforward:
    seq 1 = MgrA, required = 1
    seq 2 = MgrBigCheese, required = 1
Those are not helped by my table design -- but they're not hurt either.

But say some requests are routine enough that they can be approved by ANY of three people -- MgrB OR MgrC OR MgrD -- then must be approved by MgrBigCheese him/her-self.
    seq 1 = MgrB, required = 0
    seq 1 = MgrC, required = 0
    seq 1 = MgrD, required = 0
    seq 2 = MgrBigCheese, required = 1
That means none of MgrB/C/D is *specifically* required to personally approve, but ANY one of the three must.

Likewise, this:
    seq 1 = MgrB, required = 0
    seq 1 = MgrC, required = 1
    seq 1 = MgrD, required = 0
    seq 2 = MgrBigCheese, required = 1
Would mean that MgrC *specifically* must approve it, AND then either MgrB or MgrD, AND then MgrBig, as always.

In summary:
    For any level/seq that has "required = 1", that specific person MUST approve it before it goes to the next level.
    For any level/seq that has one or more "required = 0" approvals, ANY one of them MUST approve before it moves on.
    [The two are separate.  In the last example above, MgrC alone is not enough for level 1, since there are additional mgrs listed, at least in the scheme I'm describing -- but another bit flag could be used to allow a single approver out of multiple too.]