Thread: Document Table Record Versioning

Thank you, Brett Kaiser, blindman, MarkATrombley and Laurent for your kind replies. You all have been consistent in saying that a single table would serve my purpose best. So here's the key document table columns that I am looking at right now: documentkey (unique) documentnum activefrom activeuntil version (optional) latest (default true; optional) Thanks for all your help guys, Joe

Quote: So here's the key document table columns that I am looking at right now: documentkey (unique) documentnum activefrom activeuntil version (optional) latest (default true; optional) Few questions : Surely the latest field is unnecessary as you'd just look for either look for records where the current date is between the from and until dates. If the field defaults to true does this mean you might have more than one record with...

Something is yet unclear to me in the desired behavior: what you want is: -- a stack, i.e. returning to version n-2 will throw away n and n-1. -- an history, i.e. returning to version n-2 deletes nothing but "duplicates" n-2 to n+1 Mike's design (and my contribution) apply to a stack. Mark's is more about an history. And your final design seems to me a mix of the two ! If an history is wanted, I would use two...

Wikipedia has a decent article on temporal database design. Usually you would add a ValidStart and ValidEnd datetime columns to reflect when the row was current. The current record has a ValidEnd datetime of some specific date in the distant future so it is easy to find. If you are going to have a huge number of document revisions (more than 20 per document) then I would go with an archive table, otherwise keeping it in one table is best.

Normally I would recommend an archive table, but in your case records are not being "permanently archived", and can be change back to an active state. So, the single table may be your best option. Note though, that a single table with start and stop columns will not enable you to determine the history of when a document was in use (if it had multiple start and stop times), while an archive table would.

OK, I would have a Document table, With Effective and Termination Dates (null), with a version Number for the document that is incremented by 1 any time the document was modified (via a trigger) I would also move all data images to a history table (via a trigger) when anything on the row was modified MOO