WEBVTT 00:00.150 --> 00:00.670 All right. 00:00.720 --> 00:02.370 Welcome to the next section. 00:02.370 --> 00:08.490 So we're continuing on the same similar path talking about relationships talking about joint more complex 00:08.490 --> 00:13.510 data multiple tables and connecting those tables drawing lines between them. 00:13.510 --> 00:19.560 However we're now moving on to our next relationship which is the many to many relationship. 00:19.590 --> 00:25.530 So we saw how to do a one to many which is a pretty standard relationship and it's pretty common. 00:25.530 --> 00:30.620 Many too many is is communist Well maybe not as common but still very very common. 00:31.020 --> 00:32.700 So we're going to see how to do it. 00:32.700 --> 00:36.940 And the reason that I saved it for last is that it's a little bit trickier. 00:36.960 --> 00:41.640 It's not hard at all to scare you away but you'll see there's a little more setup involved. 00:41.640 --> 00:46.710 And then once you've discussed on a high level how many to many relationships typically work we're going 00:46.710 --> 00:53.520 to implement one using some relatively complex data that I spent way too long creating. 00:53.880 --> 00:55.540 And then we'll do some problems together. 00:55.710 --> 00:59.370 So this section will actually serve more as a code along. 00:59.430 --> 01:03.610 So I would encourage you if you don't typically I encourage you to follow along. 01:03.660 --> 01:08.160 We're going to use some relatively complex data like I said and we'll just go through it do a couple 01:08.160 --> 01:14.100 of exercises together and I'm going to use it as a way to illustrate how joints work on a many to many 01:14.100 --> 01:15.150 relationship. 01:15.180 --> 01:20.470 So let's start in this video I just made talking about the many to many relationship. 01:20.700 --> 01:26.610 So we kind of discussed it briefly in the last section but here are a couple of examples. 01:26.610 --> 01:33.120 So books and authors harkening back to that last section where we saw books a book can have multiple 01:33.120 --> 01:33.790 authors. 01:33.910 --> 01:34.110 Right. 01:34.110 --> 01:40.560 There are lots of books that are written by co-authors or teams of you know if it's a collection if 01:40.560 --> 01:47.460 it's an anthology there might be 20 authors in each of those authors can then have multiple books. 01:47.670 --> 01:53.670 So it's a many to many relationship books can have many authors authors can have many books or another 01:53.670 --> 02:01.500 common example is blog post or some sort of content whether it's a Instagram photo a tumbler or whatever 02:01.500 --> 02:10.130 you call it the tumblr posts or just a traditional blog some sort of content and then tags tags. 02:10.140 --> 02:18.630 By that I mean things like sunset picture or there's some really like Van life if you're familiar with 02:18.630 --> 02:26.070 that hash tag or I do a lot of photography so I see a lot of tags that are things like like Masterpiece 02:26.700 --> 02:32.520 landscape photography and then you know insta good if you use Instagram there's all these different 02:32.520 --> 02:34.380 tags that are out there. 02:35.010 --> 02:38.110 And each post can have multiple tasks. 02:38.340 --> 02:42.720 I mean there's a limit sometimes but you know on Instagram you can have 30 hashtags I think it is my 02:42.720 --> 02:43.550 photo. 02:43.830 --> 02:50.400 And then each of those tags can then have as many posts or photos as necessary. 02:50.400 --> 02:52.010 So that's another example. 02:52.260 --> 02:55.900 And the final example I have here is students in classes. 02:56.310 --> 03:02.220 So this is we're talking about like maybe college students or university students where students have 03:02.220 --> 03:03.030 multiple classes. 03:03.030 --> 03:05.820 They picked the classes at the beginning of the semester. 03:06.210 --> 03:07.770 One student can have. 03:07.770 --> 03:13.170 Usually you must have multiple classes unless maybe it's your last semester but you have multiple classes 03:13.170 --> 03:16.080 that you go to that you risk related to. 03:16.320 --> 03:22.380 And then each class is going to have multiple students in it and that's a many to many relationship. 03:22.480 --> 03:25.750 And the one that will be working within this section it's actually not listed here. 03:25.760 --> 03:26.700 So a little different. 03:26.880 --> 03:33.740 And to start off I want you to imagine that we're building a TV show reviewing application like yes 03:33.740 --> 03:38.000 I know we don't need any more of those but imagine something like an Internet Movie Database. 03:38.000 --> 03:42.950 Very very simple version though but we're working with TV shows exclusively. 03:42.960 --> 03:48.890 And the key idea is that we can have someone who signs up as a reviewer. 03:49.280 --> 03:54.740 We can have TV shows that you know let's say some of the ones I'm using. 03:54.740 --> 04:00.670 Fargo the TV show Archer General Hospital. 04:00.900 --> 04:10.220 Any series and a reviewer can then rate any of those series so I can sign up and rate Archer as a 8.5 04:10.220 --> 04:10.790 out of 10. 04:10.880 --> 04:17.510 So to accomplish this relationship a many to many relationship where we're basically associating users 04:17.840 --> 04:21.710 or reviewers we'll call them with TV shows. 04:21.710 --> 04:24.530 We need to actually use three tables. 04:24.530 --> 04:30.130 We use what's known as a join table or a union table. 04:30.250 --> 04:32.380 So we have our series data appear. 04:32.380 --> 04:36.670 So these would be the TV shows and then down here we have our viewers data. 04:36.700 --> 04:40.380 So these would be just names in our case we keep it very simple. 04:40.380 --> 04:45.060 So be it or this will be Chartley or whatever it is. 04:45.400 --> 04:54.070 But the way that they're connected is through a third table through reviews table so this will make 04:54.070 --> 04:58.810 sense and I show you the schema in just a moment but think about it this way a series exist on their 04:58.810 --> 04:59.030 own. 04:59.050 --> 05:03.070 They're just TV shows reviewers exist on their own. 05:03.370 --> 05:06.010 They have nothing to do with a serious out of the box. 05:06.160 --> 05:07.470 It's just someone's name. 05:07.660 --> 05:12.060 But they then are associated through reviews table. 05:12.250 --> 05:16.610 So this reviews table will have information on the TV show that's being reviewed. 05:16.900 --> 05:21.750 And then the reviewer who is doing it as well as a third piece of information which is the rating. 05:21.790 --> 05:26.280 What's than the number the numeric representation of that review. 05:26.290 --> 05:28.930 So without further ado let's take a look at our schema. 05:30.020 --> 05:36.850 We have three tables reviewers over here with an ideal primary key first name and last name. 05:36.950 --> 05:39.830 And of course in a real application we would have more data here right. 05:39.830 --> 05:43.940 We would have email we'd be storing our password in a safe protected way. 05:44.210 --> 05:48.200 We would have things like the registration date may be less active. 05:48.200 --> 05:53.820 All that kind of stuff but to keep it simple here so we can focus on the relationship focus on the joints. 05:53.870 --> 05:58.000 I'm only doing first name and last name then we also have series. 05:58.490 --> 06:06.140 And you know this is something I actually debated the plural of series in English modern traditional 06:06.230 --> 06:08.200 spoken English is just a series. 06:08.420 --> 06:14.480 However a lot of people would argue that we should name this table series's within E.S. in the end just 06:14.480 --> 06:17.330 to make it clear that that's a table name. 06:17.570 --> 06:20.170 I'm not exactly a fan of that. 06:20.270 --> 06:25.340 It's really determined by the project or the team you're working with or by you if you're working on 06:25.340 --> 06:26.130 your own thing. 06:26.180 --> 06:33.170 I've worked on projects where we had to abide by that where the plural was always with an S afterward. 06:33.500 --> 06:36.040 Even if that meant mousies instead of mice. 06:36.160 --> 06:39.030 But I'm going to leave it a series just because I think it's simplest. 06:39.050 --> 06:46.140 So series has a primary key id a title so that would be Fargo which is a TV show as well. 06:46.160 --> 06:48.510 Really good TV show actually not just a movie. 06:48.650 --> 06:55.950 So Fargo or halting catch fire of Seinfeld and then the release year. 06:56.180 --> 06:58.830 So 2014 or 2002 or whatever it is. 06:58.850 --> 07:01.700 And then a genre which we're going to keep very simple. 07:01.700 --> 07:05.600 We're only going to have animation comedy and drama. 07:05.730 --> 07:12.140 Just a couple of examples of genres but again these on their own are just separate entities where they're 07:12.200 --> 07:18.100 joined is through this third revues table which is what's known as a join or union table. 07:18.170 --> 07:19.760 It's connecting these two. 07:20.180 --> 07:21.700 And the way it's doing that. 07:21.830 --> 07:25.130 Well first of all it has an ID nothing special there. 07:25.430 --> 07:28.760 Every review has an ID a rating. 07:28.760 --> 07:36.040 So this would be you know 5.0 for a mediocre show or a nine point nine for a fantastic show. 07:36.290 --> 07:38.350 And then the series ID. 07:38.390 --> 07:43.420 So that's going to be a foreign key pointing to this series table. 07:43.520 --> 07:45.650 So what series is this Fargo. 07:45.710 --> 07:47.070 Is this Seinfeld. 07:47.450 --> 07:53.570 And then the same exact same but the same idea for reviewer ID it's going to be a foreign key pointing 07:53.570 --> 07:57.440 to the reviewers table the ID feels. 07:57.500 --> 08:05.950 So then a single review will have an ID it will have a rating 9.00 a series Id like one and then a reviewer 08:05.960 --> 08:09.590 ID which could also be one they're pointing to different tables. 08:09.650 --> 08:16.580 So this is a centralized repository or a centralized place for our information to be stored. 08:16.580 --> 08:20.750 It's connecting these two tables through this table. 08:20.750 --> 08:23.110 So hopefully you can see how this is going to work. 08:23.150 --> 08:27.260 Basically reviews on its own is going to be a pretty ugly table to look at. 08:27.260 --> 08:32.570 It's just going to be numbers it will have an ID which is number rating as a number series ideas number 08:32.570 --> 08:34.310 and reviewer ideas and number. 08:34.340 --> 08:38.010 So looking at it on its own is is actually going to be very confusing. 08:38.150 --> 08:39.320 It's not going to make sense. 08:39.350 --> 08:46.640 So we'll have to use Joines to be able to understand things so that we can use a join to replay series 08:46.640 --> 08:55.430 ID with the name of the series that it says Fargo instead of one or it will say blue steel instead of 08:56.180 --> 08:58.990 reviewer ID 3 and so on. 08:59.000 --> 09:01.220 So that's our basic idea here. 09:01.790 --> 09:04.550 Here's a simple Or here's an example with some simple data. 09:04.610 --> 09:05.670 Very very simple. 09:05.690 --> 09:07.210 You know two reviewers. 09:07.550 --> 09:15.090 So we've got blue steel and Wyatt Earp idea of one and two then we've got two series Archer and Fargo. 09:15.170 --> 09:17.010 If you're not familiar with these shows don't worry. 09:17.180 --> 09:19.480 That's not necessarily relevant. 09:19.520 --> 09:23.150 Archer is animation released first in 2009. 09:23.150 --> 09:27.930 Fargo is a drama with a little comedy released in 2014. 09:28.190 --> 09:33.880 Archer has idea of one Fargo whose idea of two so they're independent within our reviews table. 09:34.100 --> 09:39.410 Well somebody reviewed something with a rating of eight point nine. 09:39.620 --> 09:41.890 The person who did that has a review or idea of one. 09:41.890 --> 09:47.760 So that is still so blue still reviewed something and gave it an eight point nine. 09:47.780 --> 09:49.670 Well what did she review. 09:49.670 --> 09:51.300 Whatever has serious ID too. 09:51.530 --> 09:52.460 So Fargo. 09:52.580 --> 09:55.580 So Blue gave Fargo an eight point nine. 09:55.580 --> 09:59.670 Then we have a 9.5 down here and the reviewer ID is too. 09:59.840 --> 10:04.450 So that's why it or this field or this row here in reviewers. 10:04.510 --> 10:10.670 Wyatt Earp also reviewed Fargo because ID to as we can see corresponds to Fargo. 10:10.670 --> 10:17.240 So essentially what we're storing here Fargo has been rated twice once by blue 8.9 and watched by Wyatt 10:17.270 --> 10:19.030 as a nine point five. 10:19.160 --> 10:22.380 So as you can see the data is not that pretty in this table. 10:22.580 --> 10:24.320 And this is only with two reviews. 10:24.340 --> 10:29.400 Will be working with somewhere around 100 reviews which is a lot of data to look at on its own. 10:29.600 --> 10:34.790 And so we'll really be focusing on making that data understandable readable which is really the point 10:35.390 --> 10:37.310 of this entire exercise. 10:37.340 --> 10:39.750 So we're pretty much done here in this first video. 10:39.770 --> 10:44.570 We didn't actually do anything except discuss our schema but in the next video we'll actually go about 10:44.720 --> 10:47.860 implementing the tables and inserting some data on our.