I currently have a database which many users can access and make changes to. The is also a log database that stores all changes to tables within the database using triggers.
I would like to add the ability to approve edits before they are changed in the database.
What would be the best way to go about this?
We have something similar on one of our sites, we've added a bunch of tables:
users sites ... etc
Then we have a bunch of shadow tables:
users-shadow sites-shadow ... etc
The shadow tables have identical structures to the real tables except for an added line for the user who made the change. So first we use this query when a change is submitted by a user who needs to have his/her database actions approved:
REPLACE INTO users-shadow (user_mod,id,username,password,salt...) VALUES (16,50,'bob','stuff','salt'...);
Obviously, make sure this isn't open to injection, use prepared statements etc.
When approved, a row in the shadow table is simply removed from the shadow table, the user_mod value dropped and changes (non-null values) inserted into the real table (or updated if an id is specified, using REPLACE syntax). We do this logic in perl so sadly don't have any SQL on hand for it.
Remember that SQL REPLACE does a DELETE and an INSERT rather than an UPDATE. You will need to amend any triggers to allow for this behaviour.
Note: The reason we didn't use an 'approve' flag was that we needed the ability to amend existing records, of course we couldn't have multiple records with the same primary key.
well i have made this system once and here is my solution for DB structure and over all algorithm:
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/her panel(not main system, only his/her panel).
at the end if system administrator accept a user changes_version version and its related changes records ,system should place each changes table record in the right column of product table(for example i used product table, with this system you can versioning and admin approving any table).and change version record state to approved and its changes related records to approved to. so with this structure you can save and versioning different tables and keep log of each version changes.
