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:

1. A field in a table has the value PreviousValue.
2. A moderator requests this value to be NewValue.
   All users continue to see the value PreviousValue.
3. 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?

Answer 1

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

Answer 2

When an update happens, create a new row and have a flag that a moderator needs to look at it. When moderators accepts, flag the accepted record as active, and the previous record as inactive. Do this at the highest level so that if you have other tables joining off of this they inherit the active / inactive state and the is_moderated flag.

id | text      | is_active | is_moderated | is_accepted
-------------------------------------------------------
1  | hello     | 1         | 1            | 1             <-- This is the active row

New record is added changing hello to goodbye

id | text      | is_active | is_moderated | is_accepted
-------------------------------------------------------
1  | hello     | 1         | 1            | 1             <-- This is still the active row
2  | goodbye   | 0         | 0            | 0             <-- Moderator needs to accept or deny this

Accepted Result:

id | text      | is_active | is_moderated | is_accepted
-------------------------------------------------------
1  | hello     | 0         | 1            | 1             <-- This is NOT the active row
2  | goodbye   | 1         | 1            | 1             <-- Accepted, new active row

Denied State:

id | text      | is_active | is_moderated | is_accepted
-------------------------------------------------------
1  | hello     | 1         | 1            | 1             <-- This is still the active row
2  | goodbye   | 0         | 1            | 0             <-- Moderator denied

Your selected query becomes:

To get active row:

SELECT * FROM TABLENAME WHERE is_active = 1

To get rows requiring moderation

SELECT * FROM TABLENAME WHERE is_moderated = 0

Answer 3

Actually, the answer is not so difficult. Here's is my idea:-

Step 1 : Just add a column to your table Posts(or the table containing the entire posts by the users) named Moderated Post. Any moderation or edits by any moderator goes into this column.

Step 2 : Add another column to the table Posts named isModerated. This contains a value 1 or 0 just like the boolean true or false.

Step 3 :Now, you are almost done. You have got a table like this :-

                      TABLE `POSTS`

| Post id | Initial Post | Moderated Post | isModerated |
_________________________________________________________
          | The first    |                |             |
|    1    | post goes    |                |      0      |
|         | here         |                |             |
_________________________________________________________
|         | The second   | The moderated  |             |
|    2    | post goes    | post goes here |      0      |
|         | here         |(Post invisible)|             |
_________________________________________________________
|         | The third    | The moderated  |             |
|    3    | post goes    | post goes here |      1      |
|         | here         | (Post visible) |             |
_________________________________________________________

Now in your php code, after retrieving all the values from the table, you display your post like this ( JUST A ROUGH SKETCH) :-

<?php 
if($isModerated == 1) { 
   echo $moderated_post; 
}else {
   echo $initial_post;
}
?>

For changes in numerous tables

As you said that the moderation changes values in a number of tables, for that you have to add the column (Moderated_Value1) x n where n denotes number of columns whose values can be changed. Example :-

| Value 1 | Value 2 | Moderated_Value1 | Moderated_Value2 |
|   0     |    1    |      1000        |     2000         |

Now, your code becomes :-

<?php 
if($isModerated == 1) { 
   echo $moderated_post; 
   echo $moderated_value1;
   echo $moderated_value2;
}else {
   echo $initial_post;
   echo value1;
   echo value2;
}
?>

Using this method, there is also an advantage that you can easily rollback the moderation by changing isModerated value in the table from 1 to 0