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
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...
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.