Temporal data using NHibernate

Question

Hi,

Can anyone supply some tips/pointers/links on how to implement a temporal state-table with NHibernate? I.e., each entity table has *start_date* and *end_date* columns that describe the time interval in which this this row is considered valid.
When a user inserts a new entity, the *start_date* receives 'now' and the *end_date* will be null (or a date far into the future, I haven't decided yet).
When updating, I'd like to change the UPDATE query into the following:

1. UPDATE end_date for this entity's row, and
2. INSERT a new row with the current date/time and a null end_date.

I tried using event listeners to manually write an UPDATE query for 1, but can't seem to figure out how to implement a new INSERT query for 2.

Is this the proper way to go? Or am I completely off-mark here?

Answer 1

I suggest the excellent timeNarrative from Martin Fowler.

Answer 2

I think the best approach is to have something like a Java map (sorry, I'm a Java programmer) and let NHibernate to map that. The map would map something like a Period, with a "start" and "end" fields, to a value. You can the write a UserType to map the Period to two different database columns

Answer 3

Actually we have a working solution were i work but it effectively kills a part of the nhibernate's mechanism.

For 'temporal entities' NH acts only as an insert/select engine. Deletes and updates are done by a different utility where the ORM part of NH comes handy.

If you only have a handful of temporal entities you may only use nhibernate but be prepared to write your own code to ensure state relations are valid. We went that route in our first try and after the number of temporal entities started adding up the mechanism was effectively broken.

Now, inserts don't need no special tooling, just place the values in the appropriate datetime properties and you're set. We implement selects with Filters (definitely check 16.1 of NH ref as it has an example, but the condition must not use a BETWEEN) although if you go that way you will have to modify the NH source code to apply filters on all kinds of selects. Check my post at http://savale.blogspot.com/2010/01/enabling-filters-on-mapped-entities.html for doing that.

It might also work if you specify the "where" clause on the mapping (instead of filters) but i haven't tried or tested it yet, and it is my understanding that the mapped "where" on the mapping do not support parameters (at least not officially).

As i side note, the reason for using a custom tool for updates/deletes will become clear once you have read Richard Snodgrass's books on temporal databases http://www.cs.arizona.edu/~rts/publications.html

To directly answer your question, both the NULL _end value and a value far in the future will work (but prefer the NOT-NULL solution it will make your queries easier as you will not have to check with ISNULL).

For updates you effectively make a clone of the original entity then set the original entity's _end to now, and then go to the cloned and change the relevant properties, change _start to now, _end to the far-in-the-future value