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).
Best Answer
The pending table is a better concept. It only gets used to create and approve pending elements.
Approval is a transaction:
This will simplify the lookup logic so you don't need
status=approved
in every other SQL query.