I want to build a web application that will allow multiple users to collaboratively populate the contents of a fairly conventional relational database. (The database will have a fixed schema, which is known in advance).
I can see how to define the schema for the necessary object types, relations, and foreign key constraints (items, items as members of categories, links between items, and so on). Basic CRUD operations to instantiate and modify objects are no problem.
But for resilience against vandalism and mistakes, I can foresee that it will be necessary to have undo/rollback functionality, so that moderator-level users can undo changes made by other users. I'm having trouble figuring out a suitable approach to take for two key functional pre-requisites:
- Capturing all the database changes that result from an initial user request. For example, there's a many-to-many relationship between items and categories. Therefore, if a category is deleted (triggered by a user submitting an HTML form), all the category-item relation records corresponding to that category will get deleted due to referential integrity constraints on the many-to-many relation. How can I record all the cascading consequences of an initial operation, so that's it's possible to completely undo it later?
- How can I isolate undo operations so that a bad action by one user can be undone without also needing to roll back all the beneficial changes which have been made by other users, in between the bad action and the moderator's review?
The Undo patterns I've seen described (e.g. "Command" pattern) all assume that there is a stack of commands and undo operations are always applied in strict reverse order of initial application (no support for out-of-order undos).
Are there any standard patterns for handling undo capability in relational databases which would help meet these two goals? At the moment, I'm looking for generic algorithms and patterns which help solve the problems listed above, rather than platform-specific details.