I'm working on a content management system, that we have
built in cold fusion for the company I work for.
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
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
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
> 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
worked on a database system where all records where
where never altered or deleted, just newer data replaces
older data with
full accountability. Very similar to what you seem to want
The main idea from that database design that may apply to you
the data table always contained the current record, all
stored in a separate historical archive table. Thus the main
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
so that every table did not need a duplicate archive version
The structure of this archive table was basically recordID,
table, field, oldValue, newValue, timeStamp, whoMadeChange.
Then when any field in a primary table is altered, a record
archive table is generated first then the primary table is