Re: DESIGN Question: 1 or many tables and other thoughts

Tracy Rahmlow wrote:
>
> My question deals with what is the best practice in creating lookup tables.
> Many of these tables contain very limited information such as "code &
> description". For example:
>
> Agency
> 1 - Jones Inc
> 2 - Ratfield Co
>
> Source
> 1 - phone
> 2 - mail
> 3 - internet
>
> Is it better to create 1 composite table to contain this data or have separate
> tables for each? If a composite table is the way to go, how far do you take
> it? Ie, what if the entity has more than just 2 columns worth of data. What
> is the best way to enforce that valid values are actually stored in the table,
> since foreign keys can't be used to these table types? (For example, if a
> sales table has a source column, how should you enforce that valid sources are
> actually stored in the row?)
>

I like your example, because there are two very different things here: something which can possibly be dynamic (Agency) and something which stands very good chance of remaining static (source). IMHO, the first one should go to a lookup table, with FK and all, while for the second one meaning should be hardcoded with a DECODE when you need it, and integrity enforced by a CHECK constraint, i.e source_code in (1, 2, 3). Updating the CHECK constraint when you have a new source to add is not so much of a pain in terms of maintenance. To answer your 'one composite table' question, I am against, because it would prevent you from using the basic FK mechanisms and you would have to write triggers-of-death to ensure integrity. Would be ugly. That said, try to use CHECK rather than a FK when you have a reasonably weak (say, under 25) number of values to check and that you are not likely to change the values every week. I have carried out a number of tests, if the cost of an insert in a table is 100, adding a foreign key make it jump to 60 (and adding one index more than doubles the cost). In comparison, CHECK costs next to nothing.

-- 
Regards,

Stephane Faroult
Oriole Ltd
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).