2

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.

  • What kind of data needs an approval mechanism?? 21 tables smells like "over-normalization". – Rick James Nov 2 '18 at 4:56
  • Actually, whole database have 21 tables, but not all tables are in use in every moment, 10-13 tables are in use most of the time. My database is a special kind of dictionary, and all entries must be precise. Word type, translation, references, description, function... – Boris J. Nov 2 '18 at 22:04
0

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

  • Thank you for your extensive answer and advice. This is a very reasonable approach and I think it can work in my case. I will approach it with non-normalized table for user-input before approval, and after approval, all data will migrate to the relational tables of the main database. – Boris J. Nov 3 '18 at 9:16
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.

  • Thank you for your answer. I think that approach number 2 sounds better, but what about if data already exists in some of the original tables? Let's say in some parent tables, but not in junctional? There would be a situation where user try to insert some of the data that already exists in the final tables, and how would that function if there are separate temporary tables which do not have these same data? – Boris J. Nov 1 '18 at 20:31
  • Yeah, the relations could be a nightmare with #2. – Rick James Nov 2 '18 at 4:55

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.