One or many lookup tables

[Kimbodie]

Hello,
We are deciding whether to implement many lookup tables (code values and descriptions) or one massive lookup table with all code types in one table. I have seen debates before about this, but usually they are referring to relatively small apps with maybe 30 lookup tables. We project that our app will have approximately 800 lookup tables. Does this cross some threshold which causes us to use one common lookup table? Or should we stick to our guns and have one code table for each type of lookup data, even though it's 800 more tables in the database? BTW, the total number of rows that we're talking about here is about 20,000 - a relatively small quantity of information.
Thanks,
Kim

 

[SantaMufasa]

TenFold Corporation's Enterprise TenFold, MyTenFold, and Tsunami products (Highly successful and efficient Rapid Application Development tools) use the identical solution to your Option 1: "Single Code Table for all codes and their respective descriptions". I highly recommend that solution as well, and I cannot think of a good argument in favour of implementing Option 2, regardless of volume.

Let us know if you decide to choose the "Intelligent" option or the "Dumb" option.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[carp]

We've used both ways, and I too prefer the one lookup table. There are a couple of things I ran into -
1. Indexing is crucial - be sure to get it right.
2. The biggest problem I ran into with the one table/many uses approach was simply getting other people to understand how it worked and why it was simpler/more efficient to use. This is more of a management/political/persuasion issue than a technical one, which makes it a lot tougher!

 

[BJCooperIT]

If you are considering developing a form for maintenance of your lookup table(s) then forms development time is a persuasive reason management can understand. One form with a drop-down box for code type is pretty fast to create. But... consider one form with 800 blocks or 800 forms?

Are there any security issues with these tables? For instance, can one user modify some tables, but not others?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[johnherman]

I, too, have seen this done both ways. Is this mostly a transaction or Decision Support system? The tendency to denormalize for performance reasons in a DSS environment would point to the single table. In an OLTP environment, it would probably be better to have a single tables, or subject area combined code tables (for record and table locking purposes).

-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[Beilstwh]

Another reason to have multiple lookup tables is because of Relational Integrity. If you have a single lookup table, then you would have to include the lookup type code in every application table to be able to establish Relational Integrity using foreign keys. If you have multiple lookup tables, then you only have to establish the FK on the code value.

Bill
Oracle DBA/Developer
New York State, USA

 

[ChrisHunt]

I would always have a shared lookup table for short, simple lists of values that rarely (if ever) change. I see no particular value in building seperate little tables for things like "genders", "months", etc. Referential integrity is more difficult to maintain with such a table, but it can be done programtically with triggers, if you have a second table which notes which lookups are used where. Frankly I rarely bother, since the lists concerned tend to be very static.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd