More On Temporal Data & NHiberante
time to read 2 min | 336 words
 

Okay, I got the reply from NHibernate's developers list, and it seems that I took a step in the wrong direction, and that it is better to use multiply tables for keeping history, and manage the recording using triggers.

So let's take the example of a payroll system, where we want to record employee's data. We would have two tables: Employee & Employee_History. The layout of those tables are the same (id, name, adress, phone, salary) and the Employee_History has an additional timestamp and operation fields. On any operation on the employee table, you've a trigger that record the action and the time it was taken on the employee_history table. That is pretty much all you need on the database. On the code side, here is my solution using NHibernate.

Here is the class diagram:

The EmployeeHistory inherits from Employee, so it contains all the logic, but the mapping files for NHibernate would map to the different tables. Then you can freely work with the Employee class for the most recent version, and you don't need to do anything code-wise to preserve the history of the changes.

So, if you wanted to know what an employee salary was on 01 January, 2005, you could issue the following query:

session.CreateQuery("from EmployeeHistory eh where eh.Id = :guid and 
   eh.Timestamp <= :date and eh.Action != CRUDActions.Delete 
   order by eh.Timestamp desc").
  SetDateTime(new DataTime(2005,01,01)).
  SetFirstResult(0).
  SetMaxResults(1).
  List();

What do you think?*

*I'm playing around with some ideas here, so don't take this too seriously until you've tested that this works.

Tweet Share Share 0 comments
Tags: