Re: What are the design criteria for primary keys?

On 02/09/2010 9:20 PM, Bob Badour 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!

Same goes for most sites to do with db, I'd say. These five are typical of most of those in IT, eg., any of inability to abstract, to focus, considerable ability to produce so much verbiage that the overall meaning becomes vacuous, in other words over-developed talent for obfuscation combined with a protective herd mentality that shouts down the occasional sane minority voice which threatens the courtiers by pointing out that the Emperor has no clothes. Apparently it's not illegal to use whatever steroids improve the obfuscation faculty.

I believe that the coherent, concise answer (which you have given before here I think) could be assessed by any non-IT person who has had an education that cultivated a modest level of literacy.

Ever since I first saw it mentioned I've especially liked 'familiarity' but it really surprises me when people compare that with 'not-invented-here'. If one has never seen a particular system, everything, with a capital E, is not familiar but soon becomes so. Also, I don't see anything wrong with every relation having two or more candidate keys, the ones that aren't primary being very useful connections to useful features that are outside the RM, but innate to most physical implementations, such as ordering. No matter what of several definitions one uses for 'surrogate', they will soon become familiar. Celko wants everybody to use published keys, sometimes that's advantageous but it's not essential, after all the published keys one has never seen before aren't familiar before one adopts them. Part of the power of the Codd's RM (pick any version) is that it's open-ended about such bells and whistles. Received on Fri Sep 03 2010 - 22:48:40 CEST