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.
_____________________________________
Design Proposed
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: [EMAIL PROTECTED]
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: [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).
