Hello,

First off, I apologize if this question is in the wrong section, but I see no "beginner" area.

My question is relatively simple though: if I have many different codes/descriptions that my application needs, is it better to keep them all in one table, or split them into multiple ones?

At my previous job, we had one large "code" table for fixed values. Each entry had an id, type, code, and description. We seperated when to use them via the type (for example, purchase order statuses would have a type 'poStat' etc.). At the job prior, we gave each type its own table.

From what I've seen, the first is easier to model and maintain, but I assume that we take a performance hit from merging them all together.

Is there any "best practice" guide to approaching this? Should I always keep them seperate tables, merge them when under a certain number, or just keep them together.

Thanks.

Ah, the OLTP. One True Lookup Table.

I've used both methods, and I've yet to decide which is better. Both have obvious drawbacks. The one from a proliferation of tables and the other from a proliferation of records.

I guess if I had less than 10 lookup tables I would never consolidate them. Above 20 or so I would consider consolidating some of them, but I would be selective and make sure the lookup values conformed to uniform standards.

Blindman kind of mangled his acronym, OTLT is "One True Lookup Table" to me, but he has a good opinion.

A lot depends on which database engine and development tools you are using, and how well the combination of them handle concurrency. If they don't play really well together, I'd strongly recommend separate tables for every lookup... It makes the locking issues a lot easier to handle.

From a pure design perpective, every lookup class should have its own table. They are fundamentally different kinds of data (shoe colors and ski finishes might both be a number and a string, but they are still fundamentally different things).

If you get past both of those considerations, you can consolidate the tables that have similar structures into a OTLT.

I used to love OTLT, and normally had one in every database that I designed. I have now switched back, and almost now I have separate lookup tables for every different kind of thing that I lookup. I only combine lookups when I have more than one type of otherwise identical lookup in the same database.

-PatP

OLTP? Where the heck did I get that? Online Transaction Processing?

Missed my caffeine today...

Please see this entry in my "blog" for my thoughts on OTLT!

Tony's OTLT blog post is one of four good articles that i have bookmarked --

Dave's guide to the EAV

One True Lookup Table by Joe Celko

OTLT and EAV: the two big design mistakes all beginners make

Lookup Table Madness

i am with Pat. I used to use one table (which is a breach of correct normalisation), now I always use one separate Lookup table per class.

I do not believe performance is a consideration in this issue, most modern SQL engines handle either one the same.

Cheers