How to add versioning to this CMS I’m designing

central-management-serverchange-data-capturedata-versioningdatabase-design

Database design

As you can see, my CMS consists of several widgets (for example an Articles widget), of which you can create several instances, which I call blocks, and those blocks are linked to pages and the containers on those pages. The actual data for a block (e.g. an article instance) is stored in other tables which are not shown here. Containers are set up in a tree-form and every page links to a parent-container and therefore has that container and all of its children. I made it this way, because I'd like to make it very very modular. A bit like Concrete5.

Now I'd like to add versioning to this, so that people can get an old version of their page back if they messed it up. But I have absolutely no idea how to go about it. I've tried to Google for versioning tutorials, but I couldn't find any. Right now the only way I can imagine to do it, would be to make snapshots of pages with all of their associated containers, blocks and widgets and save that as a serialized php-array in a blob in my database. But that just seems like a horrible way to do it.

Best Answer

The standard way is to use a History Table pattern.

If you have a table like this:

create table foo (
  int foo_id primary key,
  name varchar(x) not null
);

You would have a corresponding table

create table foo_history (
  int foo_id,
  int version,
  name varchar(x),
  inserted_at timestamp not null default current_timestamp,
  operation_code char(1) not null, -- D=deleted, U=updated [, I=inserted]
  by_username varchar(x) not null,

  primary key (foo_id, version)
);

The data in this table would be filled by triggers on foo.

If you use Java, you could use Hibernate Envers to automate this for you.

Related Question