SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do you store multiple states of a record?

    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:

    Code:
    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

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    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.
    Last edited by oddz; Apr 9, 2009 at 20:48.

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2009
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    http://webdevsforum.com/ - New Web Developers Forum
    http://classifiedphpscript.com/ - PHP Classifieds $10

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SJH View Post
    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.

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

  6. #6
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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.

  8. #8
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).

  9. #9
    SitePoint Enthusiast
    Join Date
    Mar 2009
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    http://webdevsforum.com/ - New Web Developers Forum
    http://classifiedphpscript.com/ - PHP Classifieds $10

  10. #10
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •