List:General Discussion« Previous MessageNext Message »
From:Jan Steinman Date:October 21 2002 7:06pm
Subject:RE: how do you define a relationship?
View as plain text  
>From: "Arthur Fuller" <afuller@stripped>
>
>Unfortunately that oversimplifies the situation. A least a few movies have
>more than one director.

My motto: "Generalize for the norm; specialize for the exception."

The vast majority of movies have but one director. And I suspect that movies with multiple
directors have but one who is primary.

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!

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.

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.

Of course, your particular application may be director-centric, like if you're building a
special database to support research on directors. But if it's just a general-purpose
movies database, why bog the whole thing down just to suit a few exceptional cases?

---- SQL SQL SQL SQL SQL SQL SQL SQL  ----
-- 
: Jan Steinman -- nature Transography(TM): <http://www.Bytesmiths.com>
: Bytesmiths -- artists' services: <http://www.Bytesmiths.com/Services>
: Newsletters now on-line at <http://www.Bytesmiths.com/Newsletter>
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