How to save modifications to apply later?

Question

The moderators of my webapp have the possibility to modify some data in the database. Those modifications are visible by all the users. But for some reason, I don't want them to apply immediately, but rather only after a specific action, for instance, when I explicitly accept. The timeline is: - A field in a table has the value `PreviousValue`. - A moderator requests this value to be `NewValue`. All users continue to see the value `PreviousValue`. - I accept the change. All users now see the value `NewValue`. The problem is that the moderators can modify fields in a high number of tables. What is the best way to handle this temporary values? I can imagine these solutions, but none of them sounds good: - Duplicate all the tables (having together `MyTable` and `MyTable_ToApply`). - Add a special table (`ToApplyTable`) with four fields : the table to modify, the field to modify, the ID of the entry to modify and the new value to apply. Do you have better ideas?

Accepted Answer

As soon as question quite broad (and can have many good answers) - this is not an answer, but another possible solution:

you can have table like:

```
id - just autogenerated number
datetime - date of change
changed_by - string with name of actor or id of some user
oldvalue - just old value
newvalue - just new value
update_insert_sql - here you put actual SQL which should be executed (you already have this sql in your current code)
state - 0 - it is not applied to db, 1 - already executed, 2 - you're rejected this change
datetime_of_apply - just date of action
datetime_of_reject - just date of action
```

NOTE: instead of storing old value, you can store SQL to fetch current value, this will help you when there are several changes from different moderators to the same row/column

so, in your interface you will be able to see all proposed changes to all tables, see only actual value and proposed new value and update db if needed

possible addition: to populate this table you can use triggers, instead of changing current code

How to save modifications to apply later?

Solution

Problem