CODEWITHSUNDEEP
phpmysqlaudit-trail
Psyche
Psyche

Reputation: 8773

Auditing a MySQL table and displaying changes only after approval

I have a small web application with a few tables where users can post and edit data. I also have an application admin which moderates the new posted or edited data. What I would like to do is to audit each table and display the data only after the admin has approved it.

The application flow is something like this: a user enters an information in some table; the admin will be noticed and he will approve or reject that information. If the approves it, the information will be displayed in the front-end. Once an information is approved, it can be edited later. When a user edits an information, the admin gets noticed again and he has to approve or reject the edited information, but the previous approved information must be still available on the website.

I also need to keep each version of the changed information (something like v1, v2, v3 etc.).

In your opinion, what is the best way to make this work?

Thank you.

Upvotes: 0

Views: 852

Answers (2)

bensiu
bensiu

Reputation: 25574

  1. Extend your table with posts for field IS_FOR_DISPALY default NO.

  2. Create table with approvals containing fields: post_record_id, new_content, aprove_status, aprove_date, aprove_person, entered ....

    • new post should go to posts with IS_FOR_DISPLAY = NO and to aprovals with APROVE_STATUS = NULL,
    • admin should be able to sort / view by APROVE_STATUS, when is NULL - make decision, YES - should also change IS_FOR_DISPLAY = YES and finish initial post aproval process
    • when aproved post is edited, new record should be created in aprovals table with new content, APROVE_STATUS = NULL
    • when admin will view for those NULLs, by POST_REC_ID and IS_FOR_DISPLAY = YES in match record should know there is EDIT, and based on old and new content, who, when etc. make decision, if YES old content could be stored in history table if needed, new content should be placed in posts table, IS_FOR_DISPLAY is already YES
    • if any of aprovals decision would be NO - no changes to post table

This is just general work flow

Upvotes: 2

n00b
n00b

Reputation: 5722

make history and queue tables ? edits go to queue table and if admin aproves queue->real->history...

Upvotes: 0

Related Questions