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."
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.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!
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 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.
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).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.
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
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
