Hi group,

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

Generally, there is noting wrong with composite primary keys. The PK is the
PK. The real argument is whether or not to use a surrogate (artifical) key
when you have a perfectly good natural PK. Some will argue for the surrogate
key, others for the natural key. I prefer the natural key because it is
easier to follow if you need to do any debugging or report creation, but
that is just a preference on my part.


"Kenny-Z" <reply (AT) group (DOT) please> wrote

That statement makes no sense. A key is –- by definition -– a subset
of the attributes of an entity that uniquely identify that entity.
You discover them in the reality that you are modeling; you do not
play god and crate the universe. Failure to put a UNIQUE constraint
on a real key means your model and the reality do not match.

If you use longitude and latitude to locate a place that is a fact in
the real world, not a choice you can change on your own whim.

attribute to function as a PRIMARY KEY, is there a name for this type
of key/attribute? (Such an attribute has no actual meaning
externally)...<<

I did an article on this kind of thing in INTELLIGENT ENTERPRISE on
the website. If the new column (it is not an attribute) is created by
the system and hidden from the user, then it is a surrogate key.
Example: my SQL hashes (longitude, latitude) in a special index I
don't know about.

I also discussed things like auto-numbering that are exposed to the
users and how dangerous they are.


No, you need to make the keys longer and longer as you go down the
hierarchy o ri t will break.

CREATE TABLE Proposals
(proposal_nbr INTEGER NOT NULL PRIMARY KEY)

CREATE TABLE ProposalOptions
(PRIMARY KEY (Proposal_nbr, proposal_option_id)
FOREIGN KEY proposal_nbr REFERENCES Proposals(proposal_nbr))

CREATE TABLE Proposal_Option_Items
(PRIMARY KEY (proposal_nbr, proposal_option_id,
proposal_option_item_id),
FOREIGN KEY proposal_nbr
REFERENCES Proposals(proposal_nbr),
FOREIGN KEY (proposal_nbr, proposal_option_id)
REFERENCES ProposalOptions (proposal_nbr, proposal_option_id)

Etc.

Now you need to see which constraints are redundant and where you want
to put DRI actions.

That statement makes no sense. A key is –- by definition -– a subset
of the attributes of an entity that uniquely identify that entity.
You discover them in the reality that you are modeling; you do not
play god and crate the universe. Failure to put a UNIQUE constraint
on a real key means your model and the reality do not match.

If you use longitude and latitude to locate a place that is a fact in
the real world, not a choice you can change on your own whim.

attribute to function as a PRIMARY KEY, is there a name for this type
of key/attribute? (Such an attribute has no actual meaning
externally)...<<

I did an article on this kind of thing in INTELLIGENT ENTERPRISE on
the website. If the new column (it is not an attribute) is created by
the system and hidden from the user, then it is a surrogate key.
Example: my SQL hashes (longitude, latitude) in a special index I
don't know about.

I also discussed things like auto-numbering that are exposed to the
users and how dangerous they are.


No, you need to make the keys longer and longer as you go down the
hierarchy o ri t will break.

CREATE TABLE Proposals
(proposal_nbr INTEGER NOT NULL PRIMARY KEY)

CREATE TABLE ProposalOptions
(PRIMARY KEY (Proposal_nbr, proposal_option_id)
FOREIGN KEY proposal_nbr REFERENCES Proposals(proposal_nbr))

CREATE TABLE Proposal_Option_Items
(PRIMARY KEY (proposal_nbr, proposal_option_id,
proposal_option_item_id),
FOREIGN KEY proposal_nbr
REFERENCES Proposals(proposal_nbr),
FOREIGN KEY (proposal_nbr, proposal_option_id)
REFERENCES ProposalOptions (proposal_nbr, proposal_option_id)

Etc.

Now you need to see which constraints are redundant and where you want
to put DRI actions.