All right.
So we're moving on now to comments and we did.
Users first and photos for a reason.
One is that they're simpler and essentially Instagram but also comments is going to rely on them because
if you think about how comments work you know if we go way back here these comments over here this anti-green
box to draw your attention are written by somebody.
Right.
So it's not just a comment like awesome or banger but there's also somebody who wrote that comment.
So there's some association there so a comment needs to be related to a user.
But then these comments are related to a particular photo.
These comments here are for this photo not this one.
Right.
So they're not just floating in thin air but they're also related to a photo which means we're actually
going to have two foreign keys.
So looks like this.
We have a comments table comments will have an ID primary key or comment text which is just you know
the text of the comment.
I didn't want to call it text because that's reserved word in my ask.
Well even though it might work for you which is not a good idea to mess with that in reserve word meaning
that you know text is a particular data type it's something that's used in my.
Well in the language so naming a column in a table is not a great idea.
It's like naming a column you know integer.
OK then we've got user ID which is a foreign key related to the user's dot ID.
So that's how we know who wrote it.
Then we've got photo photo-ID which is also foreign key.
This time two photos that ID which is how we know which photo the comment is on.
And then we've got our good old friend created it.
So let's hop over to cloud 9.
Do it down here.
After photos so it will do a create table comments.
And our main things we've got ID we've got comment text and we've got user ID photo ID and created it.
So we'll start with ID we'll do integer autoincrement primary key.
Pretty standard comment text will just be our char 2:55 as well as we want to make sure it's not know
we don't want anyone leaving empty comments.
If you try them on Instagram and just won't post it then we've got user ID which is just an integer.
But we also want that to be not know we don't want to have a comment that's an orphan that's not related
to a user.
Same thing for photo-ID.
It's going to be an integer.
And we also don't want it to be not know or we don't want it to be No.
We want to make sure it's not all because then we'd have another type of an orphan comment without apparent
photo.
So we have these two relationships here user and photo are related to comments.
OK.
And we don't want either one to be no and then created that good old time stamped default now.
But we're missing our foreign key constraints so foreign key will start with user underscore ID reference's
users ID comma.
Then we've got another foreign key.
This time we're working with photo ID which referer and says photos Id.
There we go.
OK.
So just to make sure I don't have any typos here.
My semi colon and I'm just going to resource that I can find it.
Here we go.
Looks good so far.
I tried to show tables.
I've got comments and I can do describe comments and we're good.
All right.
So just like the last few videos.
Stop here if you feel good with this.
If you want to see me inserts and data and see how we relate things when we actually insert the data
I'm going to do that.
But of course move on if you feel comfortable.
So if you're still here what I'll do is insert a couple of comments between here related to you know
these three users and these these three photos and this is where it gets kind of difficult to keep everything
straight keep all the IDs and everything straight in your head.
So we'll do an insert into comments and we'll have comment text then we'll start with a user id who's
posting it.
And then the photo ID values.
So let's say our first comment is going to be blue.
Commenting on this photo here saying this is our comment it's just me.
OK.
So that's the comment text and the user ID is.
Who's saying it flew the cat has an idea of one.
And I just know you can tell because we inserted her first but of course if we went back to our select
way back here you can see now you can see there you can see here her IDs one.
OK.
And then the photo ID will say she's commenting on this one here a photo ID of two and I'll duplicate
that.
So let's say somebody else commented.
Say someone says Amazing shot on the same photo photo here and say I commented on that.
So that would be my user ID 3.
And we'll do one more this time.
Let's say Charlie Brown was commenting.
I HEART.
And Charlie Brown is easier I do too.
And let's say he's commenting on the first photo that was submitted by blue user id one so you can see
how it gets a little bit crazy.
But we can do that now and make sure I have my semi colon that of a comma there this time if we source
it everything looks good.
We can do is select star from comments.
There we go.
We've got three comments we could put a user ID in and a photo ID.
I want to do that now.
We'll do it as an exercise later with some more complex data.
But of course you can do that if you want a little bit of practice.