On Thursday 2006-06-08 05:48, dananrg@yahoo.com wrote:
> What am I missing? Why use a composite key *ever* aside from
> "familiarity?" Could someone give a real-world example where
> "familiarity" is a compelling reason to choose a composite PK, and
> trumps stability and simplicity?
Another "familiarity" translates into "self-documentation" and thus is a major
software engineering desideratum. For some designers that might be reason
enough to use a composite key.
Using a surrogate key is arguably not a gain in simplicity. It adds a column
to the table design. It is populated with a non-intuitive sequence number.
The table now has a surrogate primary key and the alternate composite key.
The only gain in simplicity is for some machine operations.
Furthermore the surrogate key allows pseudo-uniqueness. If composite key over
rows ABC has two identical values, abc and abc', the composite key must still
have a unique constraint (trading back much efficiency that might be gained
with the surrogate key) to insure that the identical values are not masked by
the primary key constraint on the surrogate key.
Likewise, the stability provided by a surrogate key is arguably illusory. If
N is the primary key and the values in composite key ABC change then the
surrogate key N simply masks poor design. If ABC is not stable then the
initial analysis was flawed and ABC was not a valid candidate for a primary
key.
N only provides stability if the contents of ABC change in such a way that ABC
remains unique.
> Stability seems to be the single-most important factor to consider. If
> the database can't uniquely identify a row, what's the point? Choosing
> a surrogate key guarantees stability.
>
> Dana
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly