Human Resources Database Design 4

[ahmun]

Hi all,

I would like some advice on designing a Database for the HR Department. This database would primarily track employee info and their salary info.

A little history:

Recently I've been tasked with developing a database system to track info in the Human Resources Department of my company. Apparently, I wow'ed the leaders of the company by putting together an ad-hoc database that was able to make an individualized report of each employee's compensation analysis. I was able to put together all the datapoints that were formally stored in seperate Excel spreadsheets, but in haste, I crammed all data into one table, without any normalization.

Now, they want this database to continue to put out that Employee Compensation Analysis for each year in the years to come. They also want to be able to get simple mass-mailings address listings for mail-merge or label printing options. I can only imagine the potential of where this can go if it gets set up the right way.

I was wondering if any of you know of where I can find resources to read up on how to organize the data structure. I'm dealing with static data (Employee info) and time-specific data (yearly salary info)

I don't know what else I might run into, and until I hear from the HR director, I won't know, so I'm doing some preemtive research to prepare myself.

Specifics:
[ul][li]What possible types of tables would I be setting up (in general)[/li]
[li]What are some naming conventions (what would I name the database and its entities... I'm kinda stuck in a paradigm that if I name my structures well, I design well.. sometimes I get brain-blocked with just coming up with a naming convention)[/li]
[li]Are there any canned structures/flowcharts out there I can use?[/li][/ul]

Earnie Eng

 

[SQLSister]

What database are you using?

First things first, HR information is extremely sensitive. There are legal issues concerning who has and has not the right to view this information. You need to talk to HR professionals and search for the legal requirements of the system. Then you need to design your security based on the legalities of the situation.

One thing I would avoid is using the SSN as the key field even though it by defintion must be unique. This is not data you want scattered through lots of tables that many people have access to. In fact I have seen it written about that some people choose not to store such information in the same table with the employee name, so that anyone casually perusing the tables won't have the immediate connection to the SSN (or salary) of the user.

I don't know what front end you intend to use, but if you are not using Access, I would suggest you look up encyption algorithms and encryt the information that is sensitve so that the database admin can't read it and it can only be found by a valid user from the user interface.

More than likely you will need to have an employee table, a salary table, a table for supervisors (Or you can do this in a hierarchial fashion in the employee table, but be careful with this type of design as queries can be inefficent if you do not design properly, read extensively on this topic before you attempt to use it.) Depending on the other requiremeents you might have one for performance appraisal information and one for disciplinary actions, etc. Depends on how much your company wants or needs.

You might have lookup tables for things like job titles, or benefits or departments. Anything you might want to use a pull down menu for data entry, create a lookup table for. THen if the data changes, you change it it one place and all the places using it will change. DEpending on your company, you may need locations as a lookup as well.

Well there's probably lots more to consider, but rememebr that data security is the number one issue in an HR database. Absolute Top priority no matter what. Keep that in mind and you won't go too far wrong.


Questions about posting. See faq183-874

 

[johnherman]

You specify that the employee data is static, but you want to use addresses for mailings. As we know, people move, so addresses change. Do you have any interest in a person's past address? If not, and you don't have to track that data, fine.

You will have a base (parent) table of Employee with attributes such as Employee number, SSN (perhaps), work phone, home phone, work location, home address, emergency contact, etc.

You will also have a subordinate (child) table called Employee Position which will have the Employee Number and Effective Date as a concatenated primary key. Other attributes include Job Title, Salary, Supervisor (perhaps), etc.

Room for growth might be in tracking Employee Skills or Education, both child tables to the base table of Employee.

Let us know if you need more guidance.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[wgcs]

What comes to my mind regarding salaries is this:

You want a time-sensitive data for salaries so that you can analyize last year, or the year before. But, what is a "year"? what time period does it cover, when someone gets a raise in the middle of the fiscal year?

So, The salary table must be organized based on the date that raises (or pay cuts) are enacted, something like:

Salary Table
------------
Foreign Key: EmployeeID
Date: DateEffected: The date this pay rate went into effect
Number: HourlyWage


This way, you can take any moment in time and gather all the latest Salary records for each employee at that date.

Note, too, when an employee is hired (or fired), you add a new salary record. If "fired", the new record could have HourlyWage=0.

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[willir]

Earnie

Here is a kcik at the can. I use Access which has some security issues than Oracle or MS*SQL, but I address this security issue in the design...

Design

tblEmployeeMaster
EmpID - primary key
EmpLN - last name, text, 25 char
EmpFN - first name + initial, 15 char
SenorityDate - date
ManagerID - foreign key to tblEmployeeMaster
Comments - memo field

Discussion:
General information for employee, no confidential info


tblAddress
AddressID - primary key
EmpID - foreign key to employee master file
Primary - yes / no
AddrType - text - mailing, home, parents, actual, summer, temp
AddrLine1 - text, 255 char
AddrLine2
City
StateProv
ZipPostCode = zip or postal code
ActiveTil - date
Comments - memo field

Dicussion:
Most employees will have one address, but you may want to capture the parent address, etc. For example, a retired or semi-retired employee may have more than one address. The ActiveTil is a date field for when the address is good for (example, summer address).

The "primary" boolean field is to determine what address to be used as the mailing address. Perhaps redundent for AddrType.


tblPhone
PhoneID - primary key
EmpID - foreign key to employee master file
Primary - yes / no
PhoneType - text, work, home, cell, emergency contact, pager, fax, etc
PhoneNo - text, 255 char
PIN
Comment - memo field

Discussion:
Like the address table, you want to be able to accommodate multiple phone numbers including the emergency number. The comment field can be used to document details such as the name of the emergency contact.

You can also use the Phone table to capture the eMail address(es), or create another table for this task. Not really much different from my perspective.

tblEmpConfidential
EmpID - primary key, same as from tblEmployeeMaster
ConfidentialPhone - confidential / unlisted phone number
CurrentPay - currency
PayType - hourly, salary
StartDate - date
EndDate - date
OTElegible - yes / no
BenefitCode - text, foreign key to Benefit table
Comment - memo field
+ over specific info for confidential info

Discussion:
Would include confidential material privy to management.


tblEmpPrivate
EmpID - primary key, same as from tblEmployeeMaster
CurrentPay - currency
TerminationCode - text

Discussion:
Would include highly confidential material privy to seniro and HR management.


tblCPayHistory
CPayHistoryID - primary key
EmpID - foreign key to tblEmpPrivate table
EffectiveDate - Date
AdjustRate - Decimal, percentage of adjustment
AdjustType - bonus, increase,
Adjustment - currency
PayType - text, hourly, salary
Comment - memo field

Discussion:
Tracks changes in pay history. Bouns or increase. Note that you can track when an employee was switched from hourly to salaried too.

tblCReview
ReviewID - primary key
EmpID - foreign key to tblEmpPrivate table
ReviewDate - date
ReviewReason - text, performance review, discipline, etc
ReviewNotes - memo field
ReviewAction - text, outcome of review - bonus, suspension
CPayHistoryID - foreign key to tblCPayHistory table
Comment - memo
ReviewLink - HyperLink to external documentation

Discussion:
Track reviews - performance or otherwise. Link to pay history for easy reference. External hyperlink for notes or reprimand, etc.


tblCAbsence
AbsenceID - primary key
EmpID - foreign key to tblEmpConfidential table
Authorized - yes / no
AbsType - reason for absence - vacation, suspension, sick
AbsDate - date of absence
AbsHours - hours of work missed
AbsIncident - interger
Comment - memo

Discussion:
Absence tracked by exception. Track hours to track partial days. Incident number is to track reportable offenses - for example, 3 day suspension but only one day would have a reportable offense of "1"; the other recorded dates would have "0".


tblCEmpHistory
EmpHistoryID - primary key
EmpID - foreign key to tblEmpPrivate table
EmpHistDate - date
EmpHistAction - text, type of action - promotion, sick leave
EmpHistDetail - text, 255 char
Comment - memo

Discussion:
Track details on employee in addition to reviews and pay history. For example, promotions, extended sick leave, etc.


tblEmpDocument
EmpDocumentID - primary key
EmpID - foreign key to tblEmpConfidential table
EmpDocLink - hyperlink to employee document
Comment - memo

Discussion:
A collection of linked documents. For example, scanned of signed policy statements, letters of reprimand, letters of acknowledgement, letter of offer. etc.


tblBenefit
BenefitCode - primary key
Vender - text
VenderAddress - numeric, foreign key to address table
EffectiveDate - date
GeneralCoverage - text, family, single, smoker, non-smoker
Comment - memo

Discussion:
Address found on address table. Effective date would be the contract expiration date. Companies do change venders. General coverage would be the overall package description.


tblBenefitDetail
BenefitDetailID - primary key
BenefitCode - foreign key to tblBenefit
CoverageCode - text
LimitAmount - currency
LimitPercet - decimal,
CoverageFrequency - interger (months)
Comment - memo

Discussion:
Details on benefit coverage. Child dental, family health, spouse optical, family support, etc. Limits would refer maximum dollar / euro amount, or maximum percentage amount. Frequency would refer to 6 months, 12 months allowed between occurences.

Beneift transactions would be managed by the vender and not necessary for this design.


tblTask
TaskID - primary key
TaskDesc - text
TaskType - text, critical, production
ReviewDate - date

Discussion:
For the purposes of cross training, responsibilities, etc, this table lists tasks critical to the business. The TaskType would categorize critical processes, production, ISO, etc. Review date would capture last time record was reviewed / updated to ensure data is current.


tblEmployeeTasks
EmpTaskID - primary key
EmpID - foreign key to tblEmpConfidential table
TaskID - foreign key to Tasks
EmpTaskType - owner, cross training, lead, manager, do-er
ReviewDate - date
Comment - memo

Discussion:
Many-to-many joiner or profile table on employee and tasks. This would allow HR to see who is trained to fill-in for sick employee, what critical tasks need to be covered during vacation or termination. After a termination, especially with cut backs, business critical processes may have no owner unless this type of work is documented. I am not sure if you wan to make this table "public" - link to employee master table or "confidential" - link to confidential table.


Security:
Use a front end / back end database with Access (group)login security. The back end should have three databases
- "Public" database can be in a shared location, and the contact information can be used by other Access databases. Would include Employee Master table, address and phone tables. It would allow all user to see who is the manager to the mployee too. The "public" area would include the Benefit tables so employees could see their benefits. I am not sure about the Tasks and Employee Tasks table -- perhaps move these to "production".

- "Production" confidential database would contain info required for production. In this case, tasks and the employee x tasks so management could call the an employee to cover for another employee off sick. It would also allow the creation of letters of reprimand by management. This folder would be more secure by the use of the operating system security - Active Directory, NT or Novell and would include tables... tblEmpConfidential, Task and Employee Tasks, Employee Documents and Absences.

- "Private" database would be for pay, reviews, pay and employee history. This database would be in a more restrictive directory, and would include tblEmpPrivate, tblCReview, tblCPayHistory and tblCEmpHistory.


Presentation
I feel a main form build from tmpEmpMaster table with a multi-tab form with embedded subforms.

The main form would look up the EmpID for tmpConfidential and tblEmpPrivate. If found, then the user has security, and linked tables can be accessible.

The either the tmpConfidential.EmpID and / or tblEmpPrivate.EmpID is not found, then a) the linked tables will not be accessible, b) you can use code to hide subform and pages that the end user is restricted from viewing.

The general public would see two or three tabs. Management would see a few more tables, and finally, HR and senior management would see all the tabs and subforms.

By splitting the data table into three, you can use both Access security and the security of the server / network operating system.


closing
I have not included some things such as training. But armed with the posted info, I am sure you can work these details out. Also, you may differ in opinion in what should be accessible. by all means....

I did not spend time on discussing Access security, but you could use Access security to restrict managers to viewing conifendtial info on their own employees.

Oh yea, I have not tested this design but from experience, I am confident it would work as expected.

Richard

 

[RoyVidar]

As indicated by Willirs reply, creating a database which is flexible and scalable enough to both solve the current challenge and meet future requirements, and meet the requirements relating to security and legal issues, might become quite a task...

Have you considered looking for commersial packages/off the shelf products? I would be surprised if there weren't any such available that covered the requirements (well, depending on price, I'd quess)

Take a look at your requirements, estimate what time you need to meet them (AND multiply with at least a factor of 3;-)), and compare with the cost of bying something...

Just wondering - using some other database than Access for backend, can't you use views to limit what information are available to different users/groups? I remember having access to a personell system on DB2, where the information was limited by views, could only see non sensitive information. Isn't the same available in SQL-Server (MSDE), MySql...?

Roy-Vidar

 

[willir]

As I understand it

The better HR systems are hugely expensive. This is fine for large orgnizations, but for organizations with less than 500 to 200 employees, there are very few products that I am aware of.

The HR systems I have seen for 50 to 400 employees were written in, suprize, surpize, Access.

Richard

 

[ESquared]

For what it's worth, I think sticking prefixes on tables such as 'tblEmployees' is unwieldy and just makes for more typing. The dba knows what his tables are and doesn't need hints to get it straight. When it comes to query writing time, those extra characters become a pain.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[willir]

ESquared

Your point is noted regarding long names for tables and fields.

Many books on Access and such present referenced standards. Moreover, a lot of the Access forums follow the standards.

That being said, the name-thing is not really important in a local setting. If you want to follow standards, or your "shop" has adopted standards - fine; if you decide not to use standards, or your own standards - fine too.

I have run across a lot of applications where field and table names were all numbers - complicated as heck to follow, but I imagine easy to type. On the otherhand, 'tblEmployees' or 'EmployeeTbl' tells everyone that this is a table for employees, but takes a more effort when typing.

I guess one more point is that when posting to the "public" or unknown person of unknown skill, it is important to communicate well enough for the target audiance to understand.

...Moving on
However, what is important is not the name of the tables and fields, but how the data is captured, and how the tables are related.

So hopefully, you will forgive my verbosness as a trade-off for clarity.

Richard

BTW - I have always liked your handle - pretty cool.

 

[ESquared]

I hope you didn't take my post as criticism of your table names. Just understand that on a daily basis when doing ad hoc queries I have to type stuff like the following:

<begin rant>

...
FROM tinOrderStatus S
   INNER JOIN tblHazardShop H ON S.frnkeyHazardShopCode = H.keyHazardShopCode
   INNER JOIN tlkStatus T ON S.frnkeyStatusCode = T.keyStatusCode

which just about drives me BONKERS, because even though I type like lightning it still slows me down. Aliases are *required* to do anything.

Whyever does a field have to have both key AND Code? And having to use frnkey for the field name in one table and key for another is frustrating. I don't need little reminders every day, day after day, as to how the fields are related and gee, which one is the primary key and which one is the foreign key because I forgot my database structure over the weekend!?!?! And gee it's so nice to have the reminder that tblHazardShop is a standard table and tinOrderStatus is an 'intersect' table, I can just never keep the purpose of those two tables straight in my feeble little brain, you know what I am saying?

Look how much cleaner the following is:

FROM OrderStatus S
   INNER JOIN Shops H ON S.ShopID = H.ShopID
   INNER JOIN Statuses T ON S.StatusID = T.StatusID

Revolutionary! ShopID = ShopID! Nope, too simple. That's not allowed, see, there is no documentation on the database and no plans to make any, so some future new-hire *might* get confused and we must prevent this very very carefully.

<end rant>

Sorry, thanks for letting me vent.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[willir]

Esquared

I agree with the you on the those dasteredly long SELECT statements.

...which is why I say a good design is so important. Provided the objectives are well defined, a good design, hopefully, should make it possible for the information to flow from the tables to the form or report without too much effort. A good design should also help one use a more modular approach.

I have seen the bad designs, the awful and long SELECT statments, the hard coding, the inflexibility.

...and ranting is good. Your point was worth posting.

 

[ccma]

Hi~
Ahmun thanks for posting this question... I'm also in the process of building a database for our Human Resource department.
I've had experience with access for about a year now and am definetly concerned about security within this new database.
Everything that I have seen requires a lot of code -- which I am just learning.

Regarding Willir naming convention -- tblEmployee - is what I use and it makes it a lot easier in the long run for me or anyone else to figure out where information is coming from -- even though it does take a long time at the time you are creating the database but later down the road it will save time.
Thanks to all who have posted --- everyone has led me in the right direction.
I have given SQLSister and Willir stars for their posts.

 

[ahmun]

Discussion about the HR database at my company have occurred and here's how things are panning out.

but first... thanks to all for taking the time to respond. I've got a lot of good info!

Here's another leg to the first story:
Our current HR and Payroll Databases (both combined in one) are outsourced and in a JDE (JD Edwards) format. Long ago, developers created a GUI in a text-based terminal program that looks much like Paradox before the days of Windows.

As soon as I offered my skills to "re-develop" the database, I got a lot of thumbs up, but as I gave this project thought, you guys are right, too... it is quite a task.

Right now I'm exploring ways to get ODBC Access to the JDEdwards database (perhaps on a read-only basis) since most of the key employees are already proficient at data entry.

The real solution needed by our company is more customized reporting, and it costs too much to contract out the host of our database to write all the little reports and permutations of, that our employees require.

Since Access comes standard with Office XP, it would make sense to create a front end in Access (most cost effective) and then use its security to limit the access of it to the right eyes.

I don't know if anybody has any thoughts on that... I'm now shifting my research over to how to get ODBC Access, among one of the hurdles, to get permission from the host of our database...


Earnie Eng

 

[willir]

Ernie
ODBC is the way to go. Make sure you only gets read-only access since any updates should be via the formal GUI interface. Have the system prompt the end-user for the ODBC password to address any security concerns.

Access will support ODBC, and if done correctly and the database schema is designed well (I am sure it is), will provide great reports. In a past-life, we gave up on other approaches including Cryst@l Rep0rts in favour of Access.

Thanks for the stars folks.

Richard

 

[ahmun]

Richard (Willir)

Crystal Reports was one thing we considered, but I was able to convince them not to use it for the reason of economy as I mentioned in my previous post.

But just curious. Why did you settle on Access as opposed to other reporting programs?


Earnie Eng

 

[ESquared]

I am about ready to give up on Crystal Reports as well, because while there are advantages and disadvantages of each, Access is more straightforward and allows more exact placement and ability to design custom results.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[willir]

Good question on Crystal...
- Already had Access as part of the standard desktop
- We have several Access gerus on site
- We did purchase a five user license for CR, but no one took any interest in it
- CR has a learning curve, and end users like to use what is familiar with them
- The CR drivers on the standard desktop had some conflicts -- a while back, but I seem to recall it uses the Borland engine which blew up other applications

Having said all this, I know CR can do a lot, and has been used effectively i applications -- just "we" were too busy to invest the time.

Richard

 

[ESquared]

Well, specifically, I wanted my crosstab report to have custom total rows, and could not find a way to do it. I never got a response in the Crystal Reports forum on this question.

Add a calculated total/summary/something row to a Cross-Tab

I could always make another query underneath it, but then is the task of aligning things so they look right.

And I really hate the way you have to lock things so they don't move around and resize by themselves. Then, if you want to move something, you have to unlock it, move it, re-lock it. Sigh.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[ceh4702]

Look out for GRPA legislation requirements concerning persons benefit and treatment information.

If you do not like my post feel free to point out your opinion or my errors.