1
00:00:00,480 --> 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,840
on working with multiple tables, relationships between data tables where we have foreign keys, referencing

8
00:00:24,840 --> 00:00:31,530
other tables, talking about relationships or associations like a one to many relationship or many to

9
00:00:31,530 --> 00:00:32,970
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,980
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,860
We're going to work with a site that you're probably familiar with Instagram, and we're going to try

16
00:01:01,860 --> 00:01:05,940
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,590
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,730
the schema.

30
00:01:56,760 --> 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:39,000
And yeah, some of you may know this, I'm in my spare time.

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

42
00:02:43,200 --> 00:02:48,930
So I am using my own Instagram here, but that's not really why I'm not trying to get more exposure

43
00:02:48,930 --> 00:02:49,680
or anything.

44
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

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

46
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

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

48
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,

49
00:03:12,750 --> 00:03:19,140
it's the same entities, the same data, and the first thing, probably the most obvious is users.

50
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.

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

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

53
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

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

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

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

57
00:03:42,240 --> 00:03:47,760
So we need to store stuff about photos and then we've got things like likes.

58
00:03:47,760 --> 00:03:50,370
So we need to store likes for a given photo.

59
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

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

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

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

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

64
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.

65
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.

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

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

68
00:04:31,080 --> 00:04:33,060
Twitter or something like Instagram.

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

70
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.

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

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

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

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

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

76
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

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

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

79
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

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

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

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

83
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

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

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

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

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

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

89
00:05:43,960 --> 00:05:45,910
We may be able to get away with fewer tables.

90
00:05:45,910 --> 00:05:48,970
But these are the entities information we need to store.

91
00:05:48,970 --> 00:05:51,100
So users photos.

92
00:05:51,760 --> 00:05:59,110
Comments on photos, likes for photos as well as hashtags which will apply to photos.

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

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

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

96
00:06:09,730 --> 00:06:13,170
And then also followers slash followers.

97
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

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

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

100
00:06:23,380 --> 00:06:25,570
So these are the main things you need to store.

101
00:06:25,600 --> 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

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

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

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

105
00:06:41,710 --> 00:06:48,460
I'm going to in the next ensuing six or seven videos go through each table we need and talk about my

106
00:06:48,460 --> 00:06:49,240
solution.

107
00:06:49,300 --> 00:06:54,310
But it would be great if you would take the time to just kind of think about it, jot it down, either

108
00:06:54,310 --> 00:06:59,560
draw diagrams out, create a schema file, and just write the create table statements, whatever you

109
00:06:59,560 --> 00:07:00,610
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,150
But think about how you would do this if you were creating.

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

113
00:07:10,960 --> 00:07:16,780
But if you were creating an application like Instagram with the functionality we described, what would

114
00:07:16,780 --> 00:07:20,430
you 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:39,280
held up my end of the bargain all the time, but these will be shorter and they'll go through my thought

119
00:07:39,280 --> 00:07:40,920
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:57,190
So for something like users, you don't have to have a username and an email and a password and I don't

123
00:07:57,190 --> 00:08:02,380
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:18,370
It's it's easy to add the individual things to a table, like for users adding an email column, it's

128
00:08:18,370 --> 00:08:24,010
not hard, but then figuring out how you have users connected to likes, for instance.

129
00:08:24,010 --> 00:08:25,690
That's what I'd like you to focus on.

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