Best practices for managing updating a database with a complex set of changes

Question

I am writing an application where I have some publicly available information in a database which I want the users to be able to edit. The information is not textual like a wiki but is similar in concept because the edits bring the public information increasingly closer to the truth. The changes will affect multiple tables and the update needs to be automatically checked before affecting the public tables.

I'm working on the design and I'm wondering if there are any best practices that might help with some particular issues.

1. I want to provide undo capability.
2. I want to show the user the combined result of all their changes.
3. When the user says they're done, I need to check the underlying public data to make sure it hasn't been changed by somebody else.

My current plan is to have the user work in a set of tables setup to be a private working area. Once they're ready they can kick off a process to check everything and update the public tables. Undo can be recorded using Command pattern saving to a table.

Are there any techniques I might have missed or useful papers or patterns?

Thanks in advance!

Answer 1

I would do it like this:

• Use insert only databases, you never update data only add new rows
• Each row has a valid from date, a valid to date and who made the change
• Read the data through a query where the valid to date = null, and the row is approved, this gives the most recent row
• When a user adds data, he can see his changes by selecting the last row that he added
• When the user is happy with the changes he has made he can mark them as approved
• When they are approved they can be seen by other users
• Undo is not a problem since you have all the previous versions, you can mark a row as no longer being approved and revert to a previous version.