Re: What are the design criteria for primary keys?

From: Bob Badour <>
Date: Fri, 03 Sep 2010 20:31:27 -0300
Message-ID: <4c81855b$0$11836$>

Cimode wrote:
> On 3 sep, 22:48, paul c <> wrote:

>>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 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.
>>>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
>>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.
> For a change from the usual sterile rantings about current state of
> contemporary database design normative habits, and to play somehow
> devil's advocate, I'd have to say that the concept of *key* is almost
> orthogonal to RM.  After all, the concept of *keys* mostly embodies
> the IBM context in which RM was theorized by Codd rather than the
> logical concept of unique tuple identifier in an algebraical sense.
> Additionally, defining quasi metaphysical subjective criteria such as
> *familiarity* somehow sounds wrong as opposed to the rigor of the
> fundamental mathematics that are supposed to act as a foundation for
> RM structural information representations.  For some reason, I noticed
> such criteria is oftent an open door to diversion from the concept of
> *distinguishability* (which at least can be expressed algebraically)
> which seems more important than others.
> Finally, I'd have to say that looking for database theory definition
> into a board, we know is filled by undeducated audiences is at best
> pointless, at worst, sterile.
> In conclusion, I think uniqueness (I'd add distinguishability),
> irreducibility, stability are more useful for definition rather than
> *simplicity*, *familiarity* since the last two can hardly be
> formalized.

One cannot formalize elegance, and yet we can recognize it:

If we can recognize it, we can use it for subjective comparison. Received on Fri Sep 03 2010 - 18:31:27 CDT

Original text of this message