Keeping Historic Data - An HR management system (1 Viewer)

[MsLady]

I am trying to design a database for an HR management system.
I am not very clear on how to handle historic employee data in this database and will need some help and advice please. Any comment you can provide, i'd really appreciate it.

We will like to have record of historic data and the date of change:
Employee address change
Employee name change
Salary change
Position/Title change
Office location change
Number of dependents change
Bonus received year-to-year

E.g. if an employee name changes, we should still have to have a record of what the old name was.
Scenario:
Jacqueline Peters got married 2years after her employment date and now goes by Jackie Harrison (the changed was made on the system), she leaves the firm 10years later. Many years later, someone calls about a Jacqueline Peters that worked here, we have no way of knowing who that is.
Now, we would like to the salary she was earning 5years after she started here though her endsalary has changed 10years later she got a big promotion (salary change) and changed department, we have no way of knowing what her salary at a particular time was and what her old title was.

What's the best way to design this database to handle historic data? Have you worked on something similar? How did you go about it?
Any ideas and comments will be greatly appreciated.

 

[FoFa]

A lot of HR systems use a start and end date to handle it, but I am more along the lines of an effective date. That way you still have point in time data available to you, and some simple date comparisons work for selection. Start and end dates work good, but you have to make sure your application handles them properly. One of the easier ones I have worked with used an effective date and an archived flag (current record did not have it set). Just some ideas to throw around.

 

[MsLady]

A lot of HR systems use a start and end date to handle it, but I am more along the lines of an effective date. That way you still have point in time data available to you, and some simple date comparisons work for selection. Start and end dates work good, but you have to make sure your application handles them properly. One of the easier ones I have worked with used an effective date and an archived flag (current record did not have it set). Just some ideas to throw around.

Hi Fofa
So you mean i create a new record for the employee each time there is a change?

 

[Kempes]

Why not have an Activity Table which records any activity (name change, Salary change, Role change). Each activity could have a status (active, archived). Active being their current details.

Ie,

Activity Table

ActivityURN
UserID
RcdDate
ActivityType (Combo of what kind of update, ie Salary, Role Change)
Role (Historical if changed)
Salary (Historical if changed)
LineManager (Hitorical if changed)
ActivityStatus
Etc......

If the user changes their name, in the users table you could create a new UserID (new record) and have a link field to the old userID. (Only on name change)
Again, add a status field within the User table (Active, Inactive).
The old user record goes to inactive (VB to handle this), and the new one can link to the old one (again, vb code to do this automatically).
This way you keep your history.

Not sure if this is the best way to do it, but it should give you a reasonable starting point.

Hope this helps.

KEMPES

 

[rburna904]

information about HR data archival

I use to work on HR systems and from my years of writing the apps and data structure the best way to maintain a log of changes is to have a duplicate of your employee/user table. Then create a trigger so that whenever anaything changes on their personal data it gets archived with the old data in the archive table. You will also want to put in there when the change took efect and who made the change....

Example :

User Table
UserId
FirstName
LastName
MI
Suffix
Prefix
Address
Address1
City
State
Zip
Login
Password

Archive User Table
UserId
FirstName
LastName
MI
Suffix
Prefix
Address
Address1
City
State
Zip
Login
Password
ChangeDate
ChangedBy

Then you can run a query pulling all the information from both tables with the same UserId via a UNION ALL query.

With EEO regulations there are also other data information that must be archived as well so you would want to make these archives as well.

Another thing to take into account is items like Assessments and Questionairs

If an Assessment/Questionair is able to be changed by an admin you would want to keep the information based on the current setup for the assessment/questionair while forcing the new information to be a new requirement from the user....

 

[The_Doc_Man]

The biggest issue is that you have added a key to your HR tables. All of them now need to include the DATE on which something is true.

One approach is to keep an AUDIT TRAIL (which you can find if you search this forum) for all records. I won't go into the details because many fine threads within this forum have belabored the point deeply.

The other approach is to have a current table of states but have a shadow table that looks just like the primary table EXCEPT that it has the date on which you archived the current table's record to the shadow table because you were changing some critical value. You would have only one entry for each person in the primary table but any number of entries in the shadow table. You would then search the primary table for current states and values, but you would search the shadow table for older information. (Or a UNION of the primary and shadow tables if you didn't know whether the information was in the shadow table.)

 

[MsLady]

Thank you for all the great ideas. I was really overwhelmed by the wealth of information and suggestions you have provided and have read and pondered on each back and forth carefully to conclude
So, this is what i currently have come up with based on our system (pls see image).
this is just a logical diagram i am still expanding...
I thought it migth be best
I have created a separate table for each group of data i need to keep history of e.g. tblContacts, tblPosition. to start with.

I thought creating an archive (mirror) table might grow rapidly as it's going to create a record anytime there's a change on the main table. but i guess archiving into a smaller tblContacts tables e.g. address when there's a new address might be more scalable... i stand corrected.

I appreciate all your invaluable replies and suggestions. and i'd appreciate more comments on what i have so far.

 

[Jacob Mathai]

This is not a data base issue. But some thing I think is important.
Do not re-use employee id. An employee id should be preserved even after that employee has left the company.

Using SSN as employee id (key) may not be a great idea. It may raise privacy/confidentiality issues. Employees may be very concerned about others seeing their SSN.