On Thursday 2006-06-08 05:48, dananrg@xxxxxxxxx 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@xxxxxxxxxxxxxx so that your > message can get through to the mailing list cleanly
