If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Adding Row Level Versioning
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
Simon Kissane wrote:
Hi, The solution I have used before when faced with this problem is as follows: Thanks. -- Mark |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Security Level | Vicki | General Discussion | 1 | August 26th, 2004 12:09 AM |
Word Password Encryption Level | Paul | General Discussion | 0 | June 11th, 2004 09:21 PM |
Creating a drawing for low level data communication messages (byte level) | Allan McLellan | Visio | 1 | May 17th, 2004 03:25 PM |
I can't change the macro security level | Julie | General Discussions | 3 | May 6th, 2004 03:53 PM |