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
>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.