How to include several records from a table into one field on another table?

[iNcR3d1bl3]

Hi!

I have this project for my class that involves me creating a movie database. The database has table for the movies and actors, now my problem comes in when I want to associate the movies with the actors, because the movies have more than one actor I don't know how to implement the movies table so it accepts more actors. Do I need to make fields for every actor in the movie? Because this would set a limit to the number of actors, or can I include all the associated actors in one field in the movie table?

Any help is greatly appreciated, thanks.

Andy

[Ender]

Hi Andy,

You need a join table between Movie and Actor, so that you can remember multiple Roles for each Actor. In the attached ER diagram, I show a Member table instead of Actor, as this general term could be used for anyone working in the Movie biz. You could use this same structure to track Directors, Producers, and other members of the production crew.

[iNcR3d1bl3]

Thank you for taking the time to assist me in this mannor. However, I am still uncertain of what I should input into the "cast" field under the movie table if I want to associate multiple actors, would I'm using the actors's ID as reference to the actor's table, so would I for example input several actors as: 1,5,7,5 : or what would I input into the field?

Thanks for any further assitance.

Andy

P.S. btw, thanks for the diagram as well.

Edited March 11, 2006 by Guest

[Ender]

The Movie table would not have a field for Actor or Actor ID. That's the point of the join table. The Role table holds the ID of both the Member (Actor) and the ID of the Movie, thus joining the Member with the Movie.

[iNcR3d1bl3]

Oh, I see it now. Alright, thanks for all your help