1 Reply Latest reply on Mar 30, 2009 3:04 PM by Newsgroup_User

    Adding version management capabilities

    fober1 Level 1
      I'm working on a content management system, that we have built in cold fusion for the company I work for.

      We now need to add version management capabilities to certain items in the system, and I'm wondering if there's good example or standard/common way to add version management to the "assets" table in the system.

      I cannot move to any finished off-the-shelve-application because the system we have in place is very specialist to our needs, and integrated with several other applications, so I need to expand the existing system.

      What fields do you add to the DB?
      - Instead of just a PK, I'm planning to add a "ID" and "Version" field
      All the records that are versions of the same assets would get the same ID, and then a separate version
      - I expect I need to add a field (bit) for "IsPublished", and a field (bit) for "IsStaging"
      - Then I will add a field for "ChangedDate", and "ChangedBy", and additional a field-reference "CheckedOutBy_FK" to the users table for the person currently editing the record
      - Additionally I will need a field-reference to a separate workflow-table. That table defines the current workflow process step, the next available steps, who can execute these steps, and if they set the Staging or Published attribute for a asset.

      I'm not worried about building the workflow piece in ColdFusion, and the database design above should work in general, but I'm already considering to add a "IsCurrentRecord" attribute because I need that in certain situations, and now I'm thinking instead of coming up with my own concept for the structure, maybe there's already some common way to add version management capabilities to an application, so I don't need to re-invent the wheel.

      I searched Google, and several SQL and Developers forum pages, but I only find thousands of page about how to version management your code, how to use version management systems, and why it is important.
      But I'm not finding anything about database structures for version-management applications.

      Any help is appreciated.

        • 1. Re: Adding version management capabilities
          Level 7
          fober1 wrote:
          > Hi,
          > I'm working on a content management system, that we have built in cold fusion
          > for the company I work for.

          It was not a content management system but at my previous employment I
          worked on a database system where all records where permanent. They
          where never altered or deleted, just newer data replaces older data with
          full accountability. Very similar to what you seem to want here.

          The main idea from that database design that may apply to you is that
          the data table always contained the current record, all changes where
          stored in a separate historical archive table. Thus the main table did
          not have extra rows just sitting around, only need when the history of a
          record is needed.

          Also a well designed archive table can suffice for many main data tables
          so that every table did not need a duplicate archive version of itself.
          The structure of this archive table was basically recordID, sequence,
          table, field, oldValue, newValue, timeStamp, whoMadeChange.

          Then when any field in a primary table is altered, a record in this
          archive table is generated first then the primary table is updated.

          Hope this gives you some ideas.