WEBVTT 1 00:00:00.300 --> 00:00:01.710 Hi Guys, and Welcome Back 2 00:00:01.710 --> 00:00:04.570 In this video, we look at the three stages of our project 3 00:00:04.570 --> 00:00:07.090 through which we will build the complete application, 4 00:00:07.090 --> 00:00:10.510 the first stage, we're gonna have one database table. 5 00:00:10.510 --> 00:00:11.430 And that's because 6 00:00:11.430 --> 00:00:14.790 users are gonna be able to add movies to a table, 7 00:00:14.790 --> 00:00:17.400 as well as store, whether they've watched the movie or not 8 00:00:17.400 --> 00:00:19.090 all in the same table. 9 00:00:19.090 --> 00:00:21.420 So at this point, we're not gonna support multiple users, 10 00:00:21.420 --> 00:00:22.930 it's just gonna be one user 11 00:00:22.930 --> 00:00:24.510 with one table of movies, 12 00:00:24.510 --> 00:00:25.820 and whether they've watched it. 13 00:00:25.820 --> 00:00:27.310 So this is more or less what it's gonna look like. 14 00:00:27.310 --> 00:00:30.690 We have the title of the movie as the first column, 15 00:00:30.690 --> 00:00:32.610 we have the release timestamp, 16 00:00:32.610 --> 00:00:33.960 or when the movie was released, 17 00:00:33.960 --> 00:00:36.480 we're gonna talk about timestamps a bit more later on. 18 00:00:36.480 --> 00:00:38.690 And we also have whether it's been watched or not 19 00:00:38.690 --> 00:00:40.370 a one means that it's been watched, 20 00:00:40.370 --> 00:00:42.500 a zero means it's not been watched. 21 00:00:42.500 --> 00:00:44.490 So as you can see, this is pretty simple. 22 00:00:44.490 --> 00:00:46.560 But there's no way for us to support multiple users 23 00:00:46.560 --> 00:00:48.710 because there's no information in this table 24 00:00:48.710 --> 00:00:50.690 to tell us who's watched what. 25 00:00:50.690 --> 00:00:53.000 So at the moment, the app will be single user 26 00:00:53.000 --> 00:00:54.350 in this first stage, 27 00:00:54.350 --> 00:00:57.400 every movie added will only belong to one user, 28 00:00:57.400 --> 00:01:00.480 and if the watched column has a one in It 29 00:01:00.480 --> 00:01:03.350 that means that the one user that has this application 30 00:01:03.350 --> 00:01:05.440 has seen this movie. 31 00:01:05.440 --> 00:01:06.840 Then after we've done this, 32 00:01:06.840 --> 00:01:08.950 and we've completed this and we know it works, 33 00:01:08.950 --> 00:01:11.000 we're gonna move over to the second stage, 34 00:01:11.000 --> 00:01:13.480 which is supporting multiple users. 35 00:01:13.480 --> 00:01:14.313 In order to do this, 36 00:01:14.313 --> 00:01:16.840 we're gonna need to separate who watched what, 37 00:01:16.840 --> 00:01:18.890 from the movies themselves. 38 00:01:18.890 --> 00:01:20.660 And so we will have a table. 39 00:01:20.660 --> 00:01:21.670 This is the one on the right, 40 00:01:21.670 --> 00:01:23.640 that stores the title of the movie, 41 00:01:23.640 --> 00:01:25.350 and when it was released, 42 00:01:25.350 --> 00:01:26.720 and then the table on the right 43 00:01:26.720 --> 00:01:28.800 stores who watched the movie 44 00:01:28.800 --> 00:01:30.840 and the title of the movie they watched. 45 00:01:30.840 --> 00:01:32.360 So here we've got, for example, 46 00:01:32.360 --> 00:01:34.700 that Rolf Smith watched two movies 47 00:01:34.700 --> 00:01:37.310 and Ann watched one movie. 48 00:01:37.310 --> 00:01:40.140 Remember that building this project incrementally, like this 49 00:01:40.140 --> 00:01:42.140 is a normal way to build software, 50 00:01:42.140 --> 00:01:45.163 we only add the features we need when we need them. 51 00:01:46.470 --> 00:01:47.980 Finally, for the third stage, 52 00:01:47.980 --> 00:01:50.980 we're going to reduce duplication further 53 00:01:50.980 --> 00:01:52.690 by storing the movie information 54 00:01:52.690 --> 00:01:54.910 and the user information in their own tables. 55 00:01:54.910 --> 00:01:57.760 And who watched what in a separate table. 56 00:01:57.760 --> 00:01:59.280 You can see in the previous tables, 57 00:01:59.280 --> 00:02:01.930 that the movies name was there twice, 58 00:02:01.930 --> 00:02:04.350 we're gonna get rid of that duplication. 59 00:02:04.350 --> 00:02:05.940 So the watched table, 60 00:02:05.940 --> 00:02:08.190 the one that tells us who watched what 61 00:02:08.190 --> 00:02:10.930 is only going to reference the other table. 62 00:02:10.930 --> 00:02:13.470 So we're gonna have something like this. 63 00:02:13.470 --> 00:02:15.970 We've got the movies on the left. 64 00:02:15.970 --> 00:02:17.880 And each movie has an ID column, 65 00:02:17.880 --> 00:02:18.900 which is the primary key. 66 00:02:18.900 --> 00:02:20.990 We're gonna talk about all of this as we go along. 67 00:02:20.990 --> 00:02:24.440 Then, we've got the user data in the middle table, 68 00:02:24.440 --> 00:02:26.100 at the moment, there's only user name in there, 69 00:02:26.100 --> 00:02:28.530 but we could add more data if we wanted. 70 00:02:28.530 --> 00:02:29.800 And finally, on the right, 71 00:02:29.800 --> 00:02:32.610 we've got the mapping of who watched what, 72 00:02:32.610 --> 00:02:35.940 so the user and the movie ID. 73 00:02:35.940 --> 00:02:37.080 So with that information, 74 00:02:37.080 --> 00:02:38.660 we could go into the user table 75 00:02:38.660 --> 00:02:40.620 and find more data about the user. 76 00:02:40.620 --> 00:02:42.540 Or we could go into the movie table 77 00:02:42.540 --> 00:02:45.670 with the ID and find more information about the movie data. 78 00:02:45.670 --> 00:02:48.220 We're going to learn more about how this is related 79 00:02:48.220 --> 00:02:51.030 and how relationships work in a later video. 80 00:02:51.030 --> 00:02:52.890 So why a table with only username? 81 00:02:52.890 --> 00:02:53.730 Well, like I said, 82 00:02:53.730 --> 00:02:54.600 this is just an example 83 00:02:54.600 --> 00:02:56.800 we could add a lot more information into that table 84 00:02:56.800 --> 00:02:58.150 and usually you would 85 00:02:58.150 --> 00:03:00.590 like email date of birth and location et cetera, 86 00:03:00.590 --> 00:03:03.090 other data related to the user. 87 00:03:03.090 --> 00:03:05.660 So what we're gonna learn through this process 88 00:03:05.660 --> 00:03:09.120 is how to better structure the data on the tables. 89 00:03:09.120 --> 00:03:13.670 So that data only exists with meaning in one place. 90 00:03:13.670 --> 00:03:14.880 This is called normalisation 91 00:03:14.880 --> 00:03:16.780 and allows us to deduplicate data 92 00:03:16.780 --> 00:03:18.150 and keep it all in one place. 93 00:03:18.150 --> 00:03:21.550 So the other tables can access that single place, 94 00:03:21.550 --> 00:03:23.250 we're gonna learn how to use JOINs 95 00:03:23.250 --> 00:03:26.290 to query data from multiple tables. 96 00:03:26.290 --> 00:03:29.130 And it's important to notice that normalised data 97 00:03:29.130 --> 00:03:31.360 has to have meaning on its own. 98 00:03:31.360 --> 00:03:33.030 And so you can take it too far. 99 00:03:33.030 --> 00:03:34.660 And don't divide a phone number 100 00:03:34.660 --> 00:03:35.950 into its digits, for example, 101 00:03:35.950 --> 00:03:38.100 because the digits don't mean anything on their own. 102 00:03:38.100 --> 00:03:39.310 Don't divide a date 103 00:03:39.310 --> 00:03:40.730 into the day, month and year 104 00:03:40.730 --> 00:03:43.120 because that doesn't mean anything on its own either. 105 00:03:43.120 --> 00:03:46.270 So, and normalisation allows us to extract data 106 00:03:46.270 --> 00:03:48.620 from one table into two tables 107 00:03:48.620 --> 00:03:51.340 if it still has meaning on its own. 108 00:03:51.340 --> 00:03:52.970 We've got some references 109 00:03:52.970 --> 00:03:54.850 in the resources section of this lecture 110 00:03:54.850 --> 00:03:57.380 with a step by step normalisation of a database 111 00:03:57.380 --> 00:03:58.690 which has an interesting read. 112 00:03:58.690 --> 00:03:59.900 And indeed, the resource does say 113 00:03:59.900 --> 00:04:01.380 when it's taken too far, 114 00:04:01.380 --> 00:04:02.950 but nonetheless, it's interesting. 115 00:04:02.950 --> 00:04:04.170 So let's get to it. 116 00:04:04.170 --> 00:04:06.700 We're gonna be building this project throughout the section. 117 00:04:06.700 --> 00:04:07.910 So let's get started. 118 00:04:07.910 --> 00:04:09.743 I'll see you in the next video.