SQL schema to hold history of employee actions when employees come/go/get promoted, etc

Question

I'm writing an app that contains the following tables: (1) employee_type, (2) employee and (3) employee_action.

Employee_action is foreign-keyed to employee, and contains a description of what happened and the date of the event, just as you might expect.

However, employees can change their type over time (promotion, demotion, relocation, etc). If my schema was just as simple as this, then you might generate a historical report that says that John was the CEO of the company when he was out delivering pizzas 10 years ago.

What would be the best way for me to save the fact that employees had a certain set of characteristics at the time that they performed an action, which are not necessarily their characteristics at the present time?

I'm stating my problem simply here. I have a lot more tables than 3, and the employees position is not the only characteristic that i'm worried about. It's not an option for me to just denormalize everything and make a history table with every possible employee field in it.

Thanks, I hope this is clear.

Answer 1

Have you considered introducing a transition (many-to-many) table linking the employee_type and the employee, and then linking the employee action to this transition table? The transition table could have an additional column for timestamping, that way allowing you to keep track of things chronologically.

Answer 2

Representing time data in SQL is tricky. There is a very good book on the subject, and it's even available for free online from the author: http://www.cs.arizona.edu/people/rts/tdbbook.pdf.

The Amazon page is on http://www.amazon.com/Developing-Time-Oriented-Database-Applications-Management/dp/1558604367, but it's out of print.

If you are serious about modeling changes over time in SQL, this book is a must-read. I learned a lot from it and I only understand maybe 25% of it, having read it only once :)

Answer 3

And to answer your question, I think your only option (if you can't do any redesign of the schema) is to add a table where you store the timespans in which a certain condition held true, eg a table employment_history with the employee, a position ('CEO', 'delivery boy' or the ID's of those positions if you have them normalized to a table) and a field for the begin- and one for the enddate that the employee had that position. That way you can join on the employment_history table to get the position people currently have. Of course, if you need to store more 'properties' than just the position that's going to be an exponentially growing PITA. Read the book above for more discussion :)

Answer 4

This should give a glimpse of idea.

Department Hierarchy with Nested Employees