1 00:00:00,120 --> 00:00:04,790 Okay, So we talked about the three kind of common relationship classifications. 2 00:00:04,800 --> 00:00:09,730 And as I mentioned, we're focusing on one to many first, and we're doing that for a couple of reasons. 3 00:00:09,750 --> 00:00:16,140 One is that compared to many to many, it's a little bit easier to get through shorter, but also it's 4 00:00:16,140 --> 00:00:20,760 more common and it's more essential, in my opinion, to most of the development that that I've done 5 00:00:20,760 --> 00:00:22,310 and that I see other people doing. 6 00:00:22,320 --> 00:00:25,800 Not to discount many to many, but we're going to get there. 7 00:00:25,800 --> 00:00:26,970 We're starting with one to many. 8 00:00:27,300 --> 00:00:32,159 So we're going to start with a classic example customers and orders. 9 00:00:32,159 --> 00:00:37,680 Or this could be users and orders or users in carts, whatever the transaction is, but we're going 10 00:00:37,680 --> 00:00:39,480 to call it customers and orders. 11 00:00:39,480 --> 00:00:44,520 So two tables and this is a one to many relationship, as we talked about earlier. 12 00:00:44,520 --> 00:00:51,570 So let's say we have a customer, President Bill Clinton, that's our customer, and he can place as 13 00:00:51,570 --> 00:00:52,920 many orders as he'd like. 14 00:00:52,920 --> 00:00:59,580 He can have many orders, but each of those orders is associated with exactly one user. 15 00:00:59,610 --> 00:01:01,860 Bill Clinton, President Bill Clinton. 16 00:01:01,860 --> 00:01:02,520 Excuse me. 17 00:01:02,820 --> 00:01:07,710 There is no way to have two people associated with one order. 18 00:01:08,380 --> 00:01:12,160 So it's a one to many customers have many orders. 19 00:01:12,160 --> 00:01:15,340 Orders have one customer associated with them. 20 00:01:15,790 --> 00:01:19,540 So let's talk about for our case, what data we want to store. 21 00:01:19,540 --> 00:01:21,010 And it's going to be very simple. 22 00:01:21,340 --> 00:01:25,060 I don't want to clutter it with a whole bunch of other pieces of information. 23 00:01:25,060 --> 00:01:27,090 We're just going to focus on the basics. 24 00:01:27,100 --> 00:01:31,250 So a first and last name for customers and an email. 25 00:01:31,270 --> 00:01:34,180 Now, in a real web app, we'd want a lot more information. 26 00:01:34,180 --> 00:01:34,750 Probably. 27 00:01:34,750 --> 00:01:40,570 We want to create a registration date when a customer signs up, maybe a last active date that we're 28 00:01:40,570 --> 00:01:42,850 storing our date time password. 29 00:01:42,850 --> 00:01:44,280 Somehow we need to store that. 30 00:01:44,290 --> 00:01:45,760 There's I don't know. 31 00:01:45,760 --> 00:01:46,930 There's so many other things. 32 00:01:46,930 --> 00:01:50,590 Phone number if we're trying to do two factor authentication, lots of things. 33 00:01:50,740 --> 00:01:53,080 So first and last name and email. 34 00:01:53,080 --> 00:01:54,880 So three things per customer. 35 00:01:55,510 --> 00:02:00,970 But then we also for orders, we need to store the date that an order was made. 36 00:02:01,150 --> 00:02:05,950 So it doesn't matter for our example if it's date or date time, but we'll keep it simple with date. 37 00:02:05,950 --> 00:02:09,820 So 2017 slash 11 slash 12 or something. 38 00:02:10,180 --> 00:02:18,460 And then the price of the order, the total amount, let's say 99.99 or 20.50. 39 00:02:18,580 --> 00:02:20,410 So that's going to be a decimal. 40 00:02:21,100 --> 00:02:26,980 So we're going to have first and last name and email, which should probably be our chars, the date 41 00:02:26,980 --> 00:02:31,030 of the purchase, which will be a date time and the price of the order which will be a decimal. 42 00:02:31,360 --> 00:02:32,680 That's the easy part. 43 00:02:32,710 --> 00:02:34,930 The next question is how do we store that? 44 00:02:34,930 --> 00:02:36,160 How do we represent it? 45 00:02:36,460 --> 00:02:40,300 And we could do it this way, which I want to make clear. 46 00:02:40,300 --> 00:02:44,920 I'm not saying this is a good way of doing it, but you could technically get away with doing it in 47 00:02:44,920 --> 00:02:46,090 one giant table. 48 00:02:46,690 --> 00:02:47,560 But there's problems. 49 00:02:47,560 --> 00:02:49,510 But let's first just walk through how it works. 50 00:02:49,510 --> 00:02:54,670 So we have first name and last name and email for customers and then order date and amount. 51 00:02:55,540 --> 00:03:00,520 So we have Boy George, whose email is George at gmail.com. 52 00:03:00,520 --> 00:03:02,800 He was a very early user of Gmail. 53 00:03:03,400 --> 00:03:10,780 And then we have the order date 2016 210 and that order was $99.99. 54 00:03:10,900 --> 00:03:18,460 So this is capturing information for one order, but then George or Boy George placed another order 55 00:03:19,030 --> 00:03:25,090 and we have duplicated information because we're storing it's Boy George, which I know is not his first 56 00:03:25,090 --> 00:03:27,130 name, but just let's go with it. 57 00:03:27,130 --> 00:03:30,760 And then George at Gmail again and then a different date. 58 00:03:30,760 --> 00:03:35,650 This one was later in 2017, and then a different price or a different amount. 59 00:03:36,010 --> 00:03:38,530 So this stores the information that we talked about. 60 00:03:38,530 --> 00:03:42,820 We needed first, last email and then data, the purchase and price of the order. 61 00:03:43,480 --> 00:03:44,710 And so that's working. 62 00:03:45,760 --> 00:03:54,160 Same thing with George Michael who has at gmail.com bought something in 2014 that was $800.67. 63 00:03:54,160 --> 00:03:59,920 And then George Michael, same email about something in 2015 that was $12.50. 64 00:04:00,850 --> 00:04:05,410 So already you should be noticing we have some a lot of duplication, right? 65 00:04:05,410 --> 00:04:10,780 Even though we are storing the information that we're supposed to store, there's a lot of duplicated 66 00:04:10,780 --> 00:04:17,050 data and then we run into these two David Bowie and Blue Steal My Cat. 67 00:04:17,560 --> 00:04:20,529 There's problems because they haven't placed orders yet. 68 00:04:20,529 --> 00:04:22,360 So we have their customer information. 69 00:04:22,360 --> 00:04:29,020 We have David Bowie, David at gmail.com, also early adopter of Gmail and we have Blue Steel Blue at 70 00:04:29,020 --> 00:04:35,830 gmail.com and they haven't placed orders, but they still have order, date and amount as columns and 71 00:04:35,830 --> 00:04:37,060 they just have no value. 72 00:04:37,060 --> 00:04:37,780 It's null. 73 00:04:38,230 --> 00:04:41,020 So this technically represents that fact. 74 00:04:41,020 --> 00:04:46,750 That boy George and George Michael have two orders each, and David Bowie and Blue still have no orders. 75 00:04:46,750 --> 00:04:48,910 So that's really not a great way of doing it. 76 00:04:48,910 --> 00:04:50,080 Yes, it works. 77 00:04:50,080 --> 00:04:53,050 But we're not only duplicating a lot of things, right? 78 00:04:53,050 --> 00:04:55,030 Boy George, George Michael. 79 00:04:55,030 --> 00:05:01,660 And if somebody shopped a lot, we would have tons of duplicated information, which is just a waste. 80 00:05:01,660 --> 00:05:07,480 But then also we run into problems where we have users who maybe haven't ordered anything yet and they 81 00:05:07,480 --> 00:05:08,830 just signed up for the site. 82 00:05:08,830 --> 00:05:13,090 There's no reason to have anything to do with orders until they actually place an order. 83 00:05:13,180 --> 00:05:19,630 So it's much better to keep our data separated because if we ever need to work with just customers on 84 00:05:19,630 --> 00:05:24,820 their own, which is happens, there are times on a site where you don't care about orders, you just 85 00:05:24,820 --> 00:05:25,660 care about customers. 86 00:05:25,660 --> 00:05:31,120 Let's say when a customer is signing in, all that you need is to take their email and check it and 87 00:05:31,120 --> 00:05:33,670 take a password and check it against the password field. 88 00:05:33,820 --> 00:05:35,470 That has nothing to do with orders. 89 00:05:35,470 --> 00:05:39,520 So why would we need to go through a giant customer orders table? 90 00:05:39,850 --> 00:05:41,380 And that's just a really silly example. 91 00:05:41,380 --> 00:05:46,780 But what I'm trying to show is that it's not just about the duplication, it's also that sometimes it's 92 00:05:46,780 --> 00:05:53,770 better to keep your data separate almost every time, unless you're always accessing your data together. 93 00:05:53,770 --> 00:06:00,430 Like if the only time we're ever talking about customers is when we're talking about orders, then maybe 94 00:06:00,430 --> 00:06:01,570 you could put them together. 95 00:06:01,570 --> 00:06:07,060 But then we still have this problem where we have people who haven't placed orders or we have duplicated 96 00:06:07,060 --> 00:06:07,510 data. 97 00:06:07,730 --> 00:06:09,350 So this is a bad idea? 98 00:06:09,620 --> 00:06:12,530 Not a good idea, aka bad idea. 99 00:06:12,980 --> 00:06:14,000 So what do we do? 100 00:06:14,900 --> 00:06:17,180 Well, here is the simplest approach. 101 00:06:17,180 --> 00:06:20,270 This is how we express a one to many relationship. 102 00:06:20,480 --> 00:06:21,590 And it looks like this. 103 00:06:21,590 --> 00:06:27,740 So we have two tables, customers and orders and customers have an ID field and then a first name, 104 00:06:27,740 --> 00:06:34,010 last name and email, and then orders have an ID field and then the date, the amount. 105 00:06:34,010 --> 00:06:35,630 And then this is the important. 106 00:06:35,630 --> 00:06:37,220 This is the crux of everything. 107 00:06:37,220 --> 00:06:42,650 They have a field called it doesn't matter necessarily what it's called, but ours is called customer 108 00:06:42,650 --> 00:06:46,490 ID and it's a reference to the customers table. 109 00:06:46,490 --> 00:06:53,600 So whatever customer ID is in a given order is going to correspond to an actual customer who placed 110 00:06:53,600 --> 00:06:53,960 it. 111 00:06:54,830 --> 00:06:56,870 So let's dive into it a bit more. 112 00:06:56,870 --> 00:06:57,950 Here's an example. 113 00:06:58,370 --> 00:07:05,210 So I took the exact same data we had for orders and for customers from back here, and I split it up 114 00:07:05,210 --> 00:07:06,380 into separate tables. 115 00:07:07,220 --> 00:07:09,590 And you can see let's just talk about customers first. 116 00:07:09,620 --> 00:07:14,240 We have a customers table with an ID, a first name, last name and an email. 117 00:07:14,660 --> 00:07:22,850 So we have Boy George George at Gmail ID one, George Michael at Gmail ID two, David Bowie, David 118 00:07:22,850 --> 00:07:27,890 at gmail.com, ID three and then blue steal blue at gmail.com ID of four. 119 00:07:28,430 --> 00:07:32,990 These are all unique corresponding to exactly one user or one customer. 120 00:07:33,530 --> 00:07:35,630 And then we have our orders table. 121 00:07:36,170 --> 00:07:43,130 So there's an ID there, order ID, and then the date that an order was placed and then the amount. 122 00:07:43,580 --> 00:07:49,940 And then the most important part, arguably the most important part, definitely the crux of the relationship 123 00:07:49,940 --> 00:07:52,190 is customer ID. 124 00:07:53,150 --> 00:08:00,710 So in this case, it's one which is pointing to this customer with idea of one meaning Boy George. 125 00:08:01,130 --> 00:08:06,260 So Boy George placed an order in 2016 for $99.99. 126 00:08:07,320 --> 00:08:14,310 Here's another customer ID, one telling us that this entire row here, this order is related to Boy 127 00:08:14,310 --> 00:08:14,910 George again. 128 00:08:14,910 --> 00:08:18,720 So he placed an order for 3550 on 2017. 129 00:08:19,950 --> 00:08:21,630 And then we have George Michael. 130 00:08:21,660 --> 00:08:25,020 Customer ID two matches this ID here. 131 00:08:25,620 --> 00:08:33,630 And he bought something for $800.67 in 2014 and something in 2015 for $12.50, which is the exact same 132 00:08:33,630 --> 00:08:35,159 data we had represented here. 133 00:08:36,030 --> 00:08:41,760 And then notice that we have users like David Bowie or customers excuse me, David Bowie and Blue Steel 134 00:08:41,789 --> 00:08:43,140 who haven't bought anything. 135 00:08:43,140 --> 00:08:46,130 And there's no extra data, there's no NOLs. 136 00:08:46,140 --> 00:08:48,810 We have the minimum information that we need. 137 00:08:48,810 --> 00:08:54,720 We're not storing anything that we don't need, so we don't have extra orders that are created because 138 00:08:54,720 --> 00:08:56,100 they haven't created orders. 139 00:08:56,100 --> 00:08:57,270 We have separate tables. 140 00:08:57,780 --> 00:09:01,500 So this is the classic way of structuring a one to many relationship. 141 00:09:01,770 --> 00:09:06,540 And along with that, there are two terms we need to point out which we have briefly discussed. 142 00:09:06,870 --> 00:09:08,880 And the first one is primary key. 143 00:09:09,090 --> 00:09:14,340 So remember this keyword we can type when we're defining a schema, we can say and what we've been saying 144 00:09:14,340 --> 00:09:23,460 a lot is ID, space, int space, auto increment and primary key and the effect of that. 145 00:09:23,460 --> 00:09:30,330 What a primary key means is that some particular column in our case, this customer ID is always unique. 146 00:09:30,870 --> 00:09:34,740 So we only have one customer with ID of one. 147 00:09:35,530 --> 00:09:37,950 And only one customer, the idea of two and three and four. 148 00:09:37,960 --> 00:09:43,570 And that's really important because if we're referencing it somewhere else, like over here, if there 149 00:09:43,570 --> 00:09:48,730 are two different customers that have ID of one, then this is useless because which one does it refer 150 00:09:48,730 --> 00:09:49,210 to? 151 00:09:49,420 --> 00:09:51,010 So it has to be unique. 152 00:09:51,010 --> 00:09:53,650 And then that's where the auto increment comes in, right? 153 00:09:53,650 --> 00:09:55,660 Where it will make it unique. 154 00:09:55,660 --> 00:10:00,550 Every time we insert a new customer, it will automatically increment this to five and to six and to 155 00:10:00,550 --> 00:10:00,880 seven. 156 00:10:00,880 --> 00:10:04,780 So there is no possible way we could have duplicate customer IDs. 157 00:10:04,780 --> 00:10:09,350 And then also here is order ID, which is also a primary key. 158 00:10:09,370 --> 00:10:13,300 It is the way that unique way of referencing an order. 159 00:10:13,510 --> 00:10:21,160 Now this is a bit extreme, but it's possible that we would have to duplicate rows here where basically 160 00:10:21,160 --> 00:10:26,830 an order was placed for the same amount at the same day by the same person. 161 00:10:26,830 --> 00:10:32,470 It's possible and we would need to refer to them separately, though we don't want our database just 162 00:10:32,470 --> 00:10:33,430 to combine them. 163 00:10:33,430 --> 00:10:38,200 Maybe a user purposefully bought the same thing twice, They bought it once and then they realized, 164 00:10:38,200 --> 00:10:39,710 Oh shoot, I really should have bought two. 165 00:10:39,730 --> 00:10:40,930 So then they buy it again. 166 00:10:40,930 --> 00:10:43,930 Same amount, same day, same customer ID. 167 00:10:43,960 --> 00:10:48,280 Well, the only thing that keeps those orders unique is our order ID. 168 00:10:48,730 --> 00:10:51,130 So again, these two are primary keys. 169 00:10:51,130 --> 00:10:55,630 They are the primary way of referring to rows in this table. 170 00:10:55,630 --> 00:11:00,430 It's what makes them or it's not the only thing that makes them unique, but it is guaranteed to be 171 00:11:00,430 --> 00:11:01,030 unique. 172 00:11:01,540 --> 00:11:03,970 And then that brings us to foreign keys. 173 00:11:04,240 --> 00:11:09,910 And foreign keys are references to another table within a given table. 174 00:11:10,450 --> 00:11:18,520 So in this case, in our orders table, customer ID is a foreign key because it's referring to this 175 00:11:18,520 --> 00:11:20,260 ID of the customers. 176 00:11:20,800 --> 00:11:27,430 So primary key primary key foreign key customers does not have a foreign key in it. 177 00:11:27,430 --> 00:11:33,340 It's not referring to any external table, but orders has a foreign key and that terminology is important. 178 00:11:33,340 --> 00:11:36,430 We'll come back to it a lot, primary key and foreign key. 179 00:11:36,430 --> 00:11:42,220 And it'll also show you how you actually say in your schema definition that something is a foreign key. 180 00:11:42,370 --> 00:11:49,600 And the reason you would do that is to enforce that whatever customer ID is in here is an existing customer 181 00:11:49,600 --> 00:11:56,020 ID over here, because we might What if we inserted something, an order in an amount with customer 182 00:11:56,020 --> 00:12:00,070 ID of 20 and we don't have a customer with ID of 20? 183 00:12:00,220 --> 00:12:00,930 We may not. 184 00:12:00,940 --> 00:12:03,490 I mean, most likely we don't want that to happen, right? 185 00:12:03,490 --> 00:12:06,430 We would want to bounce that back and say, Hey, this is a problem. 186 00:12:06,430 --> 00:12:10,720 There's not a valid customer with that ID And that's what foreign key. 187 00:12:10,720 --> 00:12:14,710 When we use that in our schema definition, it will enforce that. 188 00:12:14,860 --> 00:12:21,010 Otherwise, if we don't say that it's a foreign key explicitly, we could have customer IDs all over 189 00:12:21,010 --> 00:12:22,720 the place that don't actually exist. 190 00:12:23,590 --> 00:12:25,750 So this is the schema that we've defined. 191 00:12:25,750 --> 00:12:31,180 Well, this isn't the actual schema, but this is the basics of what we want our schema to look like. 192 00:12:31,180 --> 00:12:33,400 Now we're going to implement it in the next video.