Re: Little design mistakes that can be easily avoided (2): Listenning to CELKO (and CELKO alikes)

"Matthias Klaey" <mpky_at_hotmail.com> wrote in message news:5bsi53h8bt88nf7iaj0tpk6e3rbjkv9mh8_at_4ax.com...
> Cimode <cimode_at_hotmail.com> wrote:
>
> >On 27 mai, 05:16, paul c <toledobythe..._at_oohay.ac> wrote:
> >> Matthias Klaey wrote:
> >> > Cimode <cim..._at_hotmail.com> wrote:
> >> > ...
> >>
> >> > Hmm. Is this just the usual Celko-bashing in this newsgroup? Did you
> >> > intend to write a parody on how to misread and misinterpert other
> >> > peoples texts? You don't mean this seriously, do you?
> >>
> >> Usually I think much of what he writes here deserves to be bashed as it
> >> is not about theory, just various flawed products, workarounds and
> >> various design gospel he likes to preach. I think it's okay to talk
> >> about that stuff in a product group but this is a theory group.
> >Agreed. When one wants to speak about *taxonomy of keys*, one ought
> >to bring specific and sound scientific logic argumentation.
> >Disseminating product information with wax of science is not the same
> >as science. It hurts both science and truth.
>
> Please, Cimode. In Celkos text on the taxonomy of keys there is *not a
> single* reference to a database product.
>
> He does advertise his own work, but this doesn't bother me, just as
> Bob Badours permanent swearing doesn't bother me much either.
>
> As for the *content* of Celkos text and its relation(!) to database
> theory (short of copying large parts of a book on this theme, I am
> speaking colloquially here):
>
> 1. In the Relational Model, the key is part of the definition of a
> relation (= table in practice). You don't have a relation if it
> doesn't have a key.

I differ with the wording above. Here's an alternate wording: if you have a relation, the tuples will be distinct, by definition. This means that the entire tuple is either a candidate key or a super key. To discover candidate keys, one merely needs to discover the constraints placed on the data.

>
> 2. Theory never talks about *how* you obtain this key. It is just
> assumed that it exists. It talks about candidate keys, primary
> keys and other mathematical concepts that apply here.
>

OK, so theory never talks about this, but I will: data analysis.

> 3. Celko classifies the kinds of keys that are used in practice.
> Taxonomy is a science in its own right. His text is more of an
> expose, it could easily be expanded to twenty pages or so. But his
> conclusions nevertheless are valid and sound (a little bit expanded
> by me):
>

I think this is a useful thinig to do, in the context that Joe originally did it in.
Design practices, both best practices and others, sometimes elucidate theory, sometimes not.
It's only when theory and alleged best practices contrdict each other that further investigation is necessary. (remember, "Theory IS practical")

> 1. First, look for a natural key. If necessary, normalize your
> tables, because if you don't find such a key, this is almost always
> a sign of badly designed tables.
>

In practice, key discovery and normalized design are almost always parallel processes.

> 2. If you don't find a natural key, construct your own artificial key.
>

Before resorting to an artificial key, it's useful to push back on the keepers of the data as given, to see how they know what they are talking about, in the absence of sufficient natural keys.

> 3. Never ever use "exposed physical locators" such as IDENTITY columns
>
Agreed.

> 4. When you construct a dbms (and only then), you might want to
> investigate surrogate keys, mostly for performance reasons.

In my experience, performance has little to do with it. Received on Sun May 27 2007 - 14:58:24 CEST