HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
fieldprimaryversionpartdocumentshouldsetkey

Problem

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

Solution

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);

Context

StackExchange Database Administrators Q#87452, answer score: 5

Revisions (0)

No revisions yet.