Versioning and data moderation?

Question

CVS

Hello!
There is a table for storing user data with such columns

id<br/>
*name<br/>
*surname<br/>
jabber<br/>
skype

You need to do the following:

1. Keep edit history for each user
   
2. Fields marked with an asterisk are moderated, and changes to the data in them must be confirmed by the administrator.
   
3. Sometimes a rollback to previous revisions will be required.
   

I can’t choose how to store these revisions, I came up with three ways:

1. Store data and revisions in one table. In this case, no tables need to be created, but the table will be populated with revisions that are rarely needed.
   
2. Store revisions in a separate table. In this case, it is not clear how the revisions for unmoderated fields created after the revision for moderated fields should be stored.
   
3. Store revisions for moderated and unmoderated fields in two different tables. In this case, you will need to do additional logic to spread the data into two tables. And the revision itself becomes non-atomic.
   

Which way would be the most correct?

Answer 1

As far as I understand, the case that confuses you most of all is when the user has changed the moderated field, the moderator has not yet had time to confirm, and then the user has changed the unmoderated field and is waiting for this change to appear immediately. In this case, I see no other option than 3, that is, separate storage of moderated and non-moderated fields. The change in logic is minimal.
What revision atomicity are we talking about, what case are you considering? When did the user change both moderated and unmoderated fields on the first visit, and only unmoderated fields on the second visit? It is precisely with joint storage (options 1 and 2) that ambiguity will arise, that is, if the moderator confirms change 1, then which record data should be considered relevant? There are two options - to enter into the record at the second change in the moderated fields either the original data or the data from the first change. The first option breaks down if the moderator confirms the post, and the second one fails if it doesn't. I hope I have explained well.

Answer 2

I will describe how it was done in one of the systems that I am finalizing.
There are two tables:
Main - stores the most recent version of the record in the field set.
Correction table - stores the same set of fields + Date of change, user (who made the change), and a flag that the record was deleted. In your case, it is worth adding a flag to the second table that the data is confirmed.
Thus, in the main table, you get the most up-to-date state of the record.
And in the history table you can see which field and what was changed at what point. At the same time, for each version of the record, you can find out whether there was a confirmation.

Answer 3

Depending on the tools:
* see Partitioning - the database will do everything by itself
stackoverflow.com/questions/102278/active-flag-or-not
* The 2nd option can work with tables with the same fields to make life easier

Answer 4

Option 2 is the best.
In order to avoid collisions of the type that you mentioned, it is enough to store only the changed field / fields in one entry in the revision table, and the rest are NULL.