How to handle versions of an Entity in Database Design

Question

I am working on a new project. The domain logic of the project is as follows:

A user uploads a file (Document) and gives a title, then another user comes along and (s)he also tries to upload a file with the same title. This is OK providing that the body of the document has differences.

How can I design my domain model so that my web application that will later consume the data can inform the user viewing a particular document "hey there is another version of this document".

Here is a simple ERD of what I have, but that obviously does not perform what I want above.

I would greatly appreciate any help and advice.

Thanks

Answer 1

You could keep them all in the same table, simply with a column to track version numbers:

documents
---------
  id (part of PK)
  version_number (part of PK)
  title
  file
  creator_id (FK to users.id)
  version_date (the date that this version is created - not necessary, but good to have)
  file

Your application will need to be capable of handling updating the version number of a document when a new record is added but it is a new version of an existing document. You also need to ensure that any document metadata (such as Title) changes are propagated to all related documents. You could do this by allowing document ID to not be unique, and the primary key is a combination of document ID and version number. Otherwise, it could be impossible to tell which record to update.

Another way would be to have a stub for document metadata and a separate table for all versions:

documents
---------
  id (PK)
  title
  creator (FK to users.id)
  (other metadata)

document_versions
-----------------
  id (PK)
  document_id (FK to documents.id)
  document_version_number
  file
  (other fields)

The second option is a slightly more complicated structure, but will give you better referential integrity because all version must be linked to a document stub by a foreign key.

Personally, I'd go with the second version.