I've got a requirement to create a data structure that allows for users to view revision history. Not just the history of a table, but the history of the entire structure at a given point in time.
A pure database solution would involve massive recreation of records. For example - say I'm building a check sheet. Sheets have multiple to-do items, to-do items can be assigned to multiple sheets. It's a simple many to many relationship.
Now say I edit the sheet to remove an item. To maintain revision history, I have to recreate the check sheet and recreate its relationship to the to-do items, minus the one that got removed. And editing a to-do item would cause me to have to recreate every check sheet that references it.
So the solution I'm thinking about is to record the actions that are performed against the check sheets, and when a user wants to specific revision, replay all the changes against an object graph in my code.
Is there an event driven data pattern out there? Is this the best way to do this? I'm just looking for input from anyone who has had to work with revision history for massive data structures before.