Many to Many 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