WEBVTT 00:00.090 --> 00:00.540 OK. 00:00.660 --> 00:06.450 So we talked about the three kind of common relationship classifications and as I mentioned we're focusing 00:06.450 --> 00:07.670 on one to many. 00:07.680 --> 00:09.900 First and we're doing that for a couple of reasons. 00:09.930 --> 00:16.260 One is that compared to many many it's a little bit easier to get through shorter but also it's more 00:16.260 --> 00:21.240 common and it's more central in my opinion to most of the development that that I've done and that I 00:21.240 --> 00:22.340 see other people doing. 00:22.440 --> 00:25.830 Not to discount many too many but we're going to get there. 00:25.830 --> 00:27.060 We're starting with one too many. 00:27.450 --> 00:30.330 So we're going to start with a classic example. 00:30.480 --> 00:36.870 Customers and orders or this could be users and orders or users and carts whatever the transaction is 00:36.920 --> 00:39.510 but we're going to call it customers and orders. 00:39.510 --> 00:40.770 So two tables. 00:40.980 --> 00:44.600 And this is a one to many relationship as we talked about earlier. 00:44.640 --> 00:51.810 So let's say we have a customer President Bill Clinton that's our customer and he can place as many 00:51.810 --> 00:52.940 orders as he'd like. 00:52.950 --> 00:59.660 He can have many orders but each of those orders is associated with exactly one user. 00:59.670 --> 01:07.020 Bill Clinton President Bill Clinton excuse me there is no way to have two people associated with one 01:07.110 --> 01:08.360 order. 01:08.490 --> 01:12.160 So it's a one too many customers have many orders. 01:12.280 --> 01:15.850 Orders have one customer associated with them. 01:15.960 --> 01:19.590 So let's talk about for our case what data we want to store. 01:19.610 --> 01:21.140 It's going to be very simple. 01:21.390 --> 01:25.830 I don't want to clutter it with a whole bunch of other pieces of information we're just going to focus 01:25.830 --> 01:27.230 on the basics. 01:27.270 --> 01:31.270 So a first and last name for customers and an email. 01:31.500 --> 01:34.730 Now in a real web app we'd want a lot more information probably. 01:34.770 --> 01:40.410 We want to create you know our registration data when a customer signs up maybe a last active date that 01:40.410 --> 01:44.620 we're storing or date time password somehow we need to store that. 01:44.710 --> 01:47.830 There's I don't know there's so many other things phone number. 01:47.830 --> 01:49.700 We're trying to do two factor authentication. 01:49.770 --> 01:50.660 Lots of things. 01:50.910 --> 01:55.290 So first and last name and email so three things per customer. 01:55.650 --> 02:00.970 But then we also for orders we need to store the date that an order was made. 02:01.260 --> 02:06.050 So it doesn't matter for our example if it date or date time but we'll keep it simple with date. 02:06.120 --> 02:10.080 So 2017 slash 11 slash 12 or something. 02:10.350 --> 02:17.550 And then the price of the order the total amount let's say ninety nine point nine nine or twenty point 02:17.550 --> 02:18.490 five zero. 02:18.750 --> 02:21.210 So that's going to be a decimal. 02:21.210 --> 02:27.090 So we're going to have first and last name and email which should probably be Vardar as the date of 02:27.090 --> 02:31.440 the purchase which will be a date time and the price of the order which will be a decimal. 02:31.530 --> 02:32.850 That's the easy part. 02:32.850 --> 02:34.920 The next question is how do we store that. 02:34.920 --> 02:36.170 How do we represent it. 02:36.630 --> 02:42.170 And we could do it this way which I want to make clear I'm not saying this is a good way of doing it. 02:42.330 --> 02:47.530 But you could technically get away with doing it in one giant table but there's problems. 02:47.550 --> 02:49.480 But let's first just walk through how it works. 02:49.710 --> 02:55.590 So we have first name and last name and e-mail for customers and then order date and amount. 02:55.680 --> 03:00.530 So we have you know Boy George whose e-mail is George a gmail dot com. 03:00.540 --> 03:07.050 He was a very early user of Gmail and then we have the order date 20:16 to 10. 03:07.560 --> 03:11.060 And that order was ninety nine dollars and 99 cents. 03:11.070 --> 03:19.560 So this is capturing information for one order but then George or Boy George placed another order and 03:19.560 --> 03:22.100 we have duplicated information because we're storing. 03:22.110 --> 03:27.280 OK it's Boy George which I know was not his first name but just let's go with it. 03:27.330 --> 03:30.790 And then George had gmail again and then a different date. 03:30.810 --> 03:35.820 This one was later in 2017 and then a different price or different amount. 03:36.180 --> 03:41.190 So this store is the information that we talked about when needed first last e-mail and then date of 03:41.190 --> 03:43.460 the purchase price of the order. 03:43.560 --> 03:45.710 And so that's working. 03:45.720 --> 03:52.980 Same thing with George Michael who has GM at gmail dot com bought something in 2014 that was $800 and 03:52.980 --> 03:54.200 67 cents. 03:54.390 --> 04:00.030 And then George Michael Sam email Bazzani in 2050 and that was 12 dollars and 50 cents. 04:00.420 --> 04:00.880 OK. 04:00.930 --> 04:03.050 So already you should be noticing. 04:03.060 --> 04:04.880 We have some a lot of duplication. 04:04.920 --> 04:05.340 Right. 04:05.520 --> 04:10.710 Even though we are storing the information that we're supposed to store there's a lot of duplicated 04:10.710 --> 04:16.340 data and then we run into these two David Bowie and blue steel. 04:16.360 --> 04:20.440 My cat there's problems because they haven't placed orders yet. 04:20.670 --> 04:25.220 So we have their customer information we have David Bowie David at gmail dot com. 04:25.260 --> 04:29.840 Also early adopter of Gmail and we have blue steel blue at gmail dot com. 04:30.120 --> 04:36.540 And they haven't placed orders but they still have order date and amount as columns and they just have 04:36.540 --> 04:37.050 no value. 04:37.050 --> 04:37.880 It's not. 04:38.370 --> 04:44.170 So this technically represents that fact that Boy George and George Michael have two orders each. 04:44.490 --> 04:46.890 And David Bowie and you still have no orders. 04:46.890 --> 04:48.870 So that's really not a great way of doing it. 04:48.870 --> 04:53.090 Yes it works but we're not only duplicating a lot of things right. 04:53.170 --> 04:59.340 George George George Michael and if somebody you know shopped a lot we would have tons of duplicated 04:59.340 --> 05:01.640 information which is just a waste. 05:01.770 --> 05:07.440 But then also we run into problems where we have users who maybe haven't ordered anything yet and they 05:07.440 --> 05:09.020 just signed up for the site. 05:09.030 --> 05:13.360 There's no reason to have anything to do with orders until they actually place an order. 05:13.380 --> 05:19.590 So it's much better to keep our data separated because if we ever need to work with just customers on 05:19.590 --> 05:24.560 their own which is happens you know there are times on the site where you don't care about orders or 05:24.570 --> 05:25.650 you just care about customers. 05:25.660 --> 05:27.700 Let's say when a customer is signing in. 05:28.050 --> 05:33.150 All you need is to take their e-mail and check it and take a password and check it against the password 05:33.150 --> 05:33.750 field. 05:33.960 --> 05:35.490 That has nothing to do with orders. 05:35.580 --> 05:39.570 So why would we need to go through a giant customer orders table. 05:39.960 --> 05:41.360 And that's just a really silly example. 05:41.370 --> 05:45.110 But what I'm trying to show is that it's not just about the duplications. 05:45.300 --> 05:49.850 It's also that sometimes it's better to keep your data separate almost every time. 05:49.980 --> 05:56.550 In Leicester always accessing your data together like if the only time we're ever talking about customers 05:56.790 --> 06:01.520 is when we're talking about orders then OK maybe you could put them together. 06:01.680 --> 06:07.020 But then we still have this problem where we have people who haven't placed orders or we have duplicated 06:07.020 --> 06:07.730 data. 06:07.730 --> 06:12.730 So this is a bad idea not a good idea aka bad idea. 06:13.130 --> 06:14.380 So what do we do. 06:15.020 --> 06:17.260 Well here's the simplest approach. 06:17.270 --> 06:21.730 This is how we express one to many relationship and it looks like this. 06:21.740 --> 06:28.100 So we have two tables customers and orders and customers have an ID field and then first name last name 06:28.100 --> 06:35.600 and email and then orders have an ID field and then the date the amount and then this is the important. 06:35.660 --> 06:37.350 This is the crux of everything. 06:37.430 --> 06:42.620 They have a field called it doesn't matter necessarily what it's called but ours is called Customer 06:42.620 --> 06:46.510 ID and is a reference to the customers table. 06:46.550 --> 06:53.570 So whatever customer ID is in a given order is going to correspond to an actual customer who placed 06:53.570 --> 06:54.960 it. 06:54.980 --> 06:56.820 So let's dive into it a bit more. 06:56.930 --> 06:58.310 Here's an example. 06:58.380 --> 07:00.170 So I took the exact same data. 07:00.170 --> 07:07.460 We had four orders and four customers from back here and I split it up into separate tables and you 07:07.460 --> 07:13.130 can see it let's just talk about customers first we have a customers table with an ID a first name last 07:13.130 --> 07:14.810 name and an email. 07:14.810 --> 07:23.720 So we have board Ga-Ga G-mail ID one George Michael GMG mail id to David Bowie David at gmail dot com 07:23.800 --> 07:28.460 ID 3 and then blue steel blue at gmail dot com idea for. 07:28.520 --> 07:33.390 These are all unique corresponding to exactly one user or one customer. 07:33.620 --> 07:36.320 And then we have our orders table. 07:36.320 --> 07:44.270 So there's an ID there Order ID and then the date that an order was placed and then the amount and then 07:44.300 --> 07:49.760 the most important part well arguably the most important part definitely the crux of the relationship 07:50.090 --> 07:59.810 is customer id so in this case it's one which is pointing to this customer the idea of one meeting. 07:59.810 --> 08:05.290 Boy George so Boy George placed an order on 2016 for ninety nine dollars. 08:05.300 --> 08:07.310 Ninety nine cents. 08:07.340 --> 08:14.570 Here's another customer I.D. one telling us that this entire row here this order is related to Boy George 08:14.570 --> 08:14.910 again. 08:14.960 --> 08:24.540 So he placed an order for 35 50 on 20:17 and then we have George Michael customer ID to match this idea 08:24.540 --> 08:25.230 here. 08:25.740 --> 08:31.830 And he bought something for $800 67 cents in 2014 and something in 2015 for twelve dollars and fifty 08:31.830 --> 08:35.340 cents which is the exact same data we had represented here. 08:36.150 --> 08:41.640 And then notice that we have users like David Bowie or customers excuse me David Bowie and Blue Steel 08:41.910 --> 08:43.180 who haven't bought anything. 08:43.350 --> 08:46.050 And there's no extra data there's no Knolles. 08:46.140 --> 08:48.910 We have the minimum information that we need. 08:48.930 --> 08:51.340 We're not storing anything that we don't need. 08:51.570 --> 08:56.580 So we don't have extra orders that are created because they haven't created order and we have separate 08:56.580 --> 08:57.440 tables. 08:57.930 --> 09:01.620 So this is the classic way of structuring a one to many relationship. 09:01.950 --> 09:07.050 And along with that there are two terms we need to point out which we have briefly discussed and the 09:07.050 --> 09:08.940 first one is primary key. 09:09.210 --> 09:14.310 So remember this key word we can type when we're defining a schema we can say and what we've been saying 09:14.310 --> 09:24.420 a lot is ID space and space autoincrement and primary key and the effect of that what a primary key 09:24.420 --> 09:30.820 means is that some particular column in our case is customer ID is always unique. 09:30.960 --> 09:36.580 So we only have one customer with the idea of one and only one customer. 09:36.580 --> 09:41.710 The idea of two and three and four and that's really important because if we're referencing it somewhere 09:41.710 --> 09:47.800 else like over here if there are two different customers that have ID of one then this is useless because 09:47.800 --> 09:49.280 Which one does it refer to. 09:49.630 --> 09:55.000 So it has to be unique and then that's where the auto increment comes in right where it will make it 09:55.270 --> 10:00.870 unique every time we insert a new customer it will automatically increment this to 5 and to 6 to 7. 10:00.910 --> 10:04.740 So there is no possible way we could have duplicate customer IDs. 10:04.960 --> 10:09.380 And then also here is Order ID which is also a primary key. 10:09.430 --> 10:13.560 It is the way the unique way of referencing an order. 10:13.570 --> 10:21.130 Now this is a bit extreme but it's possible that we would have to duplicate rows here where basically 10:21.130 --> 10:26.760 an order was placed with the same amount at the same day by the same person. 10:26.770 --> 10:32.560 It's possible and we would need to refer to them separately though we don't want our database just to 10:32.560 --> 10:33.420 combine them. 10:33.460 --> 10:38.530 Maybe the user purposely bought the same thing twice they bought it once and then they realize oh shoot 10:38.580 --> 10:39.850 I really should have bought two. 10:39.850 --> 10:43.920 So then they buy it again same amount same day same customer ID. 10:44.170 --> 10:48.860 Well the only thing that keeps those orders unique is our Order ID. 10:48.880 --> 10:51.160 So again these two are primary keys. 10:51.190 --> 10:57.670 They are the primary way of referring to rows in this table is what makes them or it's not the only 10:57.670 --> 11:01.160 thing that makes them unique but it is guaranteed to be unique. 11:01.720 --> 11:09.310 And then that brings us to foreign keys and foreign keys are references to another table within a given 11:09.310 --> 11:10.580 table. 11:10.600 --> 11:19.090 So in this case in our orders table customer ID is a foreign key because it is referring to this idea 11:19.090 --> 11:20.920 of the customers. 11:20.920 --> 11:28.180 So primary key primary key foreign key customers does not have a foreign key and it is not referring 11:28.180 --> 11:33.340 to any external table but orders has a foreign key and that terminology is important. 11:33.340 --> 11:34.870 We'll come back to it a lot. 11:34.870 --> 11:36.520 Primary key foreign key. 11:36.610 --> 11:42.340 Will also show you how you actually say in your schema definition that something is a foreign key. 11:42.550 --> 11:49.570 And the reason you would do that is to enforce that whatever customer ID is in here is an existing customer 11:49.570 --> 11:56.400 id over here because we might what if we inserted something an order in an amount with customer id of 11:56.410 --> 12:00.900 20 and we dont have a customer with ID of 20 we mean. 12:00.940 --> 12:03.160 I mean most likely we dont want that to happen. 12:03.280 --> 12:03.490 Right. 12:03.490 --> 12:08.140 We would want to bounce that back and say hey this is a problem there is not a valid customer with that 12:08.140 --> 12:08.650 ID. 12:08.860 --> 12:14.840 And thats what foreign key when we use that in our schema definition it will enforce that. 12:15.010 --> 12:20.980 Otherwise if we dont say that its a foreign key explicitly we could have customer IDs you know all over 12:20.980 --> 12:22.820 the place that don't actually exist. 12:23.170 --> 12:23.570 OK. 12:23.710 --> 12:25.790 So this is the schema that we've defined. 12:25.900 --> 12:31.320 Well this is in the actual scheme of this is that the basics of what we want our schema to look like. 12:31.360 --> 12:33.370 Now we're going to implement it in the next video.