Re: What are the design criteria for primary keys?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Fri, 03 Sep 2010 11:46:50 -0300
Message-ID: <4c810a66$0$11851$9a566e8b_at_news.aliant.net>



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.

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. Received on Fri Sep 03 2010 - 09:46:50 CDT

Original text of this message