In the previous video, we learnt about the most common type of table relationship,

the one to many.

In this video, we're going to take a look at another type of relationship,

the many to many relationship.

Remember that relationship types are named according to how many rows

can be related to each other on either side of the relationship.

So many to Many relationship means that

one record can relate to many tables in another table, and one record

from the second table can relate back to many records in the first table.

A common example of a many to many relationship is orders and parts.

An order can consist of many parts and a part can be on many orders.

Thus the many to many relationship type but

there's a problem, which table do we have the foreign key to?

If we add it to the order table, you wind up having to duplicate the primary key,

order ID to have many parts on one order.

Or, if you try to add the foreign key to the parts table,

you wind up duplicating the primary key, parts ID to handle many orders.

Remember primary keys must be unique.

How do we handle this conundrum?

It turns out this is kind of a trick question.

When we have a many to many relationship type between two tables such as order and

pop, it requires a third table.

We take the primary key from each table, order_part, and

make this columns the following keys in a new table.

Also, the combination of these columns

creates a multi-column primary key in this new table.

Essentially what we've done here is resolved the many to many relationship

by creating two one to many relationships.

This new table is sometimes called a junction table or an associative entity.

As it turns out, even though we discussed the many to many relationship in concepts

they don't actually exist in a relational database at all.

Is just two one to many relationships with a junction table.

In the next video, we'll take a look at one more relationship type,

the one-to-one relationship.