Re: Lots of tables in O7

scott_at_chuck.sycraft.com wrote:
: My group is building a critical application. One option is to use many tables
: for lookup info (carriers, types of things, etc.) all data driven. This is
: best for the tools we use. Result would be 60+ tables of which 20 would have
: fewer than 20 rows (lookup stuff). Option two would be to put all of that
: lookup stuff in one table.
 

: Which is better for Oracle? Any actual experience?

SCOTT --- Neither method is more or less technically superior than the other. I'm sure will can find people who will argue to the death for any table. This is how I decide between the two.

1. What will be the cost of maintaining NN tables as opposed to 1 table with many subtypes.
2. Will the uses wish to maintain these domains?

#2 is the critical point. If the answer to that question is yes (and usually is), the use of one table is mandatory. You may then keep all lookup codes in one location, with one maintenance screen. Using the other method, you will have 60 tables and 60 maintenance screens. This is too much work for lookup codes. Here is a popular implementation.

Table Lookup Types

lookup_type       "YES_NO"
description       Yes or No Answer
enabled_flag      Y 

Tables lookup_codes

Lookup Type       YES_NO     YES_NO
Lookup Code       Y          N
Meaning           Yes        No
Enabled Flag      Y          Y

You may now simply build a master/detail screen and the user has maintainable codes. Note that any secondary data entity that doesn't fit into the common code description category may need a separate table.

Hope This Helps!!!!

Tim McCollum (tmccollu_at_us.oracle.com) or (edtwm_at_huber.com) Oracle Consulting
Iselin NJ Received on Thu Jun 02 1994 - 21:36:31 CEST