All right.
So this is the first video of basically talking about the tables in need and implementing the tables
who need for our Instagram clone.
And the first thing we'll start with is users.
Users is a pretty obvious place to start.
I guess another one might be photos, but users is at the core of everything if you think about it,
because users are connected to, you know, photos are connected to comments, they're connected to
likes, they're not connected to hashtags, but pretty much everything else relates to users.
So what I'm going to do here is something super simple.
Like I said in the last video, there's a lot more we can store here email password, like 20 different
things, location, city, IP address, all that stuff.
We're just going to focus on the basics.
So an ID, a username and a created it and created that will just be the day the user or the timestamp.
When the user signs up, it will have a default value of now or current timestamp username is just going
to be a var char var car and ID will be a primary key and we'll be referencing this ID in other places.
For example, when we talk about photos, photos will have a foreign key referencing the user ID, so
we'll go ahead.
And the first thing I have is just an empty directory I made just to store this and I'm just going to
make a new file and I'll just call it Instagram, underscore and clone underscore, clone dot SQL.
I'll open that up and this is where I'll work.
So this is where I'll do a create table users.
And like we said, we're going to have an ID, we'll have a username and a created add.
So id is going to be integer or just int I'll do integer just because I've been doing int, but it's
an alias for integer.
It's good to see both.
And we'll have it auto increment and it's a primary key.
Great.
Comma, come back to username and just a second created that will do time stamp with a default of now.
And you can also do current timestamp and remember you could do date time as well.
Time stamp is just smaller.
It's easier to store.
Okay, so then we've got username.
We know it's just going to be a var cha 255.
There's another constraint that we could add though, which is we want username to be unique.
We don't want anyone to be able to sign up with another with the same username essentially.
So we can add unique.
And you might be wondering, well then why would we have id be the primary key?
Why not make username primary key?
You absolutely could do that.
But if you're going to have a foreign key somewhere referencing username and you're working with a long
string, if someone's username is something massive like that, because we could do 255 characters,
that's going to be slower and more annoying to search potentially.
Well, not potentially, definitely slower if you have thousands and thousands of entries compared to
a smaller integer.
So it's good to have a primary key be an integer, but we can still have this unique constraint and
we can also add, not know, we don't want anyone to be able to sign up without a username.
I'm going to go ahead and make a new database to put this in, but I'm going to do it in this file.
So I'll do a create database and I'll also just call it Instagram or IG clone, and then I'll use IG
clone just like that.
So now every time I run this file, it's going to make this database use Instagram, clone the new database
and create the table.
And the reason I'm doing that is just one to show you that you can We haven't really seen using these
commands in a file, but also as we go, we may want to rerun this and recreate the user's table or
another table if we realized we messed something up or we need to change something, we're not going
to have any data in there yet.
So it's super easy just to create the database again, drop it, then create it and then use it and
then create the table users.
So with that said, let's try this going to do source and I need to reference this directory slash Instagram
clone SQL.
Okay.
And just to double check, describe users, it looks good.
So if you are good with that, go ahead and move on.
Next up, we're going to talk about photos, but I will spend a minute or two just adding one or two
users so that we can work with them in this section.
Remember, in the next section, we're going to have a massive data set that I'm going to give you.
But if we want, we can just play around with some data.
So I'll do an insert into users.
And just insert username and let's just do two or three.
So we'll have blue the cat.
And we could also have, I don't know, Charlie Brown and then I'll just put myself in there just like
that.
Okay.
So what we could do now, if I just do if I resource this whole thing, I'll get an error because it
tells me I can't create this database.
Instagram clone.
It already exists.
So I could either drop the database first and I'm going to do that for now.
You typically don't want to do that.
You don't want to drop a database ever if you can avoid it.
But if you're there's no data in there yet, or if the only data that will be in there is in the same
file like we're doing here, just testing it out.
This makes it easy because I can just run this one command.
It will erase the database, recreate it, and get all the new tables that I need in there, as well
as the new data.
So now I can do a select star from users.
And there we go.
We've got created that in there.
Automatically IDs automatic and our three usernames.
Next up, photos.