Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Thanks

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
1  
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. –  Jon of All Trades Sep 13 '13 at 23:01
1  
You can enter another version with increased version_number, but smaller version_date. Would you want to prevent this? –  A-K Sep 22 '13 at 3:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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