All right.
So this will be a quick video where we just implement the two tables we need.
They're both very simple.
So tags is just an ID and a tag name.
Photo tags is photo ID and a tag ID.
Okay.
So here I am in cloud nine and we'll start with our tags, table create table tags.
And all we need is an ID, which is an integer auto increment primary key.
And then we'll also have our tag name, which is just a var char.
255 And we want to make it unique.
We don't want to have oh, my gosh, if I can spell unique, we don't want to have any duplicates in
there.
And then after that, it would be nice to add a creative that, like I said, so we could store the
first time a tag was used or created.
And that's it.
So that's our tags table, this table here.
Now we've got to worry about photo tags.
So we'll do our create table photo tags or tagging, which is also nice, I guess.
Well, neither is nice, but also works.
We don't need an ID, all that we need is a photo ID and then the tag ID and those are both going to
be integer.
Not no.
There we go.
And then on top of that, we need to add our foreign key constraints.
Foreign key.
Photo ID is referencing the photos.
Table ID field duplicate that tag ID is referencing.
Tags Table ID field.
We also want to make sure that a user can't tag the same photo with the same hash tag multiple times,
and that's an easy fix.
We just do our same primary key with two items photo ID and tag ID, and that just ensures that we can't
have multiple instances of the same photo tags.
All right, don't forget our semicolon.
It looks like I'm missing this comma here.
There we go.
Now, if we try that.
And we look at our tables.
There we go.
We've got comments.
Follow those likes.
Photo tags, photos, tags, end users.
And that's it for our schema.
We're done.
So in the next section, if you'd like to tap out now, move on to the next section.
I'm going to give you a massive file with a bunch of data that I created.
Took way too long to do that.
We'll be able to do some exercises with and get some practice working with a bunch of tables.
But if you do want to see me, just insert a couple of tags and photo tags.
I'm going to do that now.
So if you're still here, I'm going to do that.
I'll start with inserting a couple of tags and all we have is tag name.
And just to verify that, if we go back up to our tags, it's just a tag name that we're inserting.
So let's say I'm inserting the string.
Adorable.
Okay.
And we get two more, let's say cute.
And one more would be, I don't know, sunrise.
So that will insert our tags.
Can make sure that that works.
Select star from tags.
There we go.
We've got our three tags now.
We associate them with photos.
And so we've got three photos.
I'm not.
It doesn't matter what they are, but they have an idea of one, two and three.
So if we want to do an insert into if I spell insert correctly photo tags, we'll start by taking the
photo ID and then the tag ID
values.
And let's say the first photo is tagged with adorable and the first photo is also tagged with cute because
that has an idea of two.
And then we'll say the second photo is tagged with, I don't know, sunrise.
And then the third photo is also tagged with cute like that.
So we can rerun this.
Let's do select star from photo tags.
There we go.
Looks good.
And now the true test is, can we still insert something that shouldn't work like a duplicate?
We already have one comma one.
So insert into photo tags, photo ID, comma, tag ID values one, comma, one, and we get duplicate
entry.
So that's good.
Works perfectly, but as you can see, the other order works fine too.
Come a three or three comma.
Two are not the same thing at all.
This is saying the second tag or this is saying the second photo should have the third tag.
That's this is saying the third photo should have the second tag.
Quite a mouthful.
All right.
So we're done here.
Next up, we're actually going to play around with data.
Like I said, I'm going to give you a giant file.
Hopefully it's fun.
And if it's not, well, we're basically at the end of the course.
So just hang in there.