Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2015
    Posts
    3

    How should I model this relationship?

    I think this should be easy, but it's got me completely stumped.

    I've got a Movies table and I'd like to be able to track the Writer(s), Director(s), and Actor(s) who are in that movie. I know that a Person can have more than one Job in a Movie.

    I know all of that, but I have absolutely no idea what the relationship should look like.

    I have this:
    Click image for larger version. 

Name:	Untitled.png 
Views:	4 
Size:	31.8 KB 
ID:	16369

    But, I don't even know if I'm headed in the right direction.

  2. #2
    Join Date
    Jun 2015
    Location
    Boston, MA
    Posts
    16
    I would create a movie table with MovieName, director, producer, actor1, actor2, etc. fields
    Create a table of people and put all actors, directors, etc in that table with an automatically-generated primary key.
    Fill in the roles in the movies using the primary key for the relevant person.

    - Bruce Hyatt
    Last edited by mrthnmn; 06-02-15 at 17:40.

  3. #3
    Join Date
    Feb 2015
    Posts
    3
    How would I handle situations with multiple writers or directors then?

  4. #4
    Join Date
    Jun 2015
    Location
    Boston, MA
    Posts
    16
    Make multiple fields for each - Director1, Director2 - up to maximum you might need, just like the actors.

    - Bruce Hyatt
    Last edited by mrthnmn; 06-02-15 at 17:41.

  5. #5
    Join Date
    Feb 2012
    Posts
    76
    I strongly advise against actor1, actor2, director1, director2, etc. fields. OP is on the right track, though it sounds like it may be appropriate to add Job to the relationship to turn it into a ternary relation.

  6. #6
    Join Date
    Jun 2015
    Location
    Boston, MA
    Posts
    16
    The actor1, actor2, etc solution seems wrong to me too but I haven't thought of an elegant solution yet to the situation where you have a varying number of fields for a record. I don't see how you avoid that here. The number of roles in a movie varies. The number of movies a given person is in varies. The number of roles a given person plays in a movies varies.

    You could add a table for each person and create a record for each role they played in each movie. But how do you then query for a list of movies with their director? You're going to join all the person tables?

    - Bruce Hyatt

  7. #7
    Join Date
    Feb 2012
    Posts
    76
    With an implementation like:
    Code:
    movies: movie PK, movie_name
    persons: person PK, person_name
    roles: role PK, role_name
    credits: movie PK/FK, role PK/FK, person PK/FK
    One could ask in SQL:
    Code:
    SELECT m.movie_name, p.person_name
    FROM credits c
    INNER JOIN roles r ON c.role = r.role
    INNER JOIN movies m ON c.movie = m.movie
    INNER JOIN persons p ON c.person = p.person
    WHERE r.role_name = 'Director'
    ORDER BY m.movie_name, p.person_name

  8. #8
    Join Date
    Jun 2015
    Location
    Boston, MA
    Posts
    16
    I don't know why I had such trouble seeing that but thank you. I think you helped me figure the problem I was having.

    - Bruce Hyatt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •