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 idea and all will store about an image itself is the image you Arel which will just
be a string.
You know we could have added a caption as if our char excuse me not string 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 you are all in
our case.
And then a user id very important foreign key pointing to users dot ID.
And then we also have created ad just like we had with users just a nice way to be able to you know
sort photos by most recent to least recent or figure out the first photos somebody posted or if one
photo was posted before another if you know what one looks like it's this is actually happened to me.
People take a photo of mine and repost it claiming that it's their own.
Doesn't happen often but two times actually the same person and the person actually flipped the image
like mirrored it posted it claimed that it was their own.
But of course it could just show that mine was posted first thankfully because there's a date.
I mean it didn't really matter anyways.
Right.
Just photos on their social network.
But still having that created out there helps me there.
So let's implement it now.
It's pretty straightforward.
Just create table photos
we're going to have an I.D. We'll also have our photo or image you or ever we want to call that will
have a user ID and then created that.
So ID is an integer or it's autoincrement primary key will be referencing photos that ID from other
tables because things like comments are associated with a particular photo or likes our and tags as
well.
So we need that primary key there image you or will just be a Vardar 255 book.
We should add not no.
We don't want that to be empty or to be all that's a big problem because there would be no photo to
display and then user ID that would be an integer.
And let's also make that not know a photo needs to be associated with the user.
We can't have an orphan photo that has no user and then created that just be another time stamp that
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 references the users table the ID field as the referer and there
you go.
OK.
And that will create our photos table.
So if I just restarts this whole thing now if I do show tables we have both tables in there if I do
or describe photos you'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.
Insert into photos and DiVall just to an image URL and a user ID.
Those are the two things we have to supply our self values and our first one let's say blue.
Submit it and we'll come back to you or else.
But Blue's idea is going to be one.
And if we weren't sure could do you know select star from users can see blue SP1.
Charlie Brown is too cold.
It's three.
So we'll just do something silly.
Let's just say that you are.
I was just random like that.
I'll duplicate that a couple of times in card it's command shift D or control shift on a PC and then.
But you know 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.
OK.
And I haven't submitted any photos that should work if we run our source again this time.
So I'd drop the database recreated it switched to it use it with the use command created or two tables
inserted all of this data.
Now if we can do select star from photos 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 figure out who posted these photos.
So again indefinitely if you feel good about this move on and don't waste time watching this.
But if you want the practice we could just you select.
Let's say I wanted to have the image you were all created at and the name of the username of the person
instead of a user ID.
So we'll just do a select start to start from photos join to a default inner join users on and we're
going to do it where photos don't use your ID equals users dot ID.
So if we start with that
there's a lot there some let's send it out to just be photos that image you are now com users.
User name savageness username.
Yes just like that
copy.
OK.
Now we can see that 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 you know Joines
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.