Insert/update data into relational database tables only after administrator approval - how to approach it?

Question

I'm building a relational database that holds 21 tables, which is connected to my website application through PHP.

The website has search functionality which allows users to search this database. Also, users can enter new data, through a special form. Because these entries must pass rigorous control by administrators, I want to make some kind of approval system, available only to the administrators, which can see user input before it appears in the search results, and allow it, return it to the user to make changes, or delete it.

My question is what would be an approach for this situation from a relational database design standpoint, and what is the best practice?

P.S. I know my question is probably wide, but I could not found an answer by searching the internet or other posts on this website.

Answer 1

I'd build a UI specifically for the admin and his approval. It would display the things that are awaiting approval. He would click [Approve] or [Reject] (etc?) by each one. That would go off to the backend, perhaps via AJAX, for processing.

In the database would be two sets of tables. They would not necessarily be identical in number of tables, nor in columns.

One set would be the fully-approved data and be viewable (again through some UI) by anyone allowed into the system.

The other set would be aimed just at the admin:

• Pending stuff gets put into these tables via whatever ETL mechanism makes sense.
• The AJAX calls from the admin would cause a row
• to be deleted (or flagged as rejected), or
• to be copied to the other set of tables ("approved") and deleted, or
• to be left there for further pondering, or
• whatever.

Perhaps a "row" in the admin's table(s) may be denormalized. Then the normalization occurs when/if it is copied to the approved table(s). That is, you should decide where it is convenient to do the normalization: before versus after admin approval.

Whether the as-yet-unapproved rows point to normalization tables in the approved area is a matter of convenience. I have no problem with "normalizing" a value, then deleting the link to it. This may leave an unreferenced item in the normalization table, but that is sometimes a small price to pay for convenience elsewhere. (No, I don't think FOREIGN KEYs are of any use because there is no "reference counting" feature associated with ON DELETE CASCADE.)

Answer 2

You basically have two options:

1. Extend each table with a 'Status' column, with possible values 'New', 'Approved' and 'Rejected'. Maybe another column for remarks by the administrator, and one to identify the owner of the record (which might already exist). All modules except for the import module should always filter on Status = Approved, which also influences your indices. This approach minimizes the impact on your data model, but that's probably the only advantage.

2. Build separate import_* tables for each table. They'll have the same structure as the tables in 1. Once a record is approved, it's copied over verbatim to the 'real' table. Creating and maintaining the tables is more work, but data is better separated and you don't need to change the logic of your core application.

There's no built-in waiting queue for INSERT statements in MySQL (or any database for that matter). What you describe is generally handled in application logic, and the database is just there for storage.