Re: How many FKs is too many ?

Initial thoughts are:
- sounds like a dwh type implementation. Many dimensions on a single fact table.

• is it really designed and normalized correctly?

Once I saw a database "design". Actually it was an incredible piece of crap that would never work. It had been designed by someone who didn't do database development, and looked down on such menial tasks. She saw herself as a "designer". And she did not know many Fundamental things. ie. that unix and oracle were separate logins, and used different passwords.

She imposed her vision and version of the world on us. That is, instead of asking to see what the requirements were.

One of her many mistakes was to create a DWH design for an OLTP system. She had nine lookup tables for the date alone! Many of these could have been implemented with a check clause. Ie. Month in the range of 1 to 12. And why did we need a lookup table for the year? 2004 perfectly indentifies itself. There is absolutely no need to have a surrogate key, x, to refer to 2004. What value do you add with this? It only creates complexity.

However, for this oltp system, all it needed was the date datatype. If we needed information later, then just run queries using different date masks.

So, she had created 20 to 30 FK and indexes on the largest table that would have the most activity. Duh. This slowed things down incredibly.

So, my initial idea is. The design may be fine. But there may be leaner ways to design it.

Are you in love with a lookup table for every field in the database? If so, get over it. Not every field needs a lookup table. Ultimately, you will have data to insert that you could never anticipate.

What kind of fields are being looked up? How often is the data changed? If it is loaded once, and left there, never updated, you may be able to just denormalize the data and not worry about RI.

Just how fast does this need to go? Try inserting data with PK only, Plus, one FK, two FK, ... 15 fk and check the response time. Is it within range? If you only insert a few hundred rows a day, 15 fields could be perfectly fine.

Although, yes, in general, the more FK you put on a table, the slower the inserts will be.

Just some thoughts.

BTW, foreign keys do a fabulous and fast job of referential integrity. The alternatives: triggers, and even worse, logic in the app itself, are much slower.

krislioe_at_gmail.com (xtanto) wrote in message news:<e1c9bd55.0410210337.2957db19_at_posting.google.com>...
> Hi Oracle gurus,
>
> I have two tables, one with 100.000 rows and the other with about 4
> million rows. Each table has about 55 columns, with 15 of them refer
> to a reference Table.
>
> If I put FK constraint on these 15 columns (mean I have 15 FKs).
> Is this too many ? will this cause poor performance ?
> Is there any best practices for this ?
>
> Thank you for your help,
> xtanto
Received on Wed Oct 27 2004 - 16:34:09 CDT