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.

