Avatar of sammySeltzer
sammySeltzer
Flag 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.
Microsoft SQL Server 2005Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
Scott Pletcher

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.]
Scott Pletcher

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;
Scott Pletcher

Or maybe change the table name to:

REQUEST_TASKFLOWS

since approval or rejection are stored in the same table, just with a different status.
Your help has saved me hundreds of hours of internet surfing.
fblack61
sammySeltzer

ASKER
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
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sammySeltzer

ASKER
Thanks a lot for your work. Impressive.
sammySeltzer

ASKER
Many thanks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Pletcher

>> 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.]