2

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.

2

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.

  • That means that every widget I make has to re-implement versioning, right? And that also means that if I change a page by changing 2 of its blocks then that change is reflected into 2 different tables. So the rolling back process would need to look at a lot of different tables. Would it be possible to do it all in one table? – Evert Jul 7 '14 at 18:27
  • Ya it's a pain in the ass. I would recommend trying to automate the history table creation/alter process. I wouldn't recommend putting everything in one table, but you could try, esp w a db that supports JSON. I would re-evaluate your design to use table inheritance to cut down on the # of tables. For example in Drupal everything inherits from Node. – Neil McGuigan Jul 7 '14 at 18:31
  • I've never heard of table inheritance. Does that exist in MySQL? Or only in more advance systems like PostgreSQL? Can you link me a page that explains how that works? – Evert Jul 7 '14 at 18:53
  • 1
    It's just a design pattern for sql tables. See martinfowler.com/eaaCatalog/singleTableInheritance.html . PostgreSQL has a feature called Table Inheritance which is different. – Neil McGuigan Jul 7 '14 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.