Thread: Re: Any *real* reason to choose a natural, composite PK over a surrogate, simple PK?

If one decides to use a composite key, beyond how many attributes
should one seriously consider creating a surrogate key instead? 4? 5?
Less? I have seen a composite key composed of 5 attributes and thought
- why? What's the value over a surrogate key?

I guess choosing a candidate key (presuming the candidates are
legitimate candidates) is at least partially a matter of taste. What
taste would compel people to choose composite keys composed of more
than 2-3 attributes?

A bit simplistic, but in a nutshelll....


They are used for different things. A natural PK is used for data
integrity purposes, and if it is a single attr, it can also be a
performance enhancing index.

An artificial key is used to speed up queries, by allowing joins on a
single indexed attribute.

So use your composite, data driven, unique index as an integrity rule to
prevent duplicates, and use an artificial PK to enhance performance on
queries & joins. When you need to enhance performance is largely up to
you, unless you require your design to always maximise efficiency.


HTH,

 Brent Wood