"code" tables?

The following is a message from a fellow programmer to a group of use that
are involved in table design:

"We are planning on creating 'account opening sources' lookup table to store
'account source' codes and corresponding descriptions. Problem with storing
this data in a dedicated table is that as we go on we'd end up with tens and
possibly hundreds of lookup tables.

I suggest that instead we create a more generic table that stores various
codes used within a schema, along with corresponding description and a code
'category'. Another table (optional but recommended) would contain
'category' descriptions.

This is not my idea, I've seen this approach used at other companies.
Please see attached for an illustration.
Let me know what you think."

I'd like to know how other companies do this. Do you have a separate table
for each set of codes or do you have one table with basically three columns:
code_category, code_value, code_description (or just catagory, value,
description)?

Thanks for any insight.

Frank

Frank Swarbrick wrote:

This is not my idea, I've seen this approach used at other companies.
Please see attached for an illustration.
Let me know what you think."
>
I'd like to know how other companies do this. Do you have a separate table
for each set of codes or do you have one table with basically three columns:
code_category, code_value, code_description (or just catagory, value,
description)?

The "one big Entity-Attribute-Value table" is a well-known DB
anti-pattern, i.e. a common mistake.

It _might_ make sense sometimes, but most of the times it doesn't.

>I suggest that instead we create a more generic table that stores various codes used within a schema, along with corresponding description and a code 'category'. Another table (optional but recommended) would contain 'category' descriptions. <<

The name of this design fallacy is TLT (" True Lookup Table") or
MUCK ("Massively Unified Code Keys"). You can Google around more
details or read Section 4.4 of THINKING IN SETS: AUXILIARY, TEMPRAL
AND VIRTUAL TABLES.

>This is not my idea, I've seen this approach used at other companies. <<

Me, too. I charge them $1K-2K per day to clean up stuff like this.

>For instance, the concern about someone adding a code_value that is a lot of whitespace terminated by a single space. Unless you are using a CHAR(1) field [sic: column] this is always a possibility. <<

"build it and they will come" of the evil things I do is load the
Heart Sutra into huge NVARCHAR() columns when I stress a design. If
they will not learn RDBMS, they will have a chance for
Enlightenment :)

>I'm not sure what the maximum length of the field [sic] has to do with how likely the occurrence would be. Maybev you have some sort of statistics on this? <<

Since I just started a book on industry standards, most are fixed
length strings under 25 characters -- boy, is that a generalization!

>Humans have the nearly unique ability to learn from others mistakes. You are not suggesting the NLY way to learn is to make mistakes are you? <<

If you want to learn from mistakes, let them be someone else's
mistakes! Much cheaper that way.

David Cressey wrote:

I can't tell you the number of times programmers have come to me for help
with a query
that produces wrong results, and when I try to change their "select" to
"select distinct" to see if maybe that's the problem,

Hey, wait a minute.
Usually, it's the other way round.

Some people _always_ run DISTINCT because they are not able to "remove
duplicates" :-)


I patiently explained to them that I first want to come up with a query that
is logically correct, then if necessary, come up with one that's logically equivalent, but
runs fast.

If "fast" comes first, any program will take longer to be "correct" and
will seldom ever be "clean".

>When I see SELECT DISTINCT in anything but an ad hoc query, I know something is wrong with the query. <<

I know what you are saying, but I regard this and a few other things
as symptoms of bad SQL, not absolute proof of it. I look for a CRSS
JIN that wasn't supposed to be there. But SELECT DISTINCT can be
valid.

symptoms are the use of Rs when an IN() would work. Likewise,
failure to use BETWEEN is another symptom. These tell me he has not
un-learned his original procedural language.

>I can't help wishing that the optimizer could figure it out. But that's probably way down on the list of things an optimizer should be able to do. <<

Actually, it is not too hard to detect in many cases. If the result
set has a key in it without a cross join, then the rows are UNIQUE.
Perfect hashing and bit vector indexing can also spot and reject
redundant duplicates during processing.