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.
Many-To-Many Relationship
Moderator: General Moderators
-
spacebiscuit
- Forum Contributor
- Posts: 390
- Joined: Mon Mar 07, 2005 3:20 pm
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Many-To-Many Relationship
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]
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
Ok that makes sense - so the joining table simply stores the common relationships - each record represents an actor and movie combination?
Thanks,
Rob.
Thanks,
Rob.