| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Are too many Foreign Keys in one table bad?
Gregory,
There are several things to consider since you are still at the design phase. This table sounds like a great candidate for denormalization. Is this an OLTP or OLAP system? How static are the values in the reftables? If the reftables are static and contain very few values, consider putting the values into the support_data table. If the data values (not counting the relationship codes) are small, the storage may be about the same if they are stored inside or outside of the database.
In addition to the join performance issue, you will have to worry about insert/update/delete. To prevent locking problems, you will need to put an index on the FK columns. You now have 15 extra changes to make when you change a row.
The best method to determine the optimal solution is to create test cases and measure the performance of various configurations. It will require a little bit of time right now, but may save a great deal of time in fixes/outages/redeployments when the system goes live and performance goes down.
Dan Fink
-----Original Message-----
Sent: Monday, January 06, 2003 1:04 PM
To: Multiple recipients of list ORACLE-L
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fink, Dan INET: Dan.Fink_at_mdx.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 Tue Jan 07 2003 - 09:44:42 CST
 |
 |