| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Are too many Foreign Keys in one table bad?
I am designing some tables to store Customer Support Data.
The main table (SUPPORT_DATA) contains many (up to 15) foreign key links to
other tables.
Most of the other tables are small lookup REFTABLES (eg Priority Type).
A few bigger tables store up to 1000 records eg CUSTOMER_DATA.
I am concerned that to get data for one Support record will involve a join of 15 Tables and possibly more for reports, and that this many tables may confuse the Cost Based Optimiser.
I am considering storing the CODE in the SUPPORT_DATA table instead of the ID for the reference tables. This will reduce the number of joins greatly.
SUPPORT_DATA
Id (PK)
<reftable>_code (FK)
support_data_desc
....
<REFTABLE>
<reftable>_id (PK)
<reftable>_code (Unique Constraint)
<reftable>_description
The Main problems I see with this are that DATA storage increases (I can deal with that) and that I will have to create a trigger to update all SUPPORT_DATA if one of the CODES in a REFTABLE is updated (this would be rare and so not a great concern).
Is storing the CODE a sound option?
Any hints or comments would be appreciated =)
THX Greg
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gregory Norris INET: GNorris2_at_workbrain.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).Received on Mon Jan 06 2003 - 14:03:42 CST
 |
 |