Re: What are the design criteria for primary keys?

From: paul c <toledobythesea_at_oohay.ac>
Date: Sat, 04 Sep 2010 02:10:03 GMT
Message-ID: <%Thgo.502$89.415_at_edtnps83>



On 03/09/2010 6:39 PM, Brian wrote:
> 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.
> ...

Not ridiculous at all. I've seen keys that were intractable even for the programming consultants who were paid by the hour (alone with db 'records' that had 500 fields!).

One requirement to do with airline shipping that I remember had to do with shuffling the record of multiple parcels listed on multiple airway bills at the last minute before a plane took off because the cargo master had judged that the weight and balance load was dangerous. This had to be done in seconds because of the costs of delaying the use of the plane's gate and other reasons. Because of concurrent conflicts with other db activity, it was important to be able to guarantee these kinds of changes could be made within several seconds. There were also robotic warehouses involved for which realtime notice had to be sent to scheduling process machines. The 'natural' key had at least ten attributes and huge complications were involved among the dbms' concurrency and recovery components - they were unplannable. The only practical solution was a phony key, surrogate if you will, that referred to a non-existent 'virtual' storage location. The inspiration for the solution came from existing airline flight system practice, dating from the 1960's, that invented an abstraction called flight segments which had solved the practical difficulty of using the natural key for a plane that made nine stops in a twenty-four hour day.

Perhaps you have dealt with the IATA regulations. I know for a fact that many of them are composed during junkets to Montreal and that many of the contributors are not only incompetent about their business in the first place but are also drunk when the regs are written. Never mind the politics and the national and inter-company rivalries (db fans know what damage that has done to the sql standard). By comparison even the nuttiest IT ideas typically undergo much more critical scrutiny. So personally I don't necessarily buy any standard, no matter how powerful the sponsoring organization.

Somewhere in this thread, Bob B acknowledged the above kind of band-aid. Received on Fri Sep 03 2010 - 21:10:03 CDT

Original text of this message