Welcome back.
So we're moving on to capturing the relationships of friendships between users.
So we're talking about followers or follow ings or however we want to term this.
I'm just refer to it as follows.
But it's not really a great term either.
But basically, how do we capture the data when I follow another user?
And they may or may not follow him back.
It doesn't matter.
I'm still able to follow them.
And that's all we need to capture is this one way relationship.
And the easiest way to do it is with a single table that will call follows.
And all that it is is two user IDs.
So both of these are primary excuse me, both of them are foreign keys referring to a user ID, a different
user ID.
That's important.
We can't have a user follow him or herself.
So there are different IDs and I called them follower ID and follow ID.
You could do user one ID and user to ID, but the idea here is that you can tell who is following who,
that the follower is the person following the follow we.
It's a nightmare to try and discuss here.
So let me give you an example.
Here's our table of users.
So we have three users, Tommy Blue Cat and Colt Steel, and they have an ID of one, two and three
respectively.
So here's our follows table.
And the first thing let's say that Colt Me decides to follow.
Blue.
So that means follower ID is three follower being me the follower.
Who am I following to?
Blue the follower.
But she doesn't have to follow me back.
And she doesn't follow me back.
At least not yet.
If you look next, I'm following user ID of one, which is Tommy.
Speaking of blue.
Yes.
Okay.
Let's see.
Where was I?
So I with user ID three.
Follow Tommy.
And then you can see now blue is is ready is two.
Finally follows me back.
She decides I'm a good owner and she wants to follow me on Instagram.
But the key thing here is that it's a one way relationship.
You know, here we have I'm following Tommy, but Tommy does not follow me.
And this is all we need the structure to encode that information.
A couple of points we should make that these are both foreign keys created, that it's just good to
know if you want to keep track of when people are being followed.
Again, not essential, but it's something that Instagram definitely is tracking, you know, friendship,
date or something.
And then also we don't want to have duplicated follows, so I don't want to be able to follow Blue again.
So we need to enforce that.
These are unique, the combination of these two in an order.
So as you can see, you know, three and two is here and we can have two and three, but we can't have
another three and two.
So let's get to it.
We've got follow ID, follow ID and created it and we won't be needing a primary key ID or an ID integer.
That is a primary key because we won't be referencing these friendships or follows anywhere.
So create table follows.
If you have a better name, feel free to use that.
And the first thing we have is our follower ID and our follow ID and then created that.
Oh my gosh.
Okay, so let's start with created at timestamp default now.
Perfect.
Then we've got follower ID and follow id are both integers.
We don't want them to be null.
Just copy that on over.
Put our commas there.
And now we also need to officially declare these as foreign keys.
So that would be foreign key follower ID references, user users ID and we can just duplicate that line
and just change.
Follow E id.
It also references users ID.
And then the last thing as I mentioned is we want to enforce that.
You can only have one of a given relationship.
So if user ID one is following user ID to that can not that can only be in the database once, but user
ID two can follow a user ID one anyway.
So to do that, it's just a matter of primary key.
And the order that we actually put them in here won't really matter.
I mean, it makes a difference to my SQL, but for us it won't matter.
Follow ID.
Okay.
So that creates the table for us.
Let's just check that everything works.
Looks good.
Let's do a describe follows.
Good.
All right, so just like the last couple of videos, we're done.
If you want to stick around and I'll play around with some data and show you that this primary key constraint
is working.
So if you're still here, let's try doing an insert into follows and we'll have our follower ID first
and then our follow.
We ID gosh, it starts to just sound like gibberish after saying it enough times.
So let's say that we start off, we've got these three users, right?
So blue.
Follows Charlie Brown.
So that's going to be one comment, too.
And we'll also have to follow me.
That's one comma three.
And then let's say I also follow blue back.
So that is three comma one.
And then let's say that who do we have?
Charlie Brown follows blue.
Let's say Charlie Brown follows me.
So that's two comma, three OC.
Let's try inserting them.
Looks like it works.
You can do so.
Start from users.
Excuse me.
From follows.
Great.
We got follow ID, follow the ID and created that.
And then the true test is if I try and insert a relationship like insert into follows getting sick of
this follower id follow we stuff we're almost done.
So let's say try and insert this friendship one comma two so that is blue following or let's do one
cover three it's blue following me She loves me so much She wants to follow me again.
If we try and do that, it's a duplicate entry.
But as we saw, you can see here, we already have three comma one.
So if I did do if I do one common to this time, which we also already have that's blue following Charlie
Brown.
It's a duplicate entry.
But if I switch the order and do two comma one, that's permitted because we don't have that in here
yet.
But now, of course, it's duplicate entry.
So the order does matter, right?
Who's following who?
Okay.
So hopefully that makes sense.
We're moving on now to our final piece, which is tags, hash tags.