How do you store multiple states of a record?

[SJH]

Hi everyone,

I need to be able to store multiple states of the same record, which are:

1. The live version, which visitors to the site will see
2. A draft version, which administrators will see in the admin area
3. Previous versions, all versions of the record from creation up to now

Certain attributes of the record will be concurrent across all states of the record (such as the record's name, its category) whereas others will need to differ depending on which state is being looked at.

I thought of setting it out something like this:

widget
  id
  title
  category

widget_content
  widget_id
  description
  state (would be either NEW, CURRENT or OLD)
  date

Would this work or are there any obvious pitfalls to this approach?

This is the first time I'll have done something like this so any input into how best to go about implementing this efficiently would be much appreciated.

Sam

[oddz]

widget
-- id

live_widgets
-- id
-- widget_id

draft_widgets
-- id
-- widget_id

widget_versions
-- id
-- widget_id

1.) Widget has one live widget
2.) Widget has many drafts
3.) Widget has many versions

You could remove the live_widget table and instead make the most recent version the live version if that suited your needs.

[RCD24]

Could you not just use one widget's table with an auto_incrementer as the key, and the Page_id or Widget_ID or whatever to basically connect all that are related, and then use another field that accepts int inputs, and have a certain number (1) be live, 2 draft and 3 be the previous versions.

then all you'd need is to query one table and identify and sort via the type id, and then sort by Date for the previous edits.

sound simple enough?

[elias]

Would this work or are there any obvious pitfalls to this approach?

if the state of each widget can transist into another state,
you'll have the problem that you need to update the state
fields each time.
in that case versioning the widget with an running number
and calculate the state from this version will save you a
lot of work.

[Cups]

As far as I can see, there is one live view, one draft and n old versions.

Here is a way to do it that you might consider.

Keep the draft in your table, each time it is "published" then lay down a cache file which overwrites the old cache file, that is your live version.

Simultaneously just copy the data into to a backup table, essentially a copy with an auto-datestamp, then not only you can roll back to any old version, but you have an audit trail of what was published at any given time too.

This will work on simple systems, but if as I expect you have a number of possible editors, then you will need some way of locking the record if someone is already working on it. That is a different question though.

[Czaries]

A simple solution would be to just add 'version' and 'original_id' columns to your table. Instead of doing UPDATEs on that table, always do INSERTs, filling in the 'original_id' column to relate the rows to each other if an entry already exists. For the 'version' column, just SELECT MAX(version)+1 WHERE original_id = X and fill it in. Then to display the rows, just add ORDER BY version DESC LIMIT 1 to get the newest revision or have an additional marker column like 'is_live' for the version to use.

Maybe a little too "quick and dirty", but it prevents duplicating the table structure and other things like that.

[Czaries]

After thinking about it some more, I would probably setup 2 tables:

widget
id
title
category
description
date_created

widget_versions
id
widget_id
content_id
date_created
is_live

Then do something like:

SELECT w.*
FROM widget_versions AS wv
JOIN widget AS w ON (w.id = wv.widget_id)
WHERE wv.content_id = X AND wv.is_live = 1

The 'content_id' would be the common thread to tie the 'widget' entries together as versions so you know they're related.

And then always do INSERTs into 'widget' instead of UPDATES, and add an entry to the 'widget_versions' table for each 'widget' entry, with the 'content_id', making sure to mark the current live one and clear any previously marked live versions.

[Mastodont]

I recommend to put all "live" fields into one table and drafts with old versions into another table. This scenario removes need to do JOIN for viewing records outside admin area (these SELECTs are IMHO prevalent).

[RCD24]

Ok, someone please tell me what isn't feasible with my method shown where an identifier categorizes if the content is live, drafted, or previous versions? Everything stays in one table, so no joins are needed, no need for any complicated update queries. simple. period. Unless I'm missing something

[SJH]

Thanks for your input guys. I think Cups' suggestion pretty much hits the nail on the head. I'm going to have a play with this idea and maybe a couple of others over the next few days/weeks and I'll try and post back with an update to let you know how I get on.

Thanks again everyone!