All right.
Moving right along here.
We're working with photos this time, and the easiest way to store photos is just a single table photos.
But of course, they need to be related to a particular user who created the photos.
So we'll have an ID and all we'll store about an image itself is the image URL, which will just be
a string.
We could have added a caption as a var char.
Excuse me, not a string.
We could have added to caption.
We could have added location data on Instagram.
You can say where it was taken.
You know, there's all sorts of things that we could do, but we're just focusing on image URL in our
case and then a user ID, very important foreign key pointing to users ID.
And then we also have created it just like we had with users.
Just the nice way to be able to sort photos by most recent, to least recent, or figure out the first
photo somebody posted.
Or if one photo is posted before another, if one looks like it's this actually happened to me, or
people take a photo of mine and repost it claiming that it's their own.
It doesn't happen often, but two times actually by the same person.
And the person actually like flipped the image, like mirrored it, posted it, claimed that it was
their own.
But of course I could just show that mine was posted first, thankfully, because there's a date.
I mean, it didn't really matter anyways, right?
It's just photos on a social network, but still having that created add in there helped me there.
So let's implement it now.
It's pretty straightforward.
Just create table photos.
We're going to have an ID.
We'll also have our photo or image URL, whatever we want to call that.
We'll have user ID and then create a DAT.
So ID is an integer or int auto increment.
Primary key will be referencing photos dot ID from other tables because things like comments are associated
with a particular photo or likes are and tags as well.
So we need that primary key.
Their image URL will just be a var char 255.
But we should add not null.
We don't want that to be empty or to be null.
That's a big problem because there'd be no photo to display and then user ID that would be an integer.
And let's also make that not null.
A photo needs to be associated with the user.
We can't have an orphan photo that has no user and then created.
That will just be another timestamp with the default value of now the time is created, but we still
need one more piece, which is our foreign key constraint, which is going to pertain to user underscore
ID, which is referencing or references the users table, the ID field, spell that right reference
ID, there we go.
And that will create our photos table.
So if I just resource this whole thing.
Now, if I do show tables, we have both tables in there.
If I do a describe photos, we've got photos there.
Everything looks good.
And just like before, if you want to stick around, I'm going to insert one or two photos in here associated
with our three users.
But I'm done with creating the schema, so you feel free to move on to the next one, which is comments.
But if you want you can stick around as I do an insert into photos and we'll just do an image URL and
a user ID.
Those are the two things we have to supply ourself values.
And our first one, let's say Blue, submits it and we'll come back to the URL.
But Blue's ID is going to be one and if we weren't sure could do select star from users can see blue
is one, Charlie Brown is two and Colt is three.
So we'll just do something silly.
Let's just say the URL is just random like that.
I'll duplicate that a couple of times and cloud nine it's command shift dx or control shift dx on a
PC.
And then but here's another random thing.
This time it's submitted by Charlie Brown.
Let's say Charlie Brown has another one as well like that.
Okay.
And I haven't submitted any photos.
That should work if we run our source again this time.
So it dropped the database, recreated it, switched to it with the user command, created our two tables,
inserted all of this data.
Now, if we can do select star from photos and we should have three simple photos there with a user
ID.
We're now at the point where we could do a simple join if we wanted to, to figure out who posted these
photos.
So again, definitely, if you feel good about this, move on and don't waste time watching this.
But if you want the practice, we could just do a select.
Let's say I wanted to have the image URL created at and the name the username of the person instead
of the user ID.
So we'll just do a select.
Star to start from.
Photos join and we'll just do a default inner join users on and we're going to do it where photos dot
user ID equals user's ID.
So if we start with that.
There's a lot there so let's thin it out to just be photos dot image url comma users dot username.
Is that what it is?
Username?
Yes.
Just like that.
Copy it.
Okay.
And now we can see Blue.
The cat created that and Charlie Brown created those two.
All right.
And what I'm going to do just to set a pattern for the next few videos is move these joints and things
that I'm just playing around with down to the bottom and comment them out.
We don't want those to run, but I'll leave the insert statements here as well as to create tables.
All right.
Moving on.