Looking for advice for implementing a versioning feature in our application

I am starting a project to create an "object versioning" feature for our software (.NET 3.5 / SQL Server 2008), basically it needs to do this:

• a user is looking at a customer:
  • last name is "Smith-Johnson"
  • 2 addresses (saved in different table)
  • 1 product purchased
  • 1 employee contact (saved in different table)
  • 200 mails (saved in different table)
• user clicks on "View Past State" button and chooses "September 25, 2009 15:00" which brings up a separate view which shows the same customer:
  • last name is "Smith" (name has changed since then)
  • 1 address (since then an address was added)
  • 1 product purchased (but it is different that the above since he bought this, returned it later and bought a new one)
  • 2 employee contacts (since one had been deleted since then)
  • 10 mails

In thinking about the problem in general a number of issues come up:

• at what level should changes be logged, e.g. at the database level (log every change in every property of every table) or the object level (serialize and store every object and its dependences after after change)
• how will changes in the structure of the tables be handled, e.g. if column "LastName" changes to "Surname", how to track the data in the columns as all belonging to the same column (so the versioning service do开发者_StackOverflow社区esn't report "this customer didn't have a Surname on September 25th" but instead knows to look in Lastname.
• what supportive technologies in the .NET / SQL Server 2008 area exist that might support this, e.g. I am looking into the Change Tracking feature of SQL Server 2008
• what kind of patterns exist for versioning, e.g. I'm thinking of the Command Pattern which can be used to create an Undo feature in application.
• This version service does not need to perform rollbacks. It just needs to be able to show the state of objects their dependencies.

What has been your experience implementing versioning features into software? What advice do you have?

I've worked on software with similar features to this.

Instead of data being updated in the database, every change was inserted as a new record. Each row in the database has a start and an end date.

The latest record is the one with no end date. From there, you can query the state of the data at any give date just by searching for the records that were active at that time.

The obvious downsides are storage, and the fact that you have to abstract certain aspects of your data layer to make the historical tracking transparent to whoever is calling it.

Martin Fowler documented some interesting patterns as he is developing the next version of Patterns of Enterprise Application Architecture that you might find interesting:

• Audit Log
• Snapshot
• Temporal Object
• Effectivity
• Temporal Property
• Time Point

A couple years ago I designed a system to version objects so that they could be copied between databases in different environments (Dev, Staging, QA, Production)

I used a hand-rolled AOP approach at the time. Spring.NET or some other IoC framework that supports AOP would be better today. Each modification to an objects' property was stored as a Change in a centralized database. Each Change had a version number. We used the Change(s) to record actions taken against objects in one database and replay them in a target, effectively copying the changes.

If you're using an ORM, you should be able to change column names in your database without worrying about property names if you go with this kind of solution. If you do change a property name, Change records can be updated with the new name.

One approach among many, but it worked well for us and I think it could be a good start for your case.