Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Many to Many relationships mean that a record in one table can relate to many other records in another table, and one record from the second table can also relate back to many records in the first table.
Resolving Many to Many relationships during the database design process is one of the common steps performed by data architects.
Resources:
Techopedia: Many-to-Many
Wikipedia: Many to Many
Database Primer: Relationships
In the previous video, we learnt about the
most common type of table relationship,
0:00
the one to many.
0:05
In this video, we're going to take
a look at another type of relationship,
0:07
the many to many relationship.
0:11
Remember that relationship types
are named according to how many rows
0:14
can be related to each other on
either side of the relationship.
0:18
So many to Many relationship means that
0:21
one record can relate to many tables
in another table, and one record
0:25
from the second table can relate back
to many records in the first table.
0:29
A common example of a many to many
relationship is orders and parts.
0:34
An order can consist of many parts and
a part can be on many orders.
0:39
Thus the many to many
relationship type but
0:44
there's a problem,
which table do we have the foreign key to?
0:47
If we add it to the order table, you wind
up having to duplicate the primary key,
0:51
order ID to have many parts on one order.
0:57
Or, if you try to add the foreign
key to the parts table,
1:00
you wind up duplicating the primary key,
parts ID to handle many orders.
1:05
Remember primary keys must be unique.
1:10
How do we handle this conundrum?
1:13
It turns out this is
kind of a trick question.
1:16
When we have a many to many relationship
type between two tables such as order and
1:19
pop, it requires a third table.
1:24
We take the primary key from each table,
order_part, and
1:27
make this columns the following
keys in a new table.
1:30
Also, the combination of these columns
1:34
creates a multi-column primary
key in this new table.
1:37
Essentially what we've done here is
resolved the many to many relationship
1:42
by creating two one to many relationships.
1:46
This new table is sometimes called a junction table or an associative entity.
1:50
As it turns out, even though we discussed
the many to many relationship in concepts
1:56
they don't actually exist in
a relational database at all.
2:01
Is just two one to many
relationships with a junction table.
2:05
In the next video, we'll take a look
at one more relationship type,
2:10
the one-to-one relationship.
2:13
You need to sign up for Treehouse in order to download course files.
Sign up