Welcome back.
So we're moving on to capturing the relationships of friendships between users.
So we're talking about followers or follow NGs 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 how many captures is one way relationship and the easiest way
to do it is with a single table that will call followers and all that it is is to use your ideas.
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 ideas and I called them follow her ID and follow the ID.
You could do you know User 1 ID and user to ID.
But the idea here is that you can tell who is following who the follower is the person following that
follow we.
Its 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 cold steel and they have an idea of 1 2 and 3 respectively.
OK so here's our follows table.
And the first thing that say that Colt me decides to follow blue.
So that means you know follower ID is three follow were being me the follower.
Who am I following to do the follow me.
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
OK let's see where was I.
So why with user id three follow Tommy.
OK.
And then.
Now Lou your idea is to 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 you know when people are being followed.
Again not a central but something that Instagram definitely is tracking.
You know friendship date or something.
And then also we don't want to have duplicated followers.
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.
OK.
So let's get to it.
We've got follow Id follow the ID and created it and we won't be needing a primary key ID.
Right now Id integer that is a primary key because we won't be referencing these friendships or followers
anyway 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 e id and then create it.
OK.
Gosh.
OK.
So let's start with creating that time stamp default now.
Perfect.
Then we've got follower ID and follow we are both integers.
We don't want them to be all.
Copy that on over our commas there.
And now we also need to officially declare these as foreign keys without the foreign key follower ID
reference's user user's ID and we can just duplicate that line and just change follow the ID.
It also references user ID and then the last thing 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 too that can not but that can only be in the database once but
user ID to can follow the user id one anyway.
So to do that its just a matter of primary key in the order that we actually put them in here won't
really matter.
I mean it makes a difference to my ass.
Q Well but for us it won't matter.
Follow.
AG 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 were done if you want to stick around and I'll play around recent
data and show you that this primary key constraint is working.
Okay.
So if you're still here let's try doing an insert into follows and we'll have our follow were Id first
and then our follow we ID should start with just something jibberish.
You're saying it enough times.
So let's say that we started off we've got these three users right.
So blue follows Charlie Brown that's going to be one comment too.
And we'll also have blue follow me.
That's one comment three.
And then let's say I also follow Balue back.
So that is three come in one.
And then let's say that we have Charlie Brown follows blue.
I would say Charlie Brown follows me.
So that's to come three.
OK.
Let's try inserting them.
Looks like it works.
You can do you know.
So like start from users excuse me from follows.
Great follow ready follow the idea and create it out.
And then the true test is if I try and reinsert a relationship like insert into follows.
Getting sick of this follower Id follow we stuff are almost.
So let's say try and insert this friendship one comment two.
So that is blue following.
Let's do one of three 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 Kamo one.
So if I did do you know if I read you one common to this time which we also already have it's blue following
Charlie Brown.
It's a duplicate entry.
But if I switch the order and do two come and 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.
OK.
So that makes sense.
We're moving on now to our final piece which is tag's hashtags.