Version control of a large collection in a database for undo purpose

Question

In have entities: Collection and Item. Each collection has many items. Both Collection and Item can be edited collaboratively by all users of the application. I have to provide a way to undo changes. Versioning Item records is straightforward, the difficult part is to version the relation between the Collection and Item.

• The allowed operations on the collection are: to insert an item at given position, to remove an item and to change a position of an item within the list (which can be seen as an insert at position + a delete at previous position pair).
• The insert operation will be called frequently. Sometimes a batch import can insert thousands of items into the collection. I should be able to revert such import and the undo shouldn't take much time to be executed.
• The list can consist of 20 000 and more items. I rather can't afford to copy the entire collection on each change.
• I should be able to see the state of a given revision of collection to discover changes that were in the revision.

How to model this in a relational database?

I have been thinking about using a multi-valued Temporal Property. Beside Collection and Item there is a link table with vt_from and vt_to time stamps. Probably I would have to create also a CollectionVersion entity, also with vt_from and vt_to attributes. Those versions would be listed on the "history" page of the collection. However, I haven't came up yet with a general algorithm for reverting changes. Maybe I should have diff lists (added/removed) connected to the CollectionVersion that would be used for that purpose?

Answer 1

What you are describing should be easy to model using event sourcing. Basically you want to store the changes to the collection as entities. The actual collections state at anytime can be calculated by applying the collection of events associated with it from creation to the desired point in time.

http://martinfowler.com/eaaDev/EventSourcing.html

http://codebetter.com/blogs/gregyoung/archive/2010/02/20/why-use-event-sourcing.aspx