As a developer you’re probably using a versioning control system, like subversion or git, to safeguard your data. Advantages of using a VCS are that you can walk to the individual changes for a document, see who made each change and revert back to specific revision if needed. These are features which would also be nice for data stored in a database. With the use of triggers we can implement versioning for data stored in a MySQL db.
The revisioning table
We will not store the different versions of the records in the original table. We want this solution to be in the database layer instead of putting all the logic in the application layer. Instead we’ll create a new table, which stores all the different versions and lives next to the original table, which only contains the current version of each record. This revisioning table is copy of the original table, with a couple of additional fields.
CREATE TABLE `_revision_mytable` LIKE `mytable`;
ALTER TABLE `_revision_mytable`
CHANGE `id` `id` int(10) unsigned,
DROP PRIMARY KEY,
ADD `_revision` bigint unsigned AUTO_INCREMENT,
ADD `_revision_previous` bigint unsigned NULL,
ADD `_revision_action` enum('INSERT','UPDATE') default NULL,
ADD `_revision_user_id` int(10) unsigned NULL,
ADD `_revision_timestamp` datetime NULL default NULL,
ADD `_revision_comment` text NULL,
ADD PRIMARY KEY (`_revision`),
ADD INDEX (`_revision_previous`),
ADD INDEX `org_primary` (`id`);
The most important field is `_revision`. This field contains a unique identifier for a version of a record from the table. Since this is the unique identifier in the revisioning table, the original id field becomes a normal (indexed) field.
Continue reading »
Recent Comments