[thelist] sql/table design question

Joshua Olson joshua at waetech.com
Wed Feb 19 01:40:04 CST 2003


----- Original Message -----
From: "rudy" <r937 at interlog.com>
Sent: Tuesday, February 18, 2003 6:31 PM


> if it's supposed to be bidirectional, then i don't see what the extra rows
> buy you, and i don't understand how the extra rows are faster

Book A and Book B are related to each other in a bi-directional
relationship.

Scenarios:

#1.  We have one record in the table that establishes this relationship.  I
want to search for all books related to book id number 1234:

SELECT RelatedBook = CASE WHEN Book1 = 1234 THEN Book2 ELSE Book1 END
FROM table
WHERE Book1 = 1234 OR Book2 = 1234

I used a CASE..WHEN conditional to return the value I am looking for in one
field so my script doesn't have to sort this out.  That's slow factor #1.
Slow factor #2 is the OR in the conditional.

#2.  We have two records for each relationship pointing in opposite
directions:

SELECT Book2 AS RelatedBook
FROM table
WHERE Book1 = 1234

This is obviously going to run a lot faster assuming the same number of
rows.  Of course, we'll have double the rows, but I'd bet it's STILL faster.


I understand your concerns about consistency.  Perhaps some combination of
FK's would help ensure that the inconsistencies don't occur?

Does this explain what I meant?

-joshua




More information about the thelist mailing list