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 joints 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 out 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 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 or run into some fun issues when we talk about how
hash tags work and how you should store hash tags.
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 in 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 want 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 a ton of data.
I spent a long time making this here.
So this is what we'll be working with you 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 you know two or three things at a time.
So that's where we're going.
But in this section we 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 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 you know permission or using or having the right
to use somebody's images in my course.
OK.
So with that said the reason this is here like I said is 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.
For the most people use the iOS or Android app.
It's the same entity the same data.
And the first thing probably the most obvious is users.
So we're going to need some sort of users 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 like worry about how we actually upload photos and how you know what data type
we use to store them or anything like that.
We'll just say that it's an image you are.
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 you know I can't just click on this heart button
you know 10 times and add 10 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 hash tags and hash tags are fun.
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 and 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.
You know there's a couple 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 you know 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 phone me back or people can follow me and I don't have
to follow them back.
So how do we store that database.
OK 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 users photos comments on photos likes for photos as well hashtags which will apply to photos we're
not going to worry about hashtags.
Instead 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 in this year.
And then also followers follow these kind of difficult to talk about the different roles there but just
relationships or friendships or whatever you want to call it.
I just called followers and followers or followings.
So these are the main things you 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 you know insert data and do a bunch of exercises on your own.
But I'd like for you to brainstorm how you would do this.
What are the tables you need.
I'm going to you know 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 scheme of file and just write the create table statements whatever
you feel most comfortable with if you want to get a white board 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 in 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.
So that's really what I'd like you to do.
And then in the next couple of videos I'll step through in 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 the type of information.
Just focus on the core essentials.
So for something like users you don't have to be you know have a username and an email and a password
and I don't know what else extremum asked for like a birthday maybe in a profile and all of that you
don't need that just the basics maybe like username and whatever else you need to have at work or have
it really or associate with all the other entities.
It's easy to you know 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.
OK.
So again you don't need all the information you can possibly think of just the key parts.