0

Hi,

I'm fairly new to databases and database design - I'm wondering the best way of implementing a method of versioning a record.

I'm designing a database for a simple document management application. The database has users who create and edit documents as well as adding comments to the docs. When a document is updated, I would like the old version to be stored. There will be an option to recover an old version.

I have a possible solution:

User - id, first_name, last_name, etc...
Primary key: id

Document - id, version_number, file_name, description
Primary keys: id, version

Comment - doc_id (foreign key), comment_number, comment_by_user (FK)
Primary key: doc_id, comment_number

Is this a good solution? All the documents and their old versions are stored in the same table. To locate the the current document you would use version 0. The comments are non-version specific, which I think right.

Any comments welcome, thanks.

3
Contributors
3
Replies
4
Views
11 Years
Discussion Span
Last Post by pty
0

hi - i know this is a bit late and you've probably sorted out this issue

my instinct would be to have a documents table and a revisions table

documents: id, title, summary.

revisions: id, document_id, revision_number, file_location, uploaded_by, uploaded_ts...

so when you add a new document, for example holiday_form.pdf, a new document tuple is added and a version 1 (or whatever) revision is added.

you could create a folder per document and store the revisions - i've done this in the past by naming the revisions:
<document_name>_v<revision_number>.pdf

0

Timestamps are a good way to keep track for versions but I hate the way things could mess up with timestamps.

0

Timestamps are a good way to keep track for versions but I hate the way things could mess up with timestamps.

how could things mess up with timestamps?

i dont actually use the timestamp for versioning more for auditing; its the version number that keeps track of the revision of the document

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.