List:General Discussion« Previous MessageNext Message »
From:Michael T. Babcock Date:October 21 2002 7:42pm
Subject:Re: how do you define a relationship?
View as plain text  
Jan Steinman wrote:

>My motto: "Generalize for the norm; specialize for the exception."
>
My motto: "Do it right, then optimize it later if it sucks."

>Keep a single director field, with a second NULL FULLTEXT field for additional
> directors. The NULL test for the normal case is much cheaper than what you have to go
> through by assuming that ALL movies have multiple directors!
>
I don't know that that's true; I'd be fairly confident that a 
double-JOIN to get to the director would be almost equally fast to the 
single-JOIN in fact, since as you point out there will only usually be 
one entry in the MovieDirector table's index for each movie.

>This is how books are handled in large databases. Each has a primary author, and may
> contain secondary authors. There is no assumption that large numbers of books have
> multiple, equal authors.
>
That may be true in some cases, however, the primary author may simply 
have to be the one first in alphabetical order, since I own quite a few 
books written by multiple, equal parties.

>This way, you can easily and conveniently list multiples as "Speilberg (et. al.)"
> without doing joins. An interested browser can then go further to find out who the others
> are. In the other case, each request for a director requires a JOIN.
>
In one case, they require a single join, in the other, a double-join. 
 However, in the other case, searching by director is easier (since 
choosing the non-primary director will still return the correct results 
from a single query, although if you're willing to completely sideline 
this option, doing a LIKE query against the additional field is almost 
acceptable).

>it's just a general-purpose movies database, why bog the whole thing down just to suit
> a few exceptional cases?
>  
>
I hate to claim that something 'bogs something down' without proof ... 
I'm going to go off and do some timings ... :)

-- 
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock


Thread
how do you define a relationship?Randy Hammons20 Oct
  • Re: how do you define a relationship?Richard Clarke20 Oct
  • RE: how do you define a relationship?bwarehouse21 Oct
    • RE: how do you define a relationship?Arthur Fuller21 Oct
      • Re: how do you define a relationship?Michael T. Babcock21 Oct
RE: how do you define a relationship?Jan Steinman21 Oct
Re: how do you define a relationship?Michael T. Babcock21 Oct
RE: how do you define a relationship?Hammons Randy G SSgt 612 ACOMS/SCXX22 Oct
  • Re: how do you define a relationship?Peter Brawley22 Oct
  • RE: how do you define a relationship?Arthur Fuller23 Oct
    • re: RE: how do you define a relationship?Egor Egorov24 Oct
  • Re: how do you define a relationship?Michael T. Babcock8 Nov