WEBVTT 00:00.240 --> 00:01.350 Welcome back. 00:01.350 --> 00:07.210 So we're moving on to capturing the relationships of friendships between users. 00:07.500 --> 00:13.260 So we're talking about followers or follow NGs or however we want to term this I'm just refer to it 00:13.260 --> 00:14.220 as follows. 00:14.220 --> 00:16.930 But it's not really a great term either. 00:17.180 --> 00:19.890 But basically how do we capture the data. 00:19.890 --> 00:25.030 When I follow another user and they may or may not follow him back. 00:25.050 --> 00:25.840 It doesn't matter. 00:25.860 --> 00:32.400 I'm still able to follow them and that's how many captures is one way relationship and the easiest way 00:32.400 --> 00:39.620 to do it is with a single table that will call followers and all that it is is to use your ideas. 00:39.690 --> 00:47.070 So both of these are primary excuse me both of them are foreign keys referring to a user id a different 00:47.070 --> 00:48.630 user ID that's important. 00:48.630 --> 00:51.630 We can't have a user follow him or herself. 00:51.630 --> 00:56.870 So there are different ideas and I called them follow her ID and follow the ID. 00:56.910 --> 00:59.570 You could do you know User 1 ID and user to ID. 00:59.580 --> 01:07.950 But the idea here is that you can tell who is following who the follower is the person following that 01:07.950 --> 01:09.010 follow we. 01:09.090 --> 01:10.850 Its a nightmare to try and discuss here. 01:10.850 --> 01:12.750 So let me give you an example. 01:13.020 --> 01:15.090 Here's our table of users. 01:15.130 --> 01:21.950 So we have three users Tommy blue cat and cold steel and they have an idea of 1 2 and 3 respectively. 01:22.310 --> 01:25.800 OK so here's our follows table. 01:26.070 --> 01:32.700 And the first thing that say that Colt me decides to follow blue. 01:33.010 --> 01:37.900 So that means you know follower ID is three follow were being me the follower. 01:37.990 --> 01:41.560 Who am I following to do the follow me. 01:41.830 --> 01:47.330 But she doesn't have to follow me back and she doesn't follow me back at least not yet. 01:47.500 --> 01:52.340 If you look next I'm following user id of one which is Tommy. 01:52.510 --> 01:53.610 Speaking of blue 01:58.210 --> 02:00.090 OK let's see where was I. 02:00.260 --> 02:04.140 So why with user id three follow Tommy. 02:04.520 --> 02:05.440 OK. 02:06.080 --> 02:06.920 And then. 02:07.010 --> 02:13.640 Now Lou your idea is to finally follows me back she decides I'm a good owner and she wants to follow 02:13.640 --> 02:14.870 me on instagram. 02:15.020 --> 02:18.050 But the key thing here is that it's a one way relationship. 02:18.050 --> 02:20.830 You know here we have I'm following Tommy. 02:20.840 --> 02:27.140 But Tommy does not follow me and this is all we need the structure to encode that information. 02:27.320 --> 02:32.360 A couple of points we should make that these are both foreign keys created that it's just good to know 02:33.290 --> 02:36.300 if you want to keep track of you know when people are being followed. 02:36.320 --> 02:40.180 Again not a central but something that Instagram definitely is tracking. 02:40.310 --> 02:42.420 You know friendship date or something. 02:42.830 --> 02:47.330 And then also we don't want to have duplicated followers. 02:47.570 --> 02:50.120 So I don't want to be able to follow blue again. 02:50.330 --> 02:52.170 So we need to enforce that. 02:52.190 --> 02:56.020 These are unique the combination of these two in an order. 02:56.210 --> 03:02.120 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 03:02.120 --> 03:03.340 three and two. 03:03.650 --> 03:03.850 OK. 03:03.860 --> 03:04.930 So let's get to it. 03:04.940 --> 03:11.010 We've got follow Id follow the ID and created it and we won't be needing a primary key ID. 03:11.030 --> 03:17.000 Right now Id integer that is a primary key because we won't be referencing these friendships or followers 03:17.030 --> 03:21.150 anyway so create table follows. 03:21.470 --> 03:24.940 If you have a better name Feel free to use that. 03:25.190 --> 03:32.630 And the first thing we have is our follower ID and our follow e id and then create it. 03:33.680 --> 03:34.200 OK. 03:34.410 --> 03:35.140 Gosh. 03:35.420 --> 03:36.000 OK. 03:36.500 --> 03:42.350 So let's start with creating that time stamp default now. 03:42.500 --> 03:44.280 Perfect. 03:44.540 --> 03:50.120 Then we've got follower ID and follow we are both integers. 03:50.120 --> 03:52.510 We don't want them to be all. 03:52.610 --> 03:56.170 Copy that on over our commas there. 03:56.510 --> 04:06.170 And now we also need to officially declare these as foreign keys without the foreign key follower ID 04:06.470 --> 04:17.110 reference's user user's ID and we can just duplicate that line and just change follow the ID. 04:17.120 --> 04:23.120 It also references user ID and then the last thing I mentioned is we want to enforce that you can only 04:23.120 --> 04:25.370 have one of a given relationship. 04:25.370 --> 04:32.450 So if user id one is following user id too that can not but that can only be in the database once but 04:32.540 --> 04:34.990 user ID to can follow the user id one anyway. 04:35.000 --> 04:40.910 So to do that its just a matter of primary key in the order that we actually put them in here won't 04:40.910 --> 04:41.920 really matter. 04:41.930 --> 04:43.850 I mean it makes a difference to my ass. 04:43.840 --> 04:47.610 Q Well but for us it won't matter. 04:47.620 --> 04:48.450 Follow. 04:48.710 --> 04:52.360 AG Okay so that creates the table for us. 04:52.520 --> 04:56.150 Let's just check that everything works looks good. 04:56.150 --> 05:01.480 Let's do a describe follows good. 05:01.500 --> 05:02.220 All right. 05:02.220 --> 05:07.760 So just like the last couple of videos were done if you want to stick around and I'll play around recent 05:07.770 --> 05:11.440 data and show you that this primary key constraint is working. 05:11.880 --> 05:12.520 Okay. 05:12.840 --> 05:22.800 So if you're still here let's try doing an insert into follows and we'll have our follow were Id first 05:24.180 --> 05:28.850 and then our follow we ID should start with just something jibberish. 05:28.890 --> 05:30.830 You're saying it enough times. 05:31.440 --> 05:35.760 So let's say that we started off we've got these three users right. 05:35.880 --> 05:42.930 So blue follows Charlie Brown that's going to be one comment too. 05:43.640 --> 05:45.350 And we'll also have blue follow me. 05:45.440 --> 05:47.250 That's one comment three. 05:47.570 --> 05:50.390 And then let's say I also follow Balue back. 05:50.390 --> 05:52.860 So that is three come in one. 05:53.900 --> 05:59.120 And then let's say that we have Charlie Brown follows blue. 05:59.150 --> 06:00.530 I would say Charlie Brown follows me. 06:00.530 --> 06:02.570 So that's to come three. 06:02.820 --> 06:03.460 OK. 06:05.460 --> 06:07.020 Let's try inserting them. 06:07.140 --> 06:07.940 Looks like it works. 06:07.940 --> 06:08.530 You can do you know. 06:08.540 --> 06:12.450 So like start from users excuse me from follows. 06:13.140 --> 06:16.760 Great follow ready follow the idea and create it out. 06:17.070 --> 06:25.100 And then the true test is if I try and reinsert a relationship like insert into follows. 06:25.740 --> 06:32.150 Getting sick of this follower Id follow we stuff are almost. 06:32.760 --> 06:36.890 So let's say try and insert this friendship one comment two. 06:37.320 --> 06:39.950 So that is blue following. 06:40.070 --> 06:44.250 Let's do one of three blue following me she loves me so much she wants to follow me again. 06:44.460 --> 06:46.840 If we try and do that it's a duplicate entry. 06:46.950 --> 06:51.900 But as we saw you can see here we already have three Kamo one. 06:51.900 --> 06:57.810 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 06:57.810 --> 06:59.330 Charlie Brown. 06:59.550 --> 07:00.980 It's a duplicate entry. 07:01.600 --> 07:07.300 But if I switch the order and do two come and one that's permitted because we don't have that in here 07:07.300 --> 07:07.820 yet. 07:07.960 --> 07:10.740 But now of course it's duplicate entry. 07:10.810 --> 07:12.010 So the order does matter right. 07:12.010 --> 07:13.240 Who's following who. 07:13.570 --> 07:14.050 OK. 07:14.450 --> 07:16.180 So that makes sense. 07:16.210 --> 07:20.000 We're moving on now to our final piece which is tag's hashtags.