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:
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.
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 tablexobject_id which is a "polymorphic foreign key" referencing the PK of the table aboveThe 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:
last_insert_id() will still return the auto generated ID from the deleted row;