Hello.
Welcome back.
So this section is going to be a bit of a capstone.
Maybe that's an exaggeration.
It's going to be a summary of what we've been focusing on in the last couple of sections.
So there's sort of this subsection of the course, the last three or four sections that we're all focused
on, working with multiple tables, relationships between data tables, where we have foreign keys,
referencing other tables, talking about relationships or associations like a one to many relationship
or many to many relationship and so on.
And then also joins, which is what we are focusing on as of late.
So this is going to kind of wrap that all together into something bigger because so far we've really
been working with two, maybe three tables, modeling a single relationship like customers and orders.
But now we're going to take something a little bit bigger.
We're going to focus on a bit of a case study.
We're going to work with a site that you're probably familiar with, Instagram, and we're going to
try and clone the database for some of the basic functionality.
So we're not going to be diving into like advertising and keeping track of our users data and all of
that.
We're really focusing on the main big entities to a site like Instagram, things like photos and comments
and users and so on.
So again, we're not really scratching the surface as to how Instagram actually stores everything.
We'll talk about what things work best for scaling.
We'll run into some fun issues when we talk about how hashtags work and how you should store hashtags.
So we will talk a little bit about performance, but this is really more about how do you design a schema
for something that has, in our example, at least seven or eight tables, an Instagram, the real app.
There's probably dozens of tables, but either way it's a big step up from two tables or three tables,
max.
And then the other thing I wanted to mention is that in this section, we're going to focus on designing
the schema.
It's going to be a bit of an exercise, but I'm also going to go through and do it myself.
But then in the next section, I'm actually going to give you a bunch of data, like a ton of data.
I spent a long time making this here, so this is what we'll be working with.
You'll actually be inserting this so that for once we'll be able to work with big amounts of data,
thousands of rows, and you'll see the impact that it has.
And also hopefully it will feel a bit more realistic rather than two or three things at a time.
So that's where we're going.
But in this section we've got to create the schema first.
We got to understand what we need.
So let's start by taking a look at typical Instagram page.
And yeah, some of you may know this.
I'm in my spare time.
I like to do photography, especially the landscape photography stuff around San Francisco.
So I am using my own Instagram here.
But that's not really why I'm not trying to get more exposure or anything.
It's just that I don't want to run into any issues with permission or using or having a right to use
some of these images in my course.
So with that said, the reason this is here, like I said, it's not just to show you my photo, but
mainly it's to help us understand and identify the key entities.
So on this basic Instagram, this is from the web app, though most people use the iOS or Android app,
it's the same entities, the same data.
And the first thing probably the most obvious is users.
So we're going to need some sort of user's table or a way to store users.
And then moving on, we've got a really obvious one, which are the photos.
We need a way of storing images or photos.
Now, we're not going to worry about how we actually upload photos and how what data type we use to
store them or anything like that.
We'll just say that it's an image URL.
So when we actually get to the exercise portion, I'll come back to that.
So we need to store stuff about photos and then we've got things like likes, so we need to store likes
for a given photo.
But also remember there are some complications because I can't just click on this heart button ten times
and add ten likes.
I can only like it once, so we need to have something there as well.
Maybe there's something we can do with our database and and then we've got hashtags and hashtags are
fun.
One, we're going to save that for last in this section because there's a lot of ways of doing them.
We'll talk about at least three, and there's a couple of advantages and disadvantages to each.
So hashtags, though, are important.
Part of Instagram of a lot of tagging in general is a lot of web apps, whether it's blogging or Twitter
or something like Instagram.
And then we also have other components that I couldn't fit onto one page like comments here.
That's really the big thing that we also need to be able to store comment information.
And then there's one other big thing, which is followers and following.
So the relationships between users, how do we store that?
There's a couple of ways of doing that as well.
But on Instagram, the way that it works and the way that.
Our database should work is that it's a one way relationship.
It's not like Facebook where I send a request to someone and we only become friends if they accept or
vice versa.
But both of us have to be kind of consenting friends.
On Instagram, I can follow somebody and they don't have to follow me back or people can follow me and
I don't have to follow them back.
So how do we store that in a database?
So that's really what we're focusing on.
There are obviously other things like we're not going to store profile information, we're not going
to store stuff about advertising, like I said.
And that's just scratching the surface of what we're not storing.
There's a bunch more, but we are going to store information.
And I'm not saying these are the tables we need.
We may need more tables.
We may be able to get away with fewer tables, but these are the entities information we need to store.
So user's photos, comments on photos, likes for photos as well.
Hashtags which will apply to photos.
We're not going to worry about hashtags inside of comments which you can do on the real Instagram.
We're not going to do that.
We can talk about how, but it just makes it a lot messier.
And then also followers slash followers.
It's kind of difficult to talk about the different roles there, but just relationships or friendships
or whatever you want to call it.
I just call it followers and followers or followings.
So these are the main things we need to store.
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
have to actually insert data and do a bunch of exercises on your own.
But I'd like for you to at least brainstorm how you would do this.
What are the tables you need?
I'm going to in the next in the ensuing six or seven videos, go through each table we need and talk
about my solution, but it would be great if you would take the time to just kind of think about it,
jot it down, either draw diagrams out, create a schema file, and just write the create table statements,
whatever you feel most comfortable with.
If you want to get a whiteboard or post-it notes, whatever works.
But think about how you would do this if you were creating.
It doesn't matter if you're a developer, if you know how to code and other languages or not, but if
you were creating an application like Instagram with the functionality we described, what would you
sit down and start with for your database and how would you brainstorm it?
So that's really what I'd like you to do.
And then in the next couple of videos, I'll step through and bite sized chunks.
I want to make sure that these are short videos, which I know I have said before and maybe have not
held up my end of the bargain all the time.
But these will be shorter and they'll go through my thought process and how I created it.
Oh, and before I forget, if you're going to attempt this, when you're creating the tables that you
need to store this type of information, just focus on the core essentials.
So for something like users, you don't have to have a username and an email and a password.
And I don't know what else Instagram asked for, like a birthday maybe, and a profile and all of that.
You don't need that.
Just the basics, maybe like username and whatever else.
You need to have it work or have it relate or associate with all of the other entities.
It's it's easy to add the individual things to a table.
Like for users adding an email column, it's not hard, but then figuring out how you have users connected
to likes, for instance, that's what I'd like you to focus on.
So again, you don't need all the information you can possibly think of just the key parts.