1
00:00:00,150 --> 00:00:00,690
All right.

2
00:00:00,690 --> 00:00:01,950
Welcome to the next section.

3
00:00:02,220 --> 00:00:07,740
So we're continuing on the same similar path, talking about relationships, talking about joints,

4
00:00:07,740 --> 00:00:13,230
more complex data, multiple tables, and connecting those tables, drawing lines between them.

5
00:00:13,380 --> 00:00:19,500
However, we're now moving on to our next relationship, which is the many to many relationship.

6
00:00:19,500 --> 00:00:25,380
So we saw how to do a one to many, which is a pretty standard relationship, and it's pretty common.

7
00:00:25,380 --> 00:00:27,660
Many to many is is common as well.

8
00:00:27,780 --> 00:00:30,510
Maybe not as common, but still very, very common.

9
00:00:30,900 --> 00:00:32,580
So we're going to see how to do it.

10
00:00:32,580 --> 00:00:36,830
And the reason that I saved it for last is that it's a little bit trickier.

11
00:00:36,840 --> 00:00:37,770
It's not hard.

12
00:00:37,770 --> 00:00:41,520
I don't want to scare you away, but you'll see there's a little more setup involved.

13
00:00:41,520 --> 00:00:46,560
And then once we've discussed on a high level how many to many relationships typically work, we're

14
00:00:46,560 --> 00:00:53,520
going to implement one using some relatively complex data that I spent way too long creating.

15
00:00:53,790 --> 00:00:55,590
And then we'll do some problems together.

16
00:00:55,590 --> 00:00:59,070
So this section will actually serve more as a code along.

17
00:00:59,280 --> 00:01:03,570
So I would encourage you, if you don't typically I encourage you to follow along.

18
00:01:03,570 --> 00:01:07,980
We're going to use some relatively complex data, like I said, and we'll just go through it, do a

19
00:01:07,980 --> 00:01:13,770
couple of exercises together, and I'm going to use it as a way to illustrate how joints work on a many

20
00:01:13,770 --> 00:01:15,000
to many relationship.

21
00:01:15,000 --> 00:01:19,710
So let's start in this video just by talking about the many to many relationship.

22
00:01:20,550 --> 00:01:26,250
So we kind of discussed it briefly in the last section, but here are a couple of examples.

23
00:01:26,430 --> 00:01:31,050
So books and authors harkening back to that last section where we saw books.

24
00:01:31,680 --> 00:01:34,080
A book can have multiple authors, right?

25
00:01:34,110 --> 00:01:40,860
There are lots of books that are written by co authors or teams of if it's a collection, if it's an

26
00:01:40,860 --> 00:01:47,460
anthology, there might be 20 authors, and each of those authors can then have multiple books.

27
00:01:47,460 --> 00:01:52,620
So it's a many to many relationship books can have many authors, authors can have many books.

28
00:01:53,190 --> 00:02:00,270
Or another common example is blog post or some sort of content, whether it's an Instagram photo, a

29
00:02:00,270 --> 00:02:07,560
Tumblr, whatever you call it, a Tumblr post, or just a traditional blog, some sort of content and

30
00:02:07,560 --> 00:02:10,130
then tags tags.

31
00:02:10,139 --> 00:02:18,660
By that I mean things like Sunset picture or there's some really like Vanlife, if you're familiar with

32
00:02:18,660 --> 00:02:26,280
that hashtag, or I do a lot of photography, so I see a lot of tags that are things like, like masterpiece

33
00:02:26,640 --> 00:02:31,020
landscape photography and then, you know, insta good.

34
00:02:31,020 --> 00:02:37,470
If you use Instagram, there's all these different tags that are out there and each post can have multiple

35
00:02:37,470 --> 00:02:38,070
tags.

36
00:02:38,310 --> 00:02:42,180
I mean, there's a limit sometimes, but on Instagram you can have 30 hash tags.

37
00:02:42,180 --> 00:02:50,100
I think it is on a photo, and then each of those tags can then have as many posts or photos as necessary.

38
00:02:50,190 --> 00:02:51,990
So that's another example.

39
00:02:52,110 --> 00:02:55,740
And the final example I have here is students in classes.

40
00:02:56,160 --> 00:03:02,640
So this is we're talking about maybe college students or university students where students have multiple

41
00:03:02,640 --> 00:03:05,550
classes, they pick their classes at the beginning of the semester.

42
00:03:06,090 --> 00:03:12,330
One student can have usually must have multiple classes unless maybe it's your last semester, but you

43
00:03:12,330 --> 00:03:16,110
have multiple classes that you go to that you're related to.

44
00:03:16,110 --> 00:03:19,530
And then each class is going to have multiple students in it.

45
00:03:20,390 --> 00:03:22,320
And that to many to many relationship.

46
00:03:22,340 --> 00:03:25,760
And the one that we'll be working with in this section is actually not listed here.

47
00:03:25,760 --> 00:03:26,690
So a little different.

48
00:03:26,720 --> 00:03:27,830
And to start off.

49
00:03:28,750 --> 00:03:34,270
I want you to imagine that we're building a TV show reviewing application like, Yes, I know we don't

50
00:03:34,270 --> 00:03:38,010
need any more of those, but imagine something like Internet movie database.

51
00:03:38,020 --> 00:03:39,640
Very, very simple version, though.

52
00:03:39,820 --> 00:03:46,300
But we're working with TV shows exclusively, and the key idea is that we can have someone who signs

53
00:03:46,300 --> 00:03:48,400
up as a reviewer.

54
00:03:49,150 --> 00:03:59,680
We can have TV shows that, let's say some of the ones I'm using Fargo, the TV show Archer General

55
00:03:59,680 --> 00:04:01,960
Hospital, any series.

56
00:04:02,290 --> 00:04:10,450
And a reviewer can then rate any of those series so I could sign up and rate Archer as an 8.5 out of

57
00:04:10,450 --> 00:04:10,800
ten.

58
00:04:10,810 --> 00:04:17,740
So to accomplish this relationship and many to many relationship where we're basically associating users

59
00:04:17,740 --> 00:04:23,980
or reviewers, we'll call them with TV shows, we need to actually use three tables.

60
00:04:24,340 --> 00:04:28,960
We use what's known as a join table or a union table.

61
00:04:30,190 --> 00:04:32,380
So we have our series data up here.

62
00:04:32,380 --> 00:04:33,970
So these would be the TV shows.

63
00:04:34,090 --> 00:04:36,670
And then down here we have our viewers data.

64
00:04:36,670 --> 00:04:38,410
So these would be just names.

65
00:04:38,410 --> 00:04:40,060
In our case, we'll keep it very simple.

66
00:04:40,180 --> 00:04:44,350
This will be Colt or this will be Charlie or whatever it is.

67
00:04:45,310 --> 00:04:49,450
But the way that they're connected is through a third table.

68
00:04:50,040 --> 00:04:51,840
Through a reviews table.

69
00:04:52,600 --> 00:04:56,080
So this will make sense when I show you the schema in just a moment.

70
00:04:56,350 --> 00:04:59,020
But think about it this way Series exists on their own.

71
00:04:59,020 --> 00:05:00,220
They're just TV shows.

72
00:05:01,150 --> 00:05:02,770
Reviewers exist on their own.

73
00:05:03,340 --> 00:05:06,010
They have nothing to do with the series out of the box.

74
00:05:06,010 --> 00:05:07,420
It's just someone's name.

75
00:05:07,510 --> 00:05:12,100
But they then are associated through this reviews table.

76
00:05:12,100 --> 00:05:17,740
So this reviews table will have information on the TV show that's being reviewed and then the reviewer

77
00:05:17,740 --> 00:05:23,080
who is doing it, as well as a third piece of information, which is the rating, what's the number,

78
00:05:23,080 --> 00:05:25,600
the numeric representation of that review.

79
00:05:26,170 --> 00:05:28,690
So without further ado, let's take a look at our schema.

80
00:05:29,920 --> 00:05:36,820
We have three tables reviewers over here with an ID primary key, first name and last name.

81
00:05:36,820 --> 00:05:39,770
And of course, in a real application we would have more data here, right?

82
00:05:39,820 --> 00:05:43,690
We would have email, we'd be storing our password in a safe, protected way.

83
00:05:44,080 --> 00:05:49,360
We would have things like registration date may be less active, all that kind of stuff.

84
00:05:49,360 --> 00:05:53,590
But keep it simple here so we can focus on the relationship, focus on the joints.

85
00:05:53,680 --> 00:05:55,690
I'm only doing first name and last name.

86
00:05:56,440 --> 00:06:03,970
Then we also have series and you know, this is something I actually debated the plural of series in

87
00:06:03,970 --> 00:06:04,690
English.

88
00:06:05,020 --> 00:06:08,260
Modern, traditional spoken English is just a series.

89
00:06:08,260 --> 00:06:14,170
However, a lot of people would argue that we should name this table series is with an S on the end

90
00:06:14,170 --> 00:06:16,720
just to make it clear that that's a table name.

91
00:06:17,380 --> 00:06:19,510
I'm not exactly a fan of that.

92
00:06:20,200 --> 00:06:25,360
It's really determined by the project or the team you're working with or by you if you're working on

93
00:06:25,360 --> 00:06:26,110
your own thing.

94
00:06:26,110 --> 00:06:32,770
I've worked on projects where we had to abide by that, where the plural was always with an S afterward,

95
00:06:33,430 --> 00:06:36,040
even if that meant mouses instead of mice.

96
00:06:36,040 --> 00:06:38,980
But I'm going to leave it as series just because I think it's simplest.

97
00:06:38,980 --> 00:06:42,490
So series has a primary key ID a title.

98
00:06:42,490 --> 00:06:46,150
So that would be Fargo, which is a TV show as well.

99
00:06:46,170 --> 00:06:47,350
Really good TV show, actually.

100
00:06:47,350 --> 00:06:48,310
Not just a movie.

101
00:06:48,490 --> 00:06:55,750
So Fargo or Halt and Catch Fire, Seinfeld and then the release here.

102
00:06:56,020 --> 00:06:58,810
So 2014 or 2002 or whatever it is.

103
00:06:58,810 --> 00:07:01,510
And then a genre which we're going to keep very simple.

104
00:07:01,540 --> 00:07:05,290
We're only going to have animation, comedy and drama.

105
00:07:05,650 --> 00:07:07,900
So just a couple of examples of genres.

106
00:07:08,290 --> 00:07:11,740
But again, these on their own are just separate entities.

107
00:07:11,740 --> 00:07:17,440
Where they're joined is through this third reviews table, which is what's known as a join or union

108
00:07:17,440 --> 00:07:18,010
table.

109
00:07:18,040 --> 00:07:24,610
It's connecting these two and the way it's doing that well, first of all, it has an ID, nothing special

110
00:07:24,610 --> 00:07:25,060
there.

111
00:07:25,360 --> 00:07:28,600
Every review has an ID, there's a rating.

112
00:07:28,600 --> 00:07:35,800
So this would be 5.0 for a mediocre show or 9.9 for a fantastic show.

113
00:07:36,130 --> 00:07:38,350
And then the series ID.

114
00:07:38,350 --> 00:07:42,910
So that's going to be a foreign key pointing to the series table.

115
00:07:43,330 --> 00:07:45,550
So what series is this, Fargo?

116
00:07:45,580 --> 00:07:46,990
Is this Seinfeld?

117
00:07:47,260 --> 00:07:48,370
And then the same?

118
00:07:48,370 --> 00:07:51,550
Well, not the exact same, but the same idea for reviewer ID.

119
00:07:52,120 --> 00:07:56,680
It's going to be a foreign key pointing to the reviewer's table the ID feels.

120
00:07:57,420 --> 00:08:05,490
So then a single review will have an ID, it will have a rating 9.0 a series ID like one, and then

121
00:08:05,490 --> 00:08:09,270
a reviewer ID, which could also be one there pointing to different tables.

122
00:08:09,510 --> 00:08:16,170
So this is a centralized repository or centralized place for our information to be stored.

123
00:08:16,500 --> 00:08:19,740
It's connecting these two tables through this table.

124
00:08:20,640 --> 00:08:23,070
So hopefully you can see how this is going to work.

125
00:08:23,070 --> 00:08:24,660
Basically, reviews on its own.

126
00:08:24,660 --> 00:08:27,120
It's going to be a pretty ugly table to look at.

127
00:08:27,120 --> 00:08:28,590
It's just going to be numbers.

128
00:08:28,590 --> 00:08:33,539
It will have an ID, which is the number rating is the number series, ideas number and reviewer IDs

129
00:08:33,539 --> 00:08:34,140
and number.

130
00:08:34,140 --> 00:08:38,130
So looking at it on its own, it's actually going to be very confusing.

131
00:08:38,130 --> 00:08:39,270
It's not going to make sense.

132
00:08:39,270 --> 00:08:46,650
So we'll have to use joints to be able to understand things so that we could use a join to replace series

133
00:08:46,650 --> 00:08:55,260
ID with the name of the series so that it says Fargo instead of one, or it will say Blue Steel instead

134
00:08:55,260 --> 00:08:58,440
of reviewer ID three and so on.

135
00:08:58,860 --> 00:09:00,900
So that's our basic idea here.

136
00:09:01,710 --> 00:09:07,170
Here's a simpler here's an example with some simple data very, very simple two reviewers.

137
00:09:07,440 --> 00:09:12,030
So we've got Blue Steel and Wyatt Earp idea of one and two.

138
00:09:12,150 --> 00:09:14,910
Then we've got two series, Archer and Fargo.

139
00:09:15,000 --> 00:09:19,050
If you're not familiar with these shows, don't worry, that's not necessarily relevant.

140
00:09:19,470 --> 00:09:23,010
Archer Animation released first in 2009.

141
00:09:23,010 --> 00:09:27,900
Fargo is a drama with a little comedy released in 2014.

142
00:09:28,140 --> 00:09:30,900
Archer has idea of one, Fargo has idea of two.

143
00:09:31,320 --> 00:09:32,430
So they're independent.

144
00:09:32,430 --> 00:09:38,610
But then our reviews table well somebody reviewed something with a rating of 8.9.

145
00:09:39,600 --> 00:09:44,010
The person who did that has a reviewer idea of one so that is blue steel.

146
00:09:44,010 --> 00:09:47,760
So Blue Steel reviewed something and gave it an 8.9.

147
00:09:47,760 --> 00:09:49,260
Well, what did she review?

148
00:09:49,530 --> 00:09:51,360
Whatever has series ID two.

149
00:09:51,360 --> 00:09:54,900
So Fargo so Blue gave Fargo an 8.9.

150
00:09:55,470 --> 00:09:59,700
Then we have a 9.5 down here and the reviewer ID is two.

151
00:09:59,700 --> 00:10:01,080
So that's Wyatt Earp.

152
00:10:01,590 --> 00:10:04,380
This field or this row here in reviewers.

153
00:10:04,380 --> 00:10:10,590
Wyatt Earp also reviewed Fargo because it's ID too, as we can see, corresponds to Fargo.

154
00:10:10,590 --> 00:10:16,830
So essentially what we're storing here, Fargo has been rated twice, once by blue as a 8.9 and once

155
00:10:16,830 --> 00:10:18,510
by why it is a 9.5.

156
00:10:19,050 --> 00:10:22,470
So as you can see, the data is not that pretty in this table.

157
00:10:22,470 --> 00:10:24,180
And this is only with two reviews.

158
00:10:24,180 --> 00:10:29,430
We'll be working with somewhere around 100 reviews, which is a lot of data to look at on its own.

159
00:10:29,430 --> 00:10:35,070
And so we'll really be focusing on making that data understandable readable, which is really the point

160
00:10:35,370 --> 00:10:36,810
of this entire exercise.

161
00:10:37,200 --> 00:10:38,880
So we're pretty much done here.

162
00:10:38,880 --> 00:10:43,710
In this first video, we didn't actually do anything except discuss our schema, but in the next video

163
00:10:43,710 --> 00:10:48,030
we'll actually go about implementing the tables and inserting some data on our own.