Keeping versions of a record

[djfriendly]

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.

[pty]

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

[aniseed]

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

[pty]

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