Re: Stupid Database Tricks

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 23 May 2007 12:20:49 -0300
Message-ID: <46545b84$0$4050$9a566e8b@news.aliant.net>

hasta_l3_at_hotmail.com wrote:

> On 22 mai, 22:48, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>

>>Every table will have a numeric id column, and this
>>column will be the primary key.

>
> Dear Bob,
>
> I'm pretty new to this group, and thus not aware
> of the previous discussions on this subject.
> Please pardon what could be a FAQ.
>
> I am of the opinion that a table modeling
> things out there in the "real world" should
> have a synthesized primary key column,
> mainly because the mapping between the
> database row and the "real" thing is usually
> pretty complicated, or turns out to be, sooner
> or later.
>
> That primary key should never be shown to
> the user.
>
> Of course, the rule does not apply to all relations.
>
> If this is the position you think is a stupid trick,
> I would be very grateful to have a reference
> explaining why it is a mistake.
>
> Thanks much for your time
>
> --- Raoul

Thank you for your question Raoul. I cannot give your question an adequate response in a usenet message. Instead, I refer you to Fabian Pascal's _Practical Issues..._ book.

The design criteria for candidate keys are: uniqueness, irreducibility, simplicity, stability, familiarity. Sometimes the criteria contradict one another requiring the designer to make tradeoffs. Sometimes the familiar unique irreducible key is not stable enough or simple enough for practical use -- leading a designer to introduce an arbitrary simple stable attribute to act solely as a key. Please note that doing so is a thoughtful design process.

I find any argument that resorts to "the real world" suspect. A dbms is a formalism for symbolic manipulation. The utility of the formalism depends on a number of factors including the appropriateness of the design, the accuracy of the data and the correctness of the derivation rules.

I ask you to contemplate the significance of "The Information Rule" ie. that the dbms will represent all information as explicit values in relations. If the user never sees some of the values, then the user can never use them. Is using the information not the fundamental role of a user?

Consider as well why familiarity is a design criterion for candidate keys and how that relates to the information rule. Received on Wed May 23 2007 - 10:20:49 CDT