Table design to support pending changes

Question

I have a users table. Users can belong to a group. For them I have a group table, with id, title and description.

Now, any changes in the group table must be approved by an admin. So, I need a (preferably fast & easy) way to handle both live and pending group data.

I thought about having a status field in the group table and adding a new record whenever a group is edited. But what do I do when the edit is approved? Copy the title and description back to the live record and delete the pending one? If I delete the old record and mark the new one as live, then I need to update all users records that use the old group_id to the new one. Also, all pending records would need to reference the live records...

Is there a better solution? Part of me just want to say 'forget it' and simply duplicate the fields - have a live_title and a pending_title.

I'm using MySQL. I prefer a solution that is fast and easy to implement (I need to build an API for it).

Answer 1

The pending table is a better concept. It only gets used to create and approve pending elements.

Approval is a transaction:

  BEGIN;

  INSERT INTO title
  SELECT * 
  FROM pending_title
  WHERE id = {x};

  DELETE FROM pending_title
  WHERE id = {x};

  COMMIT

This will simplify the lookup logic so you don't need status=approved in every other SQL query.