1 00:00:00,490 --> 00:00:05,389 In the previous video, we learnt about the most common type of table relationship, 2 00:00:05,389 --> 00:00:07,310 the one to many. 3 00:00:07,310 --> 00:00:11,330 In this video, we're going to take a look at another type of relationship, 4 00:00:11,330 --> 00:00:14,050 the many to many relationship. 5 00:00:14,050 --> 00:00:18,160 Remember that relationship types are named according to how many rows 6 00:00:18,160 --> 00:00:21,830 can be related to each other on either side of the relationship. 7 00:00:21,830 --> 00:00:25,040 So many to Many relationship means that 8 00:00:25,040 --> 00:00:29,880 one record can relate to many tables in another table, and one record 9 00:00:29,880 --> 00:00:34,930 from the second table can relate back to many records in the first table. 10 00:00:34,930 --> 00:00:39,510 A common example of a many to many relationship is orders and parts. 11 00:00:39,510 --> 00:00:44,510 An order can consist of many parts and a part can be on many orders. 12 00:00:44,510 --> 00:00:47,300 Thus the many to many relationship type but 13 00:00:47,300 --> 00:00:51,386 there's a problem, which table do we have the foreign key to? 14 00:00:51,386 --> 00:00:57,130 If we add it to the order table, you wind up having to duplicate the primary key, 15 00:00:57,130 --> 00:01:00,365 order ID to have many parts on one order. 16 00:01:00,365 --> 00:01:05,210 Or, if you try to add the foreign key to the parts table, 17 00:01:05,210 --> 00:01:10,690 you wind up duplicating the primary key, parts ID to handle many orders. 18 00:01:10,690 --> 00:01:13,780 Remember primary keys must be unique. 19 00:01:13,780 --> 00:01:15,390 How do we handle this conundrum? 20 00:01:16,670 --> 00:01:19,266 It turns out this is kind of a trick question. 21 00:01:19,266 --> 00:01:24,000 When we have a many to many relationship type between two tables such as order and 22 00:01:24,000 --> 00:01:27,110 pop, it requires a third table. 23 00:01:27,110 --> 00:01:30,790 We take the primary key from each table, order_part, and 24 00:01:30,790 --> 00:01:34,390 make this columns the following keys in a new table. 25 00:01:34,390 --> 00:01:37,390 Also, the combination of these columns 26 00:01:37,390 --> 00:01:42,190 creates a multi-column primary key in this new table. 27 00:01:42,190 --> 00:01:46,460 Essentially what we've done here is resolved the many to many relationship 28 00:01:46,460 --> 00:01:50,120 by creating two one to many relationships. 29 00:01:50,120 --> 00:01:56,120 This new table is sometimes called a junction table or an associative entity. 30 00:01:56,120 --> 00:02:01,440 As it turns out, even though we discussed the many to many relationship in concepts 31 00:02:01,440 --> 00:02:05,230 they don't actually exist in a relational database at all. 32 00:02:05,230 --> 00:02:09,360 Is just two one to many relationships with a junction table. 33 00:02:10,400 --> 00:02:13,830 In the next video, we'll take a look at one more relationship type, 34 00:02:13,830 --> 00:02:15,440 the one-to-one relationship.