Data versioning for database entity?

I'm looking for some help on how to design a schema that covers data
versioning on a database entity.

For example: there is a requirement for a database to store details of
a company's products for all staff to view. The 'product' entity
comprises of a main product table and multiple subordinate data tables
that hold various properties and sub properties of a product's
profile. In otherwords there are multiple linked tables that in total
hold all of the data associated with a product.

This product information has to move through a authoring, submit,
review and approve cycle. Eventualy when the product info is approved
it is available for all users to see.

The product information authors can subsequently post updates/
revisions to the original product data. Yet whilst the updates are
being submitted and approved the original product data has to be
available for all users to view. Hence, the database schema must be
able to simultaneously store the original product info as well as the
updated info which is going thru the authoring, submit and approve
cycle. Eventually when the revisions are approved the original data is
replaced with the revised data, so that all users now only see the
revised data.

In thinking of hiow to design a scheme to address this requirement I
am split between using...
* Separate tables to hold the public approved data and the yet to be
approved revision data; or
* Having all the data in a single table with a sub key to identify
public versus private

Thoughts?

On 31.08.2008 14:18, davout wrote:
> I'm looking for some help on how to design a schema that covers data
> versioning on a database entity.
>
> For example: there is a requirement for a database to store details of
> a company's products for all staff to view. The 'product' entity
> comprises of a main product table and multiple subordinate data tables
> that hold various properties and sub properties of a product's
> profile. In otherwords there are multiple linked tables that in total
> hold all of the data associated with a product.
>
> This product information has to move through a authoring, submit,
> review and approve cycle. Eventualy when the product info is approved
> it is available for all users to see.
>
> The product information authors can subsequently post updates/
> revisions to the original product data. Yet whilst the updates are
> being submitted and approved the original product data has to be
> available for all users to view. Hence, the database schema must be
> able to simultaneously store the original product info as well as the
> updated info which is going thru the authoring, submit and approve
> cycle. Eventually when the revisions are approved the original data is
> replaced with the revised data, so that all users now only see the
> revised data.
>
> In thinking of hiow to design a scheme to address this requirement I
> am split between using...
> * Separate tables to hold the public approved data and the yet to be
> approved revision data; or
> * Having all the data in a single table with a sub key to identify
> public versus private
>
> Thoughts?

Both are viable options. You can even make user's life simpler with
option two by defining a set of views that always present the current
valid set of data only.

It probably depends on other factors which solution you pick. For
example, if you have a lot of versions you will end up with tables with
only a small amount of current data vs. a large chunk of historic and
future (unapproved) data. In that case it might be more efficient to
separate them table wise. On the other hand, this will make version
updates more complex as you have to copy data between multiple tables
probably also honoring foreign key constraints.

By separating current and historic data into separate tables (or maybe
even partitions of a table) you might be able to exert more control over
how data is accessed by reserving IO bandwidth to the current data.

I am sure there are a lot more things to ponder...

It is quite likely that there are products around that do exactly what
you need. For example, if you want to set up a web site with product
data then I am pretty sure you'll find a content management system that
will fit your needs. Approval processes, publishing dates and
permissions should be concepts found in a lot of those systems.

Kind regards

robert