WEBVTT 1 00:00:00.280 --> 00:00:01.500 Hi guys, and welcome back. 2 00:00:01.500 --> 00:00:04.160 In this video, we're going to look at stage three 3 00:00:04.160 --> 00:00:06.130 of our project. 4 00:00:06.130 --> 00:00:08.450 Stage two had a couple problems, 5 00:00:08.450 --> 00:00:11.230 marking a movie as watched, removed it from 6 00:00:11.230 --> 00:00:14.640 the movies table, so we had to re-add it. 7 00:00:14.640 --> 00:00:17.560 And also, we couldn't distinguish between two users with 8 00:00:17.560 --> 00:00:19.530 the same username. 9 00:00:19.530 --> 00:00:21.360 And so these are a couple of problems. 10 00:00:21.360 --> 00:00:24.890 We are going to be fixing them in stage three. 11 00:00:24.890 --> 00:00:29.590 A good starting point for database design is to model after 12 00:00:29.590 --> 00:00:33.190 the real-world entities that your programme deals in. 13 00:00:33.190 --> 00:00:34.640 So our programme, at the moment, 14 00:00:34.640 --> 00:00:39.640 has two main real-world entities, that's users and movies. 15 00:00:39.800 --> 00:00:43.100 So we can create tables for each. 16 00:00:43.100 --> 00:00:46.250 But what about the watching thing? 17 00:00:46.250 --> 00:00:47.720 It's not really a thing, right? 18 00:00:47.720 --> 00:00:51.440 It's an action, something that a user does to a movie. 19 00:00:51.440 --> 00:00:54.020 And then when you get to something like this, 20 00:00:54.020 --> 00:00:57.680 it's interesting to think about how the act of watching 21 00:00:57.680 --> 00:00:59.640 is related to the entities. 22 00:00:59.640 --> 00:01:01.120 So watching movies, 23 00:01:01.120 --> 00:01:04.860 we've got that one user can watch many movies, 24 00:01:04.860 --> 00:01:08.880 but also one movie can be watched by many users. 25 00:01:08.880 --> 00:01:11.650 So this is known as a many-to-many relationship, 26 00:01:11.650 --> 00:01:14.320 and it can be modelled with a third table 27 00:01:14.320 --> 00:01:15.690 that links the other two. 28 00:01:15.690 --> 00:01:18.130 We've seen this before, we're just gonna explain it 29 00:01:18.130 --> 00:01:19.070 a bit better now. 30 00:01:19.070 --> 00:01:22.700 So what's interesting here is that we're going to have 31 00:01:22.700 --> 00:01:24.440 the movies table like that. 32 00:01:24.440 --> 00:01:26.910 We've got the id property, the title 33 00:01:26.910 --> 00:01:28.710 and the release timestamp. 34 00:01:28.710 --> 00:01:31.110 We've got the user data table there that just has 35 00:01:31.110 --> 00:01:32.270 the username at the moment, but, 36 00:01:32.270 --> 00:01:33.850 like we discussed earlier on in the course, 37 00:01:33.850 --> 00:01:35.640 we could have more information about users 38 00:01:35.640 --> 00:01:37.310 here if we wanted. 39 00:01:37.310 --> 00:01:40.260 And finally, notice that these two columns, username 40 00:01:40.260 --> 00:01:42.750 and id at the top, are primary keys. 41 00:01:42.750 --> 00:01:45.150 And finally, we're gonna have this watched table 42 00:01:45.150 --> 00:01:47.410 that has two foreign keys. 43 00:01:47.410 --> 00:01:51.480 The user username column is a foreign key onto 44 00:01:51.480 --> 00:01:54.490 the username column of the users table. 45 00:01:54.490 --> 00:01:57.880 And the movie id column at the bottom, is a foreign key 46 00:01:57.880 --> 00:02:01.240 that maps to the id column of the movies table. 47 00:02:01.240 --> 00:02:03.460 So this third table at the bottom is just 48 00:02:03.460 --> 00:02:06.220 a mapping between users and movies. 49 00:02:06.220 --> 00:02:09.630 It doesn't actually store any more information than that. 50 00:02:09.630 --> 00:02:11.300 With this table, we can easily say 51 00:02:11.300 --> 00:02:14.170 that one user watched multiple movies. 52 00:02:14.170 --> 00:02:17.300 Rolf Smith, in this case, watched two movies. 53 00:02:17.300 --> 00:02:20.050 And one movie was watched by multiple users. 54 00:02:20.050 --> 00:02:24.110 In this case, movie two was watched by Rolf and Anne. 55 00:02:24.110 --> 00:02:27.180 If we wanted to grab more information about movies, 56 00:02:27.180 --> 00:02:29.660 we just go into the movies table and fetch 57 00:02:29.660 --> 00:02:32.200 the related information to the movie id. 58 00:02:32.200 --> 00:02:34.170 And similarly, if we wanted to grab more information 59 00:02:34.170 --> 00:02:37.200 about users, we'd grab the user username column, 60 00:02:37.200 --> 00:02:40.160 go into the users table and fetch the information there. 61 00:02:40.160 --> 00:02:43.410 We're going to learn how to do that using joins in just 62 00:02:43.410 --> 00:02:44.710 a moment. 63 00:02:44.710 --> 00:02:46.080 That's everything for this video. 64 00:02:46.080 --> 00:02:47.200 This is stage three. 65 00:02:47.200 --> 00:02:49.500 We're gonna be implementing this over the next few videos. 66 00:02:49.500 --> 00:02:52.350 Thank you for watching, and I'll see you in the next one.