All right.
So we're moving on now to comment and we did users first and photos for a reason.
One is that they're simpler and essential to 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 handy green box that draw your attention are written by somebody, right?
So it's not just a comment like Awesome or Bang or but there's also somebody who wrote the 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 it looks like this.
We have a comment table comments will have an ID primary key, our comment text, which is just the
text of the comment.
I didn't want to call it text because that's a reserved word in my SQL.
Even though it might work for you, it's just not a good idea to mess with that and reserved word.
Meaning that text is a particular data type.
It's something that's used in my SQL in the language.
So naming a column in a table is not a great idea.
It's like naming a column integer.
Then we've got user ID, which is a foreign key related to the user's ID, so that's how we know who
wrote it.
Then we've got photo ID, which is also a foreign key, this time to photos 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 nine, do it down here after photos.
So we'll do a create table comments.
And our main things.
We've got ID, we've got comment, text, then we've got user ID, photo ID and created that.
So we'll start with ID, we'll do integer auto increment primary key, pretty standard comment text
will just be our char 255 as well as we want to make sure it's not null.
We don't want anyone leaving.
Empty comments.
If you try that on Instagram, it just won't post it.
Then we've got user ID, which is just an integer, but we also want that to be not null.
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 null or we don't want it to be null.
We want to make sure it's not null because then we'd have another type of an orphan, a comment without
a parent photo.
So we have these two relationships here.
User and photo are related to comments and we don't want either one to be null and then create that
good old timestamp default now.
But we're missing our foreign key constraints.
So foreign key and we'll start with user underscore ID references, users ID comma.
Then we've got another foreign key.
This time we're working with photo ID, which references oops.
Photos ID.
There we go.
Okay.
So just to make sure I don't have any typos here, add my semicolon and I'm just going to resource that
if I can find it.
Here we go.
Looks good so far.
If I do 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, insert some 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 or related to these three
users and 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 the 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 about her comment is just meow.
So that's the comment text.
And the user ID is who's saying it blue.
The cat has an idea of one.
And I just you 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.
Well, no, you can't see there.
You can see here her ID is one OC and then the photo ID will just say she's commenting on this one here
with photo ID of two and I'll duplicate that.
So let's say somebody else commented, let's say someone says amazing shot on the same photo, photo
here, and let's say I commented on that.
So that would be my user ID three and we'll do one more this time.
Let's say Charlie Brown is commenting,
I heart this and Charlie Brown is user ID two and let's say he's commenting on the first photo that
was submitted by Blue with 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 semicolon
instead of a comma there.
This time, if we source it, everything looks good, we can do it.
Select star from comments.
There we go.
We've got three comments.
We could put a user ID in and a photo ID.
I won't 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.