One or many lookup tables

[klm2klm2]

We're designing an enteprise application. Some of our team members who are experienced with DB2 want to merge all of our lookup (i.e. reference) tables into one table. I normally let every logical lookup table be its own physical table. When using the Oracle database, is there any material benefit to combining all lookup tables into a single reference table?

The major disadvantage that I see of having a combined reference table is that if you want to have columns that contain values about only one type of lookup value, you have to have a meaningless column tied to all other types of lookup values.

BTW, The following is a sample lookup table named Color.

ColorType varchar2(1) e.g. R, G, B...
ColorDesc varchar2(50) e.g. Red, Green, Blue...

Combining all lookup tables into a single reference table would require the addition of a new 1st column called something like ReferenceType, where in the above example it would indicate "Color".

Thanks for your opinion,
Kim

 

[carp]

Kim -
I agree with you for several reasons.
1. A universal mapping table can quickly becom unwieldy as people start tinkering with the structure. As another type of lookup becomes necessary, somebody will almost always want to "tweak" the rules for the table. Pretty soon, you need a "Lookup Committee" to control the table, and it will take you two weeks of coordination effort just to add "brown" to your color lookups.

2. A universal mapping table will tend to be much larger (in terms of rows) than you will need for any separate mapping. Consequently, your indexing strategy will need to be very good to avoid slowdowns of what should be a quick/easy lookup. Indeed, smaller lookup tables can often have all of their data stored in a relatively few number of blocks, thus rendering indexes unnecessary.

With that said, a universal lookup table makes it easy to remember where you go to look something up.

I'm sure you'll get more inputs both for and against either approach. I, for one, look forward to the discussion.

 

[carp]

Extending your team members' approach, I suppose any data warehouse could be implemented with two tables: a fact table and a lookup table. Which raises the question: Would the star schema now be renamed to something like the hydrogen schema?

 

[dbtoo2001]

IMHO:

(Have they heard about normalization?

http://www.bkent.net/Doc/simple5.htm

http://www.databasedesign-resource.com/normal-forms.html

http://www.wisegeek.com/what-is-a-relational-database.htm

Oh, wait, is DB2 relational or hierarchial? I forget, I haven't used DB2 in 8 years. )

In my opinion, they should be seperate tables, built as IOT's (always use the leading column of an IOT in the 'where' predicate).
When you port it to 10g (why aren't you using 10g?) You can have individual tables in particular instances running on servers utilized for specific functions, whereas the one honkin' big table theory would constantly be moved around between the caches, this may slow down the application. Rows may not be logically grouped together in the same blocks (colors will be intermixed with fruits intermixed with vegetables, and man, what a messy salad that could be!), possibly causing more I/O than needed.

 

[sem]

In some cases creating separate table to contain 5-10 rows of public data of the same structure (code, short name, description) is an overkill. There's no real gain from creating COLORS table with 8 records, GENDERS with 2 records, YESNO of all flavours (TRUEFALSE, HIGHLOW... ) with 2 records each etc.

But the decision to move lookup data to that universal domain table shouldn't be blind. Most obvious requirements are:
1. Similar structure
2. Small size
3. Publicity

P.S. When you have such universal table you should resist the temptation to move everything there: that table must be under strict control by experienced, not only well-educated guy.

Regards, Dima

http://seminihin.narod.ru