SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Monday, June 12, 2006

Named Mistakes and Questionable Practices

SQL Apprentice Question
Has anybody here made a list of canonical mistakes[1] that DB
practitioners make?

One example mistake is the One True Lookup Table aka the
Entity-Attribute-Value table, as in this article:
<http://www.dbazine.com/ofinterest/oi-articles/celko22>. Doubtless
people have run across others. Ideally, such references would:

* Name the mistake,
* Describe the mistake's implementation, and
* Show how and under what circumstances it's a mistake, as the above
article does.

Thanks in advance for any hints, tips, or pointers :)


[1] Let's exclude, for the purposes of this discussion, the idea that
SQL itself is a giant mistake and that we should now be using
VaporWareTransRelationalDBMS™--vague, questionable patents
filed--which will eventually be a product some day. We can say that
that horse has already been beaten well enough and is already
deceased, if it makes people happier.

Celko Answers
>> Has anybody here made a list of canonical mistakes that DB practitioners make? <<

Not really, but we do have a few:

1) EAV = Entity-attribute-vaue
2) OTLT = One True Lookup Table aka MUCK (Massively Unified Code Keys)
3) Integrity in the Apps
4) Denormalized tables
5) Auto-increment methods for keys
6) No keys at all

>> I presume this is considered a mistake only because of SQL allowing duplicate "rows". In the real RM, no such mistake is possible. <<

No, we would have Dr. Codd's "Degree of Duplication" operator that
nobody mentions. This is how he handled dups in RM II. At least you
can avoid dups in base tables in SQL via constraints and in results via

Teradata used to disallow duplicate rows in its early versions, but
lost out to Standard SQL. My wish is that we had put in a [CREATE |
DECLARE] FILE () construct for the non-tables. But
SQL was built on the existing indexed file systems of the day, and we
are stuck with the family legacy (aka "family curse").

No comments: