"code" tables?

#1

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

#2

Frank Swarbrick wrote:

Quote:

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.

#3

Frank Swarbrick wrote:

Quote:

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.

#4

Frank Swarbrick wrote:

Quote:

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.

#5

Quote:

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 OTLT ("One 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, TEMPORAL
AND VIRTUAL TABLES.

Quote:

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.

#6

Quote:

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 OTLT ("One 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, TEMPORAL
AND VIRTUAL TABLES.

Quote:

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.

#7

Quote:

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 OTLT ("One 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, TEMPORAL
AND VIRTUAL TABLES.

Quote:

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.

#8

Quote:

On 6/19/2008 at 2:57 AM, in message
Cbp6k.4005$Qu5.1469 (AT) tornado (DOT) fastwebnet.it>, Marco

Mariani<marco (AT) sferacarta (DOT) com> wrote:

Quote:

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.

That was my thought. I will try to discourage them from this path.
Thanks!

Frank

#9

Quote:

On 6/19/2008 at 2:57 AM, in message
Cbp6k.4005$Qu5.1469 (AT) tornado (DOT) fastwebnet.it>, Marco

Mariani<marco (AT) sferacarta (DOT) com> wrote:

Quote:

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.

That was my thought. I will try to discourage them from this path.
Thanks!

Frank

#10

Quote:

On 6/19/2008 at 2:57 AM, in message
Cbp6k.4005$Qu5.1469 (AT) tornado (DOT) fastwebnet.it>, Marco

Mariani<marco (AT) sferacarta (DOT) com> wrote:

Quote:

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.

That was my thought. I will try to discourage them from this path.
Thanks!

Frank