Compound PK or ?

First, is there any general consensus about whether or not compound primary keys are good or bad and in which situations?

Second, if I have a natural candidate primary key that is two attributes (compound), but I create an additional/superfluous single attribute to function as a PK, is there a name for this type of key/attribute? (Such an attribute has no actual meaning externally)...

Last, my particular example in this case looks something like this, in pseudo DDL:

TableName(KeyName[KeyType])

Proposal(ProposalNum[PK])
ProposalOption(ProposalNum[PK/FK], ProposalOptionNum[PK]) PropsoalOptionItem(ProposalNum[PK/FK], ProposalOptionNum[PK/FK], ServiceItemCode[PK/FK])

This is a first take using natural compound PKs (if my terms are correct). I hope the data structure makes some sense without complete DDL: Each Proposal has one or more ProposalOptions; Each ProposalOption has one or more ProposalOptionItems. Each ProposalOptionItem references ServiceItemCode in ServiceItem (not shown).

At this point it becomes tempting to do:

Proposal(ProposalNum[PK])
ProposalOption(ProposalOptionID[PK], ProposalNum[FK]) PropsoalOptionItem(ProposalOptionItemID[PK], ProposalOptionID[FK])

Thanks for any input on this!

Ken Received on Fri Sep 05 2003 - 05:16:26 CEST