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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
