Temporal databases...

nathanbaulch · **Joined:** Wed Aug 31, 2005 9:29 pm **Posts:** 14

I'm looking for some general advice on the best way to proceed with my particular design.

I have a database in which most tables have a CreateDate and a RemoveDate field. Nothing from these tables will ever be deleted, only given a RemoveDate. The application driving this database will have a calendar visible at all times where the user can choose the current date. Only records that were valid on that date (CreateDate is before or equal and RemoveDate is null or after) will be displayed. So as the user moves around the calendar, the database will always look as it did on that day.

There's no problem listing objects based on the dates using a criterion. The problem is with collections, which are automatically loaded.

What is the cleanest way to go about this?

Cheers

radu · **Joined:** Wed Jun 15, 2005 4:17 am **Posts:** 156

hmm, very interesting! just some thoughts:
have you tried to use the "where" attribute in the class tag? I imagine in this case you should set somewhere a record with the date meaning "today". I have never tried this, but I suspect that the collections will be aware of this filtering criteria.

Cheers,
Radu

KPixel · **Posted:** Sat Dec 10, 2005 8:41 am

Make the collection lazy and filter it whenever you need a sub-set (read 9.3.4. Filtering collections)...

Nathan Baulch · **Joined:** Thu Dec 08, 2005 6:49 pm **Posts:** 49

Quote:

have you tried to use the "where" attribute in the class tag?

The problem is, as I understand, that I'd have to create a new table/class/mapping for the "current date" table and place a many-to-one link from all temporal objects to that table.

Quote:

Make the collection lazy and filter it whenever you need a sub-set

Since I would like to one day make my application pluggable, I was hoping for something a little less inconvenient for the consumer of my API. This approach also doesn't seem to work with Ayende's EntitySet<T> technique which I've implemented (problem outlined here: http://forum.hibernate.org/viewtopic.php?t=954292).

What about if I create my own EntityPersister? This would completely hide the inner workings from the consumer and clear up a lot of repetitive plumbing code. At a glance, it looks like I'd be overriding the GenerateSelectString() method and adding a couple of where clauses via SqlSimpleSelectBuilder.AddWhereFragment().

Is there anything I should know before giving it a shot?