Hello.

I have a customer that has keep track of all his data. When it was changed, by whom. What was the value of record 'X' on march 15, 2006, ...

He solved this problem like this: lets take a table T_USER as an example. T_USER would have the usual fields:
- UserId
- User_FirstName
- ...

Next to that, T_USER has the following fields:
- Sequence (int)
- StartDate (DateTime) (=the moment this record is valid)
- EndDate (DateTime) (= the moment this record stops being valid)
- IsDeleted (bool) (= this record has been deleted)
- ChangedBy (username)
- ChangeDate (DateTime)

The primary key of T_USER is a compound key of UserId and Sequence.

Next to T_USER, there is a table called T_USER_LINK. This table holds only one field:
- UserId (PK)

Strange, no? Here's how it works:
-) when you insert a new user, you insert a new record in T_USER_LINK. The generated unique key is then used to insert a new record in T_USER. UserId is set to the new key, Sequence is set to 1. StartDate and EndDate are set as needed (optionally EndDate can be left 'null', meaning this record is valid 'till the end of time).
-) when you update an existing record, you don't touch the T_USER_LINK table, but you insert a new record in T_USER. The previous record gets an enddate equal to the new record's startdate. The new record's sequence gets incremented by 1, so it's compound primary key stays unique.
-) when you delete a record, you set it's enddate to the current date, and set deleted to true. You never EVER delete a record (!)

What this does is that you effectively have some kind of source control for your records. If you could draw a diagram of the records, you'd get a sort of Gantt chart where every record 'cascades' into the next record with the same Id, but a different sequence.

When you read records, you must always specify a Date for which all the records returned must be valid.

I am looking for a generic way to handle this logic that is common to all the tables in the database. That means that I don't want to write specific views, stored procs, data access objects or any other per-table stuff to handle this rather generic mechanism. I want the applications on top of that database to be agnostic to this mechanism.

The select stuff is special, but not a real challenge for NHibernate. However, I have a big hunch the updates will be a problem. Does anyone see a possible solution to handle this?

Fire the original designer? :-D I mean, I suppose his technique works, but, Holymotherofgod that sounds like a maintenance nightmare.

Lol, nice suggestion. Anyone else?

This is REALLY the database I will have to work with. There is nothing I can do about it for now. Maybe later I can come up with a better idea. For this, all suggestions are welcome too. The tracking of records IS a requirement. I'm sure I'm not the first one to encounter this requirement.