Page 1 of 1

Many-To-Many Relationship

Posted: Tue Apr 12, 2011 7:54 am
by spacebiscuit
Hi,

I am looking for suggestions as to how I might model a many-to-many relationship in a database.

For example consider a table of actors and actresses and a table of movies. An actor may appear in many movies and a movie will feature many actors. How do I account for the fact that an actor might feature in 100 movies - I can't have a field for each movie.

I can't figure this out.

Thansk in advance,

Rob.

Re: Many-To-Many Relationship

Posted: Tue Apr 12, 2011 10:33 am
by AbraCadaver
Normally you would use a join table that contains the actor ids and related movie ids:

actors
[text]id name
1 Sean Connery
2 John Cleese
3 John Candy
4 Steve Martin[/text]
actors_movies
[text]actor_id movie_id
1 3
1 2
2 1
3 5
4 4
4 5[/text]
movies
[text]id name
1 The Holy Grail
2 Highlander
3 First Knight
4 The Jerk
5 Planes, Trains, Automobiles[/text]

Re: Many-To-Many Relationship

Posted: Wed Apr 13, 2011 6:36 am
by spacebiscuit
Ok that makes sense - so the joining table simply stores the common relationships - each record represents an actor and movie combination?

Thanks,

Rob.