Re: What are the design criteria for primary keys?

On Sep 3, 10:46 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> Brian wrote:
> > On Sep 3, 12:20 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>
> >>Choosing good *primary keys* and candidate keys is a vitally important
> >>*database design* task--as much art as science. The design task has very
> >>specific design criteria.
>
> >>*What are the criteria?*
>
> >>As an experiment, I asked the above question on StackOverflow.com having
> >>first verified it wasn't already answered on the site. It is a very
> >>important question that has a very simple and clear answer. I had
> >>planned to offer a sizable bounty if nobody gave the correct answer
> >>after the 1st day and answer it myself if nobody claimed the bounty.
>
> >>http://tinyurl.com/ignorancetothefifth
>
> >>Five people identified as John Saunders, David Stratton, Claudio Redi,
> >>wallyk, and rockinthesixstring voted to close it with nothing
> >>approaching a correct answer supposedly because "It's difficult to tell
> >>what is being asked here. This question is ambiguous, vague, incomplete,
> >>or rhetorical and cannot be reasonably answered in its current form."
>
> >>I thought the question was clear enough. The answer, of course, is:
> >>uniqueness, irreducibility, simplicity, stability and familiarity.
>
> >>To anyone who uses that site as a resource, all I can say is: Caveat lector!
>
> > The answer, of course, is:
> > uniqueness, irreducibility, familiarity, and if at all possible,
> > simplicity and stability.
>
> The criteria are what they are. One could just as easily rewrite them as
> "simplicity, simplicy, familiarity, and if at all possible, uniqueness
> and irreducibility" without really changing the meaning of anything.

Now you're just being ridiculous! Uniqueness and irreducibility are necessary: a candidate key is not a candidate key unless it has the uniqueness and irreducibility properties.

>
> They are a list of criteria that sometimes conflict requiring one to
> make design tradeoffs.
>
> > There should be no reason to use surrogates, so I agree on
> > familiarity, but not every candidate key is simple, and while
> > stability is a worthwhile goal, it is not always achievable.
>
> I try not to should on anyone or anything; although, I did make a recent
> exception.
>
> All keys are surrogates. A natural key is merely a familiar surrogate.
>
> While not every candidate key is simple, during database design, one
> still chooses whether to express references with a compound candidate or
> a simpler alternative.- Hide quoted text -
>
> - Show quoted text -
Received on Sat Sep 04 2010 - 03:39:05 CEST