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.

enter image description here

I would greatly appreciate any help and advice.


1 Answer 1


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

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

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:

  id (PK)
  creator (FK to users.id)
  (other metadata)

  id (PK)
  document_id (FK to documents.id)
  (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.

  • 2
    I'd actually recommend the first version, as it allows for a document's title and creator/owner to change. Consider an IsCurrent bit field, it makes querying simpler. Sep 13, 2013 at 23:01
  • 2
    You can enter another version with increased version_number, but smaller version_date. Would you want to prevent this?
    – A-K
    Sep 22, 2013 at 3:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.