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.

Will this table design slow down normal searching for records? I guess compared to a table which just had the current documents it would be slower...

Any comments welcome, thanks.

You seem to be missing a table, as your Comment table's foreign key (doc_id) doesn't have a parent PK to reference - it cannot reference part of the 2-col PK of document.

Perhaps it should be more like:

create table document (doc_id primary key, ...);
create table document_version (doc_id, version_number, ... primary key(doc_id, version_number));
create table comment (doc_id references document, comment_number, ...);

This model seems reasonable. The only kind of searches that would be slowed down would be those that cannot use an index to locate the rows they require but instead must perform a full scan of the table. If a typical document has many versions, and a typical search involves scanning the text of only the current versions of each document, then there may be a case for separating current and past versions into separate tables.