The index is necessary on both sides. What if you wanted to delete from the
lookup table? You'd need to be able to first make sure that lookup value
was not in use.

I've definitely found performance problems with Interbase and SQL Server
when doing:

Select ... From Table T Join LookupTable L On T.Lookup_ID = L.Lookup_ID

I don't exactly where the optimizer is getting confused, but the following
query runs MUCH faster on both platforms, even though the RI relationship
between the tables guarantees that the queries are identical:

Select ... From Table T Left Outer Join LookupTable L On T.Lookup_ID =
L.Lookup_ID

The above assumes that Count(T.*) is very large compared to Count(L.*). My
worst case example was where Count(T.*) was about 6 million and Count(L.*)
was about 70. Then the first query was running in 30 minutes and the second
in 30 seconds... (there were actually a few lookup tables involved).


- Dave Berg - TCI

-----Original Message-----
From: Joseph Alba [mailto:jalba@...]
Sent: Tuesday, July 11, 2000 2:46 PM
To: IB-Architect@egroups.com
Subject: [IB-Architect] Referential Integrity


Dear BBW Jim,

I have a lookup table with an expected cardinality of around 30 rows
I reference this table on a data table with an expected cardinality of 7
million rows.

Table Lookup:
-- Lookup_ID
-- Lookup_Name

Table Data
__ Data_ID primary key
__ Date
__ Lookup_ID references Lookup(Lookup_ID)

Question:
Is this safe to do?
Would this establishing of referential integrity constraint not cause an
index selectivity problem?

-----

Although this might look like a support question, what I'm really asking is,

Is this a problem area (architecturally - establishing reference
relationships) that needs a solution?

Or, is there nothing to worry about? specifically on selectivity issues that
the automatically generated index the reference creates.

At present, I have this rule with myself:

1. Only establish reference constraints when the relationship is a master /
detail one (I mean Header/Items).
2. Use Insert/Update/Delete triggers to establish Lookup constraints (to
cascade / restrict changes on Lookup tables to referencing tables)
-> on clients I use the transaction with READ COMMITTED

----

I realize that an automatically created Index is quite useful when the
nature of the reference is a Master-Detail one.
But, if the nature of relationship is a Look-up (Lookup ID validity
checking) the index on the referencing side is not necessary.

Is this suggestion okay for the new IB? ->

1. When the REFERENCE contains a CASCADE instruction -> automatically create
the index because this is most probably a Master/Detail relationship.

2. When the REREFERENCE does not contain a CASCADE instruction (restrictive)
then, do not automatically create an index because this is most probably
just a lookup relationship

Joseph Alba
jalba@...




------------------------------------------------------------------------
Respond.com - Technology Solutions for your Business!
http://click.egroups.com/1/6828/6/_/830676/_/963352033/
------------------------------------------------------------------------

To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com