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.