Best Practices to Build an Admin Approval System

Question

there should be a sub system of admin panel which different users can manage their products but every change should be approved by administrator before going affecting the main Product table. there is three main table:

1. Product : store products that have final approved and are used in entire system

2. Changes_versions : a table with One To Many Relation with Product Table that indicates each change version is committed by who , when ,and is approved/rejected by admin or still is in Pending state .table structure is as following :

   CREATE TABLE `changes_versions` (
     `xid` int(11) unsigned NOT NULL AUTO_INCREMENT,
     `xcreated_date` datetime DEFAULT NULL,
     `xupdated_date` timestamp NULL DEFAULT NULL,
     `xversion` int(11) DEFAULT NULL,
     `xobject_id` int(11) DEFAULT NULL,
     `xobject_type` varchar(255) DEFAULT NULL,
     `xstate` enum('PENDING','ACCEPTED','REJECTED') DEFAULT 'PENDING',
     PRIMARY KEY (`xid`)
   ) ENGINE=InnoDB AUTO_INCREMENT=165 DEFAULT CHARSET=utf8
   

3. Changes : a table that have One To Many relation with Changes_versions table that keep every column change record of the main Table (here i mean product table) and by approving a change_version record by admin its related changes records will be placed in main table column. table structure is as following :

   CREATE TABLE `changes` (
     `xid` int(11) unsigned NOT NULL AUTO_INCREMENT,
     `xcreated_date` datetime DEFAULT NULL,
     `xcreated_by` varchar(255) DEFAULT NULL,
     `xupdated_date` timestamp NULL DEFAULT NULL,
     `xupdated_by` varchar(255) DEFAULT NULL,
     `xversion_id` int(11) DEFAULT NULL,
     `xcolumn_name` varchar(255) DEFAULT NULL,
     `xcolumn_value` varchar(255) DEFAULT NULL,
     `xstate` enum('PENDING','ACCEPTED','REJECTED') DEFAULT 'PENDING',
     `xadmin_review` text,
     PRIMARY KEY (`xid`)
   ) ENGINE=InnoDB AUTO_INCREMENT=764 DEFAULT CHARSET=utf8
   

with this system and table schema i handled to work with record changes, user fetch list of records ,if user have any Pending state change_version, system will pull its related changes records and place them in the right column in the fetched product row(temporary just for displaying) , so even if user has any pending state changes he/she can see its changes in his/hes panel(not main system, only his/her panel).

the problem is for INSERTING new records, i can create a change_version record and save all user data to changes table pointing to new change_vesrion record but change_vesrsion record is not connected to any product record because there was no record.

notice that based on system complexity and current stability i do not want to add any column to product table to indicate that this is a temp record.

so i want a strategy to handle issues like when i paginate products in user panel and filling them with last PENDING changes, there is not product record for inserted record to fill with changes so user cant see his/her previous inserted product.

---

I should also notice that some what this tables structure may seems complex for this question.this structure is complex because changes_vesrsion and chaneges tables save and present historical and admin approval process for many tables with different structures.

Answer 1

Let me first describe your issue in my words (correct me if I'm wrong).

You have different items (like products) in your system with one (InnoDB) table per item type. Every item table has an AUTO_INCREMENT column (like id). You have an item versions table (changes_versions) to store different versions of a specific item. The item is identified by the columns

• xobject_type (like 'product') which references a table
• xobject_id which is a "polymorphic foreign key" referencing the PK of the table above

The problem: When a user creates a new item, it shouldn't be inserted into the items table, but you need a reference to store in the xobject_id column.

A possible solution: "Reserve" the ID by inserting and deleting a row in a transaction.

Example:

start transaction;
insert into products(column_1, column_2) values ('value_1', 'value_2');
delete from products where id = last_insert_id();
select last_insert_id();
commit;

What you will get:

• The inserted row will never be visible for other users since it is deleted in the same transaction.
• last_insert_id() will still return the auto generated ID from the deleted row;
• That ID will be "burned", so the engine will never generate it again.
• You can still insert that ID manually, when an admin accepts the item.
• No changes of the DB schema are required.

Demo: http://rextester.com/IJB42705

Answer 2

I think SQL View can be handy here, change your table name and adding a flag field that temp rows is flagged by this field , then create a view with name Product and exclude this field and rows flagged by this field in the View , by this way your Original Table (that now converted to view) will remain Untouched

Answer 3

A solution to this is create all tables as discribed and when user create a product into changes_version table there should be a mysql event that check regularly each record in product table and match it with changes_version table. If any changes or new insertion found inside changed_version table, the product table should be inserted/updated accordingly.

Revised Solution:

You can achieve it by inserting user record into product table from changes_version when user logged in and delete it on logout. This works for those products who are not yet moved into product table and you can check it via query.

If you have front-end source then you can show changes_version record to user only and moved it via trigger to product table when it gets approved.