Many-To-Many Relationship

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
spacebiscuit
Forum Contributor
Posts: 390
Joined: Mon Mar 07, 2005 3:20 pm

Many-To-Many Relationship

Post 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.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Many-To-Many Relationship

Post 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]
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
spacebiscuit
Forum Contributor
Posts: 390
Joined: Mon Mar 07, 2005 3:20 pm

Re: Many-To-Many Relationship

Post 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.
Post Reply