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:

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

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

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.

Wouldn't it be simpler to have Product and ProductVersion, where the Version table has all the columns from Product plus the authorisation columns. This would mean that changes are done by copying the main fields from the Version table to Product. For new records, the Version.productID will be NULL, but can be filled in once you create the Product record. — Nigel Ren, May 29 '17 at 09:00
@NigelRen well i should have explained that too that this versioning system usage is not just for product table. it have usage for multiple tables with different structure and columns so your way does not apply to my situation — Aref Anafgeh, May 29 '17 at 09:10
Should it be possible to change a new item (product) which isn't in the original table (`products`) yet, because it has never been accepted yet? — Paul Spiegel, May 31 '17 at 16:40
@PaulSpiegel i do not understand your question exactly.in my system i want that user that creates a row (suppose admin has not approved it yet) can edit that row too — Aref Anafgeh, Jun 01 '17 at 07:00
@ArefAnafgeh My question is well answered. I asked it, because if that wouldn't be the case, you could just write `NULL` into `xobject_id` and update it when the item is accepted. — Paul Spiegel, Jun 01 '17 at 16:20

score 2 · Accepted Answer · answered May 31 '17 at 18:21

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

thank you for answering my question.my problem is when i show the user their products, i fetch changes for changes table and in Model Level replace them with real product data.so even if user have any unapproved changes he/she can still see its changes on his panel.but because my query is from product table (joining with other several tables) i don't know what to do with changes that does not have any real product . — Aref Anafgeh, Jun 01 '17 at 07:09
@ArefAnafgeh You will need to change something either way. This way you can keep your schema but need to rewrite some code. You will need to reed the unaccepted items from the `changes` table. Or you change the requirements and list those items in a separate view. I wrote this answer, because you explicitly didn't want to change the original tables. But I would probably add the `accepted` or `status` column to the item tables. — Paul Spiegel, Jun 01 '17 at 16:13

Muhammad Muazzam · Answer 2 · 2017-06-04T09:49:37.850

2

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.

edited Jun 04 '17 at 09:49

answered Jun 04 '17 at 09:08

Muhammad Muazzam

2,810
6
33
62

well the problem is i dont want to insert such record into product table because it creates complexity in other sections of project.i handled to distinguish inserted records from updated records in chages_version table but when i want to show the user that made those changes the data,i show the orginal data(from product ) with their changes merged together.but i do not know how to do this with inserted records.because for merging my system look for product record and in insertion situation there is no product record.i hope i can clearify my needs – Aref Anafgeh Jun 04 '17 at 09:13
well its huge table schema.what specific information are you looking for?table have two state columns , 1.xactive which is enum that have active/inactive state 2.xdeleted which is enum with data : deleted/undeleted. other columns are just information about product. whta kind of data do you need exactly? – Aref Anafgeh Jun 04 '17 at 09:19
ok no issue, tell me where first new record inserted? I need to understand flow of incomming data – Muhammad Muazzam Jun 04 '17 at 09:22
well if the admin insert a product record,it directly first will be inserted in product table. but if user wants to insert product, first a new changes_version record is generated then all columns data are inserted in changes table with forign key related to change_version last record – Aref Anafgeh Jun 04 '17 at 09:25
Ok so you want to manage user insertion only? also tell me next step – Muhammad Muazzam Jun 04 '17 at 09:30
well the main problem is displaying those inserted data to the user that inserted the data because admin does not yet approved the changes so a new product record be inserted. in edit situation i fetched the product record and in Model level i fetched the related PENDING state changes records and fill the product record and then show it to user so user can see their changes but in insertion situation like i said, there is no product record to fetch and fill it with PENDING changes records – Aref Anafgeh Jun 04 '17 at 09:34
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/145794/discussion-between-muhammad-muazzam-and-aref-anafgeh). – Muhammad Muazzam Jun 04 '17 at 09:36
"... inserting ... when user logged in and delete it on logout." - Are you serious? So if a user doesn't want to care about admin approval, he or she just doesn't log out. You can as well skip the approval system completely. – Paul Spiegel Jun 06 '17 at 12:25

score 1 · Answer 3 · answered May 29 '17 at 09:21

1

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

answered May 29 '17 at 09:21

Silverboy.ir

187
2
11

thanks for replying.this is a good way too.but does views have same performance as table in joins and complex queries? – Aref Anafgeh May 29 '17 at 09:30

Best Practices to Build an Admin Approval System

3 Answers3