Re: Code tables, conditional foreign keys?

On 23/05/09 06:05, Rodrigo E. De León Plicet wrote:
>>> Is there a better way?
>>
>> Yeah, natural keys.
>
> +1.
>
> Also, what Ben described reeks of EAV.
>
> Ben, please read:
>
> http://joecelkothesqlapprentice.blogspot.com/2006/04/using-one-table-vs-many.html

Ah yes, the great Celko. I had the honor of being "lectured" by him on a
similar topic, and to tell the truth, I'm still unconvinced. I had
inherited a generally well-designed database with a clear relational
structure, apart from one table about which I was uncertain. This table
collected several groups of attributes/values that were not necessarily
related to each other, and could (should?) be split into separate
tables. It was nowhere near as bad as the example in the linked article,
and we did have check constraints in place. The values were used to
classify and sometimes "tag" rows in other tables. I hesitated to break
this table up, because that would have meant

- doubling the number of existing tables (70 to 140)
- significant code changes all over the place
- having a lot of very small tables with identical layout
"id" (surrogate key)
"entry" (string; used to refer to this row by the application)
"label" (string; visible on the front end)

Here's an example of the value groups that were contained in the table:

fax status:
pending, active, sent, error
department:
office, accounting, it, legal, experts
deadline type:
official, unofficial
...

Using the "entry" field as natural keys would have been possible in some
places, but not everywhere, and the labels still had to be editable by
the users. Some of the "entry" strings were rather verbose (>40
characters), which would have made the other tables a lot larger. It
also didn't "feel right" to have so many tiny tables with only 2-5 rows.

Is it really advisable to put all these values into 70 separate tables
with the exact same layout? I don't quite see the benefit.

- Conrad