Should I set document version field to be part of a primary key

Question

Should I set document version as a primary key I am working on a document management system, and I have these two tables representing part of the whole tables.

On the DocumentVersion table I have set the primary key to be (version + documentID) . so can anyone advice if this is a recommended approach where the documentID is of type int while the version is of type decimal(5,2) ? or it is better if I create a new field and set it as the primary key inside the DocumentVersion table? Thanks

Answer 1

The DocumentVersion table makes sense to me. The two columns well define the Document by the DocumentID relationship to the Primary Key on Document and the Version makes a unique entry for DocumentVersion. (Just as the name implies.)

There is no problem having a primary key that included two different data types, so an INT and a DECIMAL(5,2) will coexist happily in the same Primary Key.

What you do want to avoid is having a join between mismatched data types, such as joining a VARCHAR(10) with an INT. This will likely result in problems with the join performance even when all the data is clean. (And, of course, a VARCHAR can hold strings that would not convert to INT which would be another class of problem.)

EDIT: Depending upon your usage, a surrogate PK column DocumentVersionID could be beneficial for other tables to reference. However, for integrity of tracking the versions, the DocumentID,Version should at least be enforced by a UNIQUE CONSTRAINT.

E.g. ... CONSTRAINT Unique_DocumentVersion UNIQUE (DocumentID, Version);

Answer 2

While there is nothing wrong with your schema, personally I would create a separate DocumentVersionId column for similar reasons to those listed in Why should I create an ID column when I can use others as key fields?:

• Joins based on a single integer are more efficient than those involving multiple columns. Your schema doesn't show any other tables joining to DocumentVersion, but to me it seems fairly likely that you will want to add one eventually.
• An auto-incrementing DocumentVersionId is a good choice for a clustered index.