Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Querying Relational Databases

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Many to Many Relationships

2:16 with Andrew Chalkley and Kevin Nahm

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

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    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 a 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
    part, 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

      You need to sign up for Treehouse in order to set up Workspace

      Sign up