0

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).

  • Pending records and approved records are 2 different entity types. Pending is template, approved is instance. Yes, they are similar visually... but their actions are absolutely different. 2 entities - 2 tables. One can be converted to another - move from table to table. – Akina Nov 19 '18 at 7:47
  • Using two tables was my initial gut feeling as well. But then the more I read on the issue, the more doubts I had. – Gaspy Nov 19 '18 at 8:08
  • 1
    If only title changes, why do you need to update all users records? Isn't there a group_id or similar? – McNets Nov 19 '18 at 8:37
  • I simplified a bit. The group table holds more stuff. My dilemma was about handling the approval. Do I copy the pending record over the live record? Or delete the live record and the pending becomes live? Second method seemed messier. Using a separate table altogether seems better. – Gaspy Nov 19 '18 at 8:43
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.