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?