Unfortunately that oversimplifies the situation. A least a few movies have more than one director. AI, for example: first half by Kubrick, second (lousy) half by Speilberg. Another example is The Twilight Zone, which had four directors.
This calls for a junction table between Movies and Directors, called MovieDirectors and having MovieID and DirectorID. Perhaps this isn't refined enough either. Consider Clint Eastwood, who stars, directs and produces. Woody Allen, who does all that and also writes. A fuller (heh heh) resolution of the problem might look like this: Create a new table called Roles, which contains rows for Actor, Director, Producer, D.O.P. and whatever else is of interest. Combine the existing Directors and Actors tables into a single table called Talent. Create a junction table called TalentRoles, with FKs referencing Talent and Roles. Clint would have rows for Writer, Director and Actor. Now create a table called MovieRoles with foreign keys into TalentRoles and Movies. Then you add as many people to a movie as you want, nw whatever roles are legitimate for those people. I.e. you couldn't add Clint as a writer. hth, Arthur Fuller -----Original Message----- From: bwarehouse [mailto:bwarehouse@;yahoo.com] Sent: Sunday, October 20, 2002 5:38 PM To: Randy Hammons; [EMAIL PROTECTED] Subject: RE: how do you define a relationship? You are on the right track.. but kill your relationships as you have them currently defined... think of it this way... (one to many).. you have movies and you have directors.. it should be one director to many movies seeing that a movie normally only has one director.. so take you primary key from directors and place that foreign key in the movies table. Remember: one director has many movies, not one movie has many directors.. BOTTOM LINE. Make a field in your movies table exactly the same as the primary key in the directors table, but don't autonumber it if that is what is in the primary key of the directors table, just make it the same type( ie. Number, text, date, etc). and make sure it has the exact same specs as the directors primary key field except the autonumber type; and make sure the perimeter is "allow duplicate key is set to yes, if you index the field in the movies table), but you don't have to index the foreign key in the movie table if the primary key of the directors table is already indexed. Just make the foreign key, "allow duplicates 'YES' in the movie table.. Hope this helps.. Later b.ware -----Original Message----- From: Randy Hammons [mailto:rathlon1@;cox.net] Sent: Sunday, October 20, 2002 8:43 AM To: [EMAIL PROTECTED] Subject: how do you define a relationship? I think I've firmed my "theoritical" understanding of relationships enough to be dangerous. Now for the logical part....how do you actually define a relationship in mysql? For instance... table movies columns (movie id(pk) , movie title , year filmed) table directors columns (director id(pk), movie id(fk), total movies) Here I have a primary key in each table which I have defined in my table structure. I also have a foreign key in the directors table, but where did I define that to the database? Is this a sort of relationship? Like a one to many? I've noticed in some reading that certain keys are refered to by their respective names ( column - whatever ) and the relatioship column is defined as ( column - relwhatever ), is this the proper way to define a relationship? Is the primary/foreignkey, relationship concept something that is used by the database to replicate data or are they design tools used to develope an independent, non repetitive data structure? Someone please help...I'm so confused. Thanks so much.... --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
