Hi,

I'm looking for a way to maintain historical data on all entities in my database, including links. In other words, I want to be able to query the full state of an object graph at any point in time. for example, if the user changes a quantity in a LineItem, I want to save a new version of the LineItem and a new Order referring to the new LineItem and the unmodified LineItems as well -- this way, I can query the previous order and see its complete form.

The other part of my probel is that I want to differentiate between foreign key references to objects of a specific version (e.g. Order/LineItem) and ferences to the most current version of an object (e.g. the Order eferences the User who entered it -- here I want the most recent version of User queried, not the historical state when the Order was changed).

What are some recommended appraoches for accomlishing this? Some ideas I've had so far:

1. Maintain a separate Order_ID table that links the order's primary key to a version primary key.
2. Referencing historical objects by both their ID and version number; for cases where the most recent verion is required use a nested SELECT to get the highest version number)
3. When an entity is updated, write its previous state as a new object (forcing a re-write of any associated link tables) before updating the entity row itself.

Every approach has some drawbacks in terms of time/space. I'm more interested in fast query performance. I realize I will have to give up one-to-many relationships in favor of link tables for all relationships, but that’s fine.

Any ideas?

Thanks a lot!

Jen

good article of .
Martin Fowler :Patterns for things that change with time.

http://martinfowler.com/ap2/timeNarrative.html