HR Employee Database?

[flower]

Hi Everyone:

I have been asked to create a HR employee database. I need some guidance in creating the database.
The database will keep track of all the details about the employees and the areas that need to be covered are:

1. Personal information and Company Info: Name, Add, Phone Number, Job titles in the company, promotions, salary change, Date of Hire, Date of Re-Hire, Break in employment (date of leaving, date of coming back), Reason for break in employment (FMLA, Military…), etc

2. Benefits Area: This would include what kind of Insurance plan the employee has. For example, Our Company provides 2 kinds of Dental insurance. If they have insurance then whether it is just for the employee, spouse, family. Same thing goes for Mediacal Insurance. This would also include information on Life Insurance, Long Term Disability, etc.

3. Equal Employment Opportunity(EEO): This would include their Job Group, Minority Group, Veteran Status(Special Disabled, Vietnam Era..)

This is some of the information I need to keep track of. I was thinking of creating a tab form with 3 tabs(Personal Info, Company Info, Benefita Area, EEO). But I am a little unsure about the table structure. Can anyone help me with that? Are there any sample databases I can look at to get an idea?

Thanx
ER

 

[mission2java_78]

Look up normalization.

I like your idea of the 3 tabs..generally I use this.
I hope they dont want you to put together a labor collection system with this..that can get a little ugly.

Youll need a table to hold the basic employee information. Make sure you seperate fields with:
LastName, FirstName, MiddleInitial, etc.

You'll need another table which holds Employee->insurance / medical plans (1->many).

You'll need some lookup tables so that the end user doesnt have to type Blue Cross and Blue Shield / HAP / etc. You can then join all these tables.

I'd give ya more but Im currently having a brain fart.

Jon

 

[flower]

Hi Jon:

Thanx for the information. This would definitely help me start.
I would appreciate any further tips.

Thanx
ER

 

[mission2java_78]

Hi Jon:

Thanx for the information. This would definitely help me start.
I would appreciate any further tips.

Thanx
ER

Your job titles such be its own table as well...meaning you could have:

Table JobTitles
--------------------
1 Janitor
2 Head Honcho
3 Manager
4 Scumbag

That way when HR enters a new employee they do not manually type this in and just have the jobtitles in a combo box. So your employees table would have a JobTitleID foreign key.

You should also have a bit datatype (on / off / yes / no) off "Active" if its on the employee is active...if its off he / she has either left or FIRED!...That way you can query your data of all existing employees in the company.

The benefits area would be a seperate table as well. Actually you could seperate this even more..one table could just list all of the benefits. Another table would display the ID of the employee who has the benefit and the ID of the benefit. That way you can also query your tables out using a JOIN.

Hope this helps a bit more,
Jon

 

[Pat Hartman]

I avoid yes/no flags for status. I prefer dates. They give much more information and can be used whereevre the flag would be used. So you would check the termination date for null to determine active employees.

Most HR systems need to keep extensive history. Virtually every change made to an employee record needs to be logged in history. I would keep a main HR table with current job title, status, etc. And every time some field changes, I would create a record in the history file with a comment as to why a change was made and who made it.

 

[flower]

And every time some field changes, I would create a record in the history file with a comment as to why a change was made and who made it.

Thanx for the great tips. This would certainly get me started.
You are right Pat. I will need to keep track of each and every detail about the employee. Their salary change, promotion, etc.
But I din't quite follow how you create a record in the history file when a field changes. Do I need to create a seperate table that will keep track of their history?

Thanx again
Ekta

 

[Pat Hartman]

The history table should duplicate the table it is tracking with a few changes.

1. If the main table has an autonumber primary key, that would need to be defined as long in the history table.
2. Since there will be many rows in the history table relating to a single employee record, the history table needs its own autonumber primary key and the primary key of the original table is used as the foreign key.
3. You need to add a ChangeDt field, a ChangeBy field and a ChangeReason field.