1
00:00:00,510 --> 00:00:01,020
Hello.

2
00:00:01,050 --> 00:00:01,920
Welcome back.

3
00:00:01,950 --> 00:00:05,100
So this section is going to be a bit of a capstone.

4
00:00:05,220 --> 00:00:06,780
Maybe that's an exaggeration.

5
00:00:06,780 --> 00:00:11,640
It's going to be a summary of what we've been focusing on in the last couple of sections.

6
00:00:11,850 --> 00:00:17,520
So there's sort of this subsection of the course, the last three or four sections that we're all focused

7
00:00:17,520 --> 00:00:24,180
on, working with multiple tables, relationships between data tables, where we have foreign keys,

8
00:00:24,180 --> 00:00:30,990
referencing other tables, talking about relationships or associations like a one to many relationship

9
00:00:30,990 --> 00:00:32,970
or many to many relationship and so on.

10
00:00:32,970 --> 00:00:36,450
And then also joins, which is what we are focusing on as of late.

11
00:00:36,690 --> 00:00:42,090
So this is going to kind of wrap that all together into something bigger because so far we've really

12
00:00:42,090 --> 00:00:49,950
been working with two, maybe three tables, modeling a single relationship like customers and orders.

13
00:00:50,310 --> 00:00:53,280
But now we're going to take something a little bit bigger.

14
00:00:53,310 --> 00:00:56,350
We're going to focus on a bit of a case study.

15
00:00:56,370 --> 00:01:01,440
We're going to work with a site that you're probably familiar with, Instagram, and we're going to

16
00:01:01,440 --> 00:01:05,940
try and clone the database for some of the basic functionality.

17
00:01:05,940 --> 00:01:11,700
So we're not going to be diving into like advertising and keeping track of our users data and all of

18
00:01:11,700 --> 00:01:12,070
that.

19
00:01:12,090 --> 00:01:18,450
We're really focusing on the main big entities to a site like Instagram, things like photos and comments

20
00:01:18,450 --> 00:01:19,560
and users and so on.

21
00:01:20,040 --> 00:01:25,340
So again, we're not really scratching the surface as to how Instagram actually stores everything.

22
00:01:25,350 --> 00:01:28,830
We'll talk about what things work best for scaling.

23
00:01:29,230 --> 00:01:34,080
We'll run into some fun issues when we talk about how hashtags work and how you should store hashtags.

24
00:01:34,080 --> 00:01:39,480
So we will talk a little bit about performance, but this is really more about how do you design a schema

25
00:01:39,480 --> 00:01:45,630
for something that has, in our example, at least seven or eight tables, an Instagram, the real app.

26
00:01:45,810 --> 00:01:50,820
There's probably dozens of tables, but either way it's a big step up from two tables or three tables,

27
00:01:50,820 --> 00:01:51,390
max.

28
00:01:51,660 --> 00:01:56,070
And then the other thing I wanted to mention is that in this section, we're going to focus on designing

29
00:01:56,070 --> 00:01:56,760
the schema.

30
00:01:56,790 --> 00:02:01,080
It's going to be a bit of an exercise, but I'm also going to go through and do it myself.

31
00:02:01,140 --> 00:02:07,090
But then in the next section, I'm actually going to give you a bunch of data, like a ton of data.

32
00:02:07,110 --> 00:02:10,830
I spent a long time making this here, so this is what we'll be working with.

33
00:02:10,830 --> 00:02:15,690
You'll actually be inserting this so that for once we'll be able to work with big amounts of data,

34
00:02:15,690 --> 00:02:19,020
thousands of rows, and you'll see the impact that it has.

35
00:02:19,320 --> 00:02:24,720
And also hopefully it will feel a bit more realistic rather than two or three things at a time.

36
00:02:24,720 --> 00:02:25,800
So that's where we're going.

37
00:02:25,800 --> 00:02:28,680
But in this section we've got to create the schema first.

38
00:02:28,680 --> 00:02:30,450
We got to understand what we need.

39
00:02:30,450 --> 00:02:35,250
So let's start by taking a look at typical Instagram page.

40
00:02:35,430 --> 00:02:37,500
And yeah, some of you may know this.

41
00:02:37,620 --> 00:02:39,000
I'm in my spare time.

42
00:02:39,000 --> 00:02:43,200
I like to do photography, especially the landscape photography stuff around San Francisco.

43
00:02:43,200 --> 00:02:45,810
So I am using my own Instagram here.

44
00:02:45,810 --> 00:02:49,680
But that's not really why I'm not trying to get more exposure or anything.

45
00:02:50,010 --> 00:02:55,230
It's just that I don't want to run into any issues with permission or using or having a right to use

46
00:02:55,230 --> 00:02:56,730
some of these images in my course.

47
00:02:57,630 --> 00:03:02,280
So with that said, the reason this is here, like I said, it's not just to show you my photo, but

48
00:03:02,280 --> 00:03:06,300
mainly it's to help us understand and identify the key entities.

49
00:03:06,300 --> 00:03:12,750
So on this basic Instagram, this is from the web app, though most people use the iOS or Android app,

50
00:03:12,750 --> 00:03:15,000
it's the same entities, the same data.

51
00:03:15,330 --> 00:03:19,140
And the first thing probably the most obvious is users.

52
00:03:19,410 --> 00:03:23,160
So we're going to need some sort of user's table or a way to store users.

53
00:03:23,160 --> 00:03:27,060
And then moving on, we've got a really obvious one, which are the photos.

54
00:03:27,060 --> 00:03:29,130
We need a way of storing images or photos.

55
00:03:29,130 --> 00:03:34,740
Now, we're not going to worry about how we actually upload photos and how what data type we use to

56
00:03:34,740 --> 00:03:36,240
store them or anything like that.

57
00:03:36,240 --> 00:03:38,610
We'll just say that it's an image URL.

58
00:03:38,610 --> 00:03:42,240
So when we actually get to the exercise portion, I'll come back to that.

59
00:03:42,240 --> 00:03:48,960
So we need to store stuff about photos and then we've got things like likes, so we need to store likes

60
00:03:48,960 --> 00:03:50,370
for a given photo.

61
00:03:50,430 --> 00:03:58,530
But also remember there are some complications because I can't just click on this heart button ten times

62
00:03:58,530 --> 00:03:59,940
and add ten likes.

63
00:04:00,150 --> 00:04:04,530
I can only like it once, so we need to have something there as well.

64
00:04:04,710 --> 00:04:12,270
Maybe there's something we can do with our database and and then we've got hashtags and hashtags are

65
00:04:12,270 --> 00:04:12,510
fun.

66
00:04:12,510 --> 00:04:17,820
One, we're going to save that for last in this section because there's a lot of ways of doing them.

67
00:04:18,510 --> 00:04:23,040
We'll talk about at least three, and there's a couple of advantages and disadvantages to each.

68
00:04:23,040 --> 00:04:25,290
So hashtags, though, are important.

69
00:04:25,290 --> 00:04:31,770
Part of Instagram of a lot of tagging in general is a lot of web apps, whether it's blogging or Twitter

70
00:04:31,770 --> 00:04:33,060
or something like Instagram.

71
00:04:33,570 --> 00:04:39,540
And then we also have other components that I couldn't fit onto one page like comments here.

72
00:04:39,840 --> 00:04:44,520
That's really the big thing that we also need to be able to store comment information.

73
00:04:45,150 --> 00:04:50,700
And then there's one other big thing, which is followers and following.

74
00:04:50,700 --> 00:04:54,450
So the relationships between users, how do we store that?

75
00:04:55,230 --> 00:04:57,240
There's a couple of ways of doing that as well.

76
00:04:57,390 --> 00:04:59,820
But on Instagram, the way that it works and the way that.

77
00:04:59,890 --> 00:05:03,560
Our database should work is that it's a one way relationship.

78
00:05:03,580 --> 00:05:10,390
It's not like Facebook where I send a request to someone and we only become friends if they accept or

79
00:05:10,390 --> 00:05:11,090
vice versa.

80
00:05:11,110 --> 00:05:14,710
But both of us have to be kind of consenting friends.

81
00:05:14,980 --> 00:05:19,240
On Instagram, I can follow somebody and they don't have to follow me back or people can follow me and

82
00:05:19,240 --> 00:05:20,590
I don't have to follow them back.

83
00:05:20,830 --> 00:05:23,140
So how do we store that in a database?

84
00:05:23,800 --> 00:05:25,300
So that's really what we're focusing on.

85
00:05:25,330 --> 00:05:30,550
There are obviously other things like we're not going to store profile information, we're not going

86
00:05:30,550 --> 00:05:33,310
to store stuff about advertising, like I said.

87
00:05:33,940 --> 00:05:36,610
And that's just scratching the surface of what we're not storing.

88
00:05:36,640 --> 00:05:40,750
There's a bunch more, but we are going to store information.

89
00:05:40,750 --> 00:05:42,700
And I'm not saying these are the tables we need.

90
00:05:42,700 --> 00:05:43,960
We may need more tables.

91
00:05:43,960 --> 00:05:48,970
We may be able to get away with fewer tables, but these are the entities information we need to store.

92
00:05:48,970 --> 00:05:56,140
So user's photos, comments on photos, likes for photos as well.

93
00:05:56,440 --> 00:05:59,110
Hashtags which will apply to photos.

94
00:05:59,110 --> 00:06:04,900
We're not going to worry about hashtags inside of comments which you can do on the real Instagram.

95
00:06:04,900 --> 00:06:06,150
We're not going to do that.

96
00:06:06,160 --> 00:06:09,720
We can talk about how, but it just makes it a lot messier.

97
00:06:09,730 --> 00:06:13,110
And then also followers slash followers.

98
00:06:13,210 --> 00:06:19,390
It's kind of difficult to talk about the different roles there, but just relationships or friendships

99
00:06:19,390 --> 00:06:20,230
or whatever you want to call it.

100
00:06:20,230 --> 00:06:23,290
I just call it followers and followers or followings.

101
00:06:23,410 --> 00:06:25,570
So these are the main things we need to store.

102
00:06:25,570 --> 00:06:31,660
And the way that I'd like to structure this is I would like you to try and give this a shot so you don't

103
00:06:31,660 --> 00:06:36,490
have to actually insert data and do a bunch of exercises on your own.

104
00:06:36,490 --> 00:06:40,210
But I'd like for you to at least brainstorm how you would do this.

105
00:06:40,210 --> 00:06:41,680
What are the tables you need?

106
00:06:41,710 --> 00:06:47,500
I'm going to in the next in the ensuing six or seven videos, go through each table we need and talk

107
00:06:47,500 --> 00:06:52,870
about my solution, but it would be great if you would take the time to just kind of think about it,

108
00:06:52,870 --> 00:06:58,990
jot it down, either draw diagrams out, create a schema file, and just write the create table statements,

109
00:06:58,990 --> 00:07:00,610
whatever you feel most comfortable with.

110
00:07:00,610 --> 00:07:03,790
If you want to get a whiteboard or post-it notes, whatever works.

111
00:07:03,790 --> 00:07:07,140
But think about how you would do this if you were creating.

112
00:07:07,150 --> 00:07:11,380
It doesn't matter if you're a developer, if you know how to code and other languages or not, but if

113
00:07:11,380 --> 00:07:16,900
you were creating an application like Instagram with the functionality we described, what would you

114
00:07:16,900 --> 00:07:20,440
sit down and start with for your database and how would you brainstorm it?

115
00:07:20,470 --> 00:07:22,450
So that's really what I'd like you to do.

116
00:07:22,450 --> 00:07:27,370
And then in the next couple of videos, I'll step through and bite sized chunks.

117
00:07:27,370 --> 00:07:33,190
I want to make sure that these are short videos, which I know I have said before and maybe have not

118
00:07:33,370 --> 00:07:35,440
held up my end of the bargain all the time.

119
00:07:35,440 --> 00:07:40,910
But these will be shorter and they'll go through my thought process and how I created it.

120
00:07:40,930 --> 00:07:46,030
Oh, and before I forget, if you're going to attempt this, when you're creating the tables that you

121
00:07:46,030 --> 00:07:49,930
need to store this type of information, just focus on the core essentials.

122
00:07:49,930 --> 00:07:56,260
So for something like users, you don't have to have a username and an email and a password.

123
00:07:56,260 --> 00:08:02,380
And I don't know what else Instagram asked for, like a birthday maybe, and a profile and all of that.

124
00:08:02,380 --> 00:08:03,100
You don't need that.

125
00:08:03,100 --> 00:08:06,160
Just the basics, maybe like username and whatever else.

126
00:08:06,160 --> 00:08:11,950
You need to have it work or have it relate or associate with all of the other entities.

127
00:08:12,190 --> 00:08:15,940
It's it's easy to add the individual things to a table.

128
00:08:15,940 --> 00:08:22,180
Like for users adding an email column, it's not hard, but then figuring out how you have users connected

129
00:08:22,180 --> 00:08:25,690
to likes, for instance, that's what I'd like you to focus on.

130
00:08:26,170 --> 00:08:31,420
So again, you don't need all the information you can possibly think of just the key parts.