Adding Row Level Versioning

[Mark Jerde]

I have a 6-table, 1 MB MS Access database that generates a 149-page section
of a Word document. I expect this to grow to 500+ pages. I'm considering
upsizing to SQL Server 2000, hence the cross post. ;-) The tables are
normalized; data is not duplicated.

I would like to extend the schema to enable row level versioning of all 6
tables. For example, I expect to publish the first draft of the Word
document this Friday. In the future I would like to be able to regenerate
the section as it existed on Friday. I would also like to have "label"
capability similar to MS VSS.

For now, I don't care about "which user" issues or modifying existing
records. One way to handle it is to have all the "versions" in the existing
6 tables, each modification will add a new row, and the highest AutoNum ID
for the group of the "same" versioned rows is the latest and greatest.
Labeling is then just saving the IDs of of the most recent row in each group
in all the tables. Another strategy is to have the 6 tables always have the
latest & greatest, and store previous versions in "archive" tables. This
makes some things easier, but I see labeling and producing prior versions
more challenging. There are probably other strategies as well.

ISTM this has to have been figured out before, such as for document
management systems. But so far I haven't found anything in books or on the
www. If someone will post helpful links and/or wise advice I will very
grateful. Why reinvent the wheel?

Thanks.

-- Mark

 

[Simon Kissane]

Hi,

The solution I have used before when faced with this problem is as
follows:

1. I use a "Revision" table for all the objects. This includes
information such as:
- ID of revision (unique per revision)
- ID of object (unique per object)
- type of object
- type of revision (new/update/delete)
- date
- by user
- status (approved/not-approved/etc...)
- approved by user
- comments by approver
- etc...

2. For each type of object, I have a separate revision table,
containing the data for each revision, along with the revision ID.

3. I have an ApprovalStatus table which indicates the latest approved
revision of each object. Triggers on the Object Revision table update
this whenever a revision is submitted or approved.

Cheers
Simon Kissane

 

[Mark Jerde]

Hi,

The solution I have used before when faced with this problem is as
follows:

Thanks.

-- Mark