What's the best way to implement a "soft" save or modification workflow?

Question

I'm working on a MVC based web app on LAMP that needs some records to be modified only under the approval of a "superior" user. (The normal user can submit changes but they get applied only after this approval)

There are is only a table in which this would have to take place, say "events":

EVENTS - id - name VARCHAR - start_date DATETIME - guest INTEGER

Every time one of the attributes of an events gets modified by a "normal" user, these changes are not made official until there's a revision (and possible approval) from this "super" user.

At first I though of the following options:

• Duplicating each columns, except the id, say name_temp for "name", to hold the pending-approval modification.
• Creating a separate table with a duplicate structure and hold there all the pending approval modifications.

Have you implemented this before? What do you think is the best/your way to do this? And also: Is there any pattern for this kind of problem?

Thanks!!!

PD: I need to keep the "old" record where it was, until the new one gets approved..

Answer 1

Why not add 1 more column named IsApproved? bool or tinyint(1) type? and just now show approved events?

Edit: Oh it's every attribute approval. Then 2nd table would be my choice named PendingEventChanges with same structure or just id+changeable properties, and on aproval "super" user would update original data and would remove etries from pending.

First choice is very much against database normalization, so adding more properties in future would cause trouble.

Answer 2

If it's only about a permission for adding new events, I would go with one additional boolean column, namely is_approved or similar.

There are edits possible, though, so, in my opinion, it's a big no-no to duplicate every column in the same table to store the temporary value. Just imagine what would happen when two users post their changes to the same event.

Option number two, namely a separate table, is a better choice. You can store each attempt there and just update your main table accordingly.

With this approach even a some form of rollbacking changes is possible. Just be sure you store a timestamp with each and never delete your approved edits.

Answer 3

Let me add my vote for the "second table" approach -- as both other respondents said, it's head and shoulders above attempts to shoehorn "proposed changes" into the master table (that would complicate its schema, every query on it, etc, etc).

Writing all intended changes to an auxiliary table and processing them (applying them to the master table if they meed certain conditions) "in batches" later is indeed a common pattern, often useful when you need the master table to change only at certain times while proposed changes can come in any time, and also in other conditions, e.g. when writes to the master table are very costly (lock contention or whatever) but writing N changes is not much costlier than writing one, or validating a proposed change is very time-consuming (you could class your use case as an extreme case of the latter category, since in your case the validation requires a human to look at the proposed change -- VERY slow indeed by computer standards;-).

Answer 4

If history or versioning is important, combined with the approval, I would go for a single table. You would add a revision number, which could work with your existing primary key and could be incremented on each revision. You could then add a state field to show which is the current version, expired versions and needs approval versions. A nice index on the state and key field will get you snappy results. If this is just one, two or three fields out of many, then a specific table to handle these unapproved edits might be better as suggested above.