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


"Matthias Klaey" <mpky@xxxxxxxxxxx> wrote in message
news:5bsi53h8bt88nf7iaj0tpk6e3rbjkv9mh8@xxxxxxxxxx
Cimode <cimode@xxxxxxxxxxx> wrote:

On 27 mai, 05:16, paul c <toledobythe...@xxxxxxxx> wrote:
Matthias Klaey wrote:
Cimode <cim...@xxxxxxxxxxx> 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.


.

Relevant Pages