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

>Create a new table called Roles, which contains rows for Actor, Director,
>Producer, D.O.P. and whatever else is of interest.
>  
>
For what its worth, I would consider it more appropriate to simply do:

Movies: ID, etc.
People: ID, Name, etc.
MoviePeople: MoviesID, PeopleID, Role

SELECT COUNT(*) FROM Movies
    LEFT JOIN MoviePeople ON Movies.ID = MovieID
    LEFT JOIN People ON PeopleID = People.ID
    WHERE People.Name = "Clint Eastwood"
        AND MoviePeople.Role = "Writer";

... to check if Clint Eastwood is a writer or not; although it limits 
you to the movies you know about, but in a situation like IMDB.com, 
that's pretty much everything.

PS, I'd actually make Role an ID to a Roles table, but that would've 
added a JOIN to the query above and unnecessarily complicated my 
comment.  Good suggestion though!

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