1 00:00:00,240 --> 00:00:01,050 Welcome back. 2 00:00:01,170 --> 00:00:07,020 So we're moving on to capturing the relationships of friendships between users. 3 00:00:07,350 --> 00:00:12,120 So we're talking about followers or follow ings or however we want to term this. 4 00:00:12,120 --> 00:00:14,220 I'm just refer to it as follows. 5 00:00:14,220 --> 00:00:16,950 But it's not really a great term either. 6 00:00:17,100 --> 00:00:22,410 But basically, how do we capture the data when I follow another user? 7 00:00:22,920 --> 00:00:25,010 And they may or may not follow him back. 8 00:00:25,020 --> 00:00:25,860 It doesn't matter. 9 00:00:25,860 --> 00:00:27,390 I'm still able to follow them. 10 00:00:27,750 --> 00:00:31,410 And that's all we need to capture is this one way relationship. 11 00:00:31,410 --> 00:00:36,390 And the easiest way to do it is with a single table that will call follows. 12 00:00:36,960 --> 00:00:39,630 And all that it is is to use your IDs. 13 00:00:39,630 --> 00:00:47,100 So both of these are primary excuse me, both of them are foreign keys referring to a user ID, a different 14 00:00:47,100 --> 00:00:48,480 user ID that's important. 15 00:00:48,480 --> 00:00:51,240 We can't have a user follow him or herself. 16 00:00:51,510 --> 00:00:56,850 So there are different IDs and I called them follower ID and follow ID. 17 00:00:56,880 --> 00:01:03,840 You could do user one ID and user to ID, but the idea here is that you can tell who is following who, 18 00:01:03,840 --> 00:01:08,400 that the follower is the person following the follow. 19 00:01:08,400 --> 00:01:10,860 We It's a nightmare to try and discuss here. 20 00:01:10,860 --> 00:01:12,690 So let me give you an example. 21 00:01:12,870 --> 00:01:15,090 Here's our table of users. 22 00:01:15,090 --> 00:01:20,880 So we have three users, Tommy Blue Cat and Colt Steel, and they have an ID of one, two and three 23 00:01:20,880 --> 00:01:21,870 respectively. 24 00:01:23,340 --> 00:01:25,740 So here's our follows table. 25 00:01:25,860 --> 00:01:31,320 And the first thing let's say that Colt Me decides to follow. 26 00:01:32,050 --> 00:01:32,680 Blue. 27 00:01:32,860 --> 00:01:37,780 So that means follower ID is three follower being me the follower. 28 00:01:37,900 --> 00:01:41,260 Who am I following to Blue The follower. 29 00:01:41,710 --> 00:01:46,060 But she doesn't have to follow me back and she doesn't follow me back. 30 00:01:46,060 --> 00:01:46,990 At least not yet. 31 00:01:47,380 --> 00:01:52,390 If you look next, I'm following user ID of one, which is Tommy. 32 00:01:52,400 --> 00:01:53,950 Speaking of blue. 33 00:01:55,630 --> 00:01:56,350 Yes. 34 00:01:58,190 --> 00:01:59,410 Okay, let's see. 35 00:01:59,420 --> 00:02:00,170 Where was I? 36 00:02:00,230 --> 00:02:02,360 So I with user ID three. 37 00:02:02,510 --> 00:02:09,770 Follow Tommy and then you can see now blue is user ID is two. 38 00:02:09,770 --> 00:02:11,260 Finally follows me back. 39 00:02:11,270 --> 00:02:14,510 She decides I'm a good owner and she wants to follow me on Instagram. 40 00:02:14,900 --> 00:02:17,960 But the key thing here is that it's a one way relationship. 41 00:02:17,960 --> 00:02:22,370 You know, here we have I'm following Tommy, but Tommy does not follow me. 42 00:02:22,370 --> 00:02:26,390 And this is all we need the structure to encode that information. 43 00:02:27,320 --> 00:02:32,000 A couple of points we should make that these are both foreign keys created, that it's just good to 44 00:02:32,000 --> 00:02:36,170 know if you want to keep track of when people are being followed. 45 00:02:36,290 --> 00:02:41,300 Again, not essential, but it's something that Instagram definitely is tracking, friendship, date 46 00:02:41,300 --> 00:02:42,050 or something. 47 00:02:42,770 --> 00:02:47,420 And then also we don't want to have duplicated follows. 48 00:02:47,420 --> 00:02:50,210 So I don't want to be able to follow Blue again. 49 00:02:50,330 --> 00:02:52,130 So we need to enforce that. 50 00:02:52,130 --> 00:02:56,060 These are unique, the combination of these two in an order. 51 00:02:56,060 --> 00:03:01,790 So as you can see, you know, three and two is here and we can have two and three, but we can't have 52 00:03:01,790 --> 00:03:03,290 another three and two. 53 00:03:03,890 --> 00:03:04,940 So let's get to it. 54 00:03:04,940 --> 00:03:12,590 We've got follow ID, follow ID and created it and we won't be needing a primary key ID or an ID integer. 55 00:03:12,590 --> 00:03:17,720 That is a primary key because we won't be referencing these friendships or follows anywhere. 56 00:03:17,960 --> 00:03:21,050 So create table follows. 57 00:03:21,440 --> 00:03:24,530 If you have a better name, feel free to use that. 58 00:03:25,130 --> 00:03:32,000 And the first thing we have is our follower ID and our follow E ID and then created that. 59 00:03:34,100 --> 00:03:35,180 Oh my gosh. 60 00:03:35,390 --> 00:03:42,500 Okay, so let's start with created at timestamp default now. 61 00:03:42,500 --> 00:03:43,250 Perfect. 62 00:03:44,450 --> 00:03:49,520 Then we've got follower ID and follow ID are both integers. 63 00:03:50,060 --> 00:03:55,520 We don't want them to be null, just copy that on over, put our commas there. 64 00:03:56,360 --> 00:04:00,920 And now we also need to officially declare these as foreign keys. 65 00:04:01,310 --> 00:04:14,120 So that would be foreign key follower ID references, user users ID, and we can just duplicate that 66 00:04:14,120 --> 00:04:16,910 line and just change follow ID. 67 00:04:16,940 --> 00:04:19,310 It also references users ID. 68 00:04:19,579 --> 00:04:22,550 And then the last thing as I mentioned is we want to enforce that. 69 00:04:22,550 --> 00:04:25,400 You can only have one of a given relationship. 70 00:04:25,400 --> 00:04:32,780 So if user ID one is following user ID to that can not that can only be in the database once, but user 71 00:04:32,780 --> 00:04:35,060 ID two can follow a user ID one anyway. 72 00:04:35,060 --> 00:04:40,940 So to do that, it's just a matter of primary key and the order that we actually put them in here won't 73 00:04:40,940 --> 00:04:41,900 really matter. 74 00:04:41,900 --> 00:04:46,550 I mean, it makes a difference to my SQL, but for us it won't matter. 75 00:04:47,570 --> 00:04:48,680 Follow ID. 76 00:04:49,940 --> 00:04:52,410 Okay, So that creates the table for us. 77 00:04:52,440 --> 00:04:54,270 Let's just check that everything works. 78 00:04:55,050 --> 00:04:56,010 Looks good. 79 00:04:56,040 --> 00:04:59,040 Let's do a describe follows. 80 00:05:00,620 --> 00:05:01,220 Good. 81 00:05:01,460 --> 00:05:02,090 All right. 82 00:05:02,090 --> 00:05:04,970 So just like the last couple of videos, we're done. 83 00:05:05,330 --> 00:05:10,460 If you want to stick around and I'll play around with some data and show you that this primary key constraint 84 00:05:10,460 --> 00:05:11,240 is working. 85 00:05:12,740 --> 00:05:23,090 So if you're still here, let's try doing an insert into follows and we'll have our follower ID first 86 00:05:24,050 --> 00:05:26,630 and then our follow We ID. 87 00:05:27,020 --> 00:05:30,590 Gosh, it starts to just sound like gibberish after saying it enough times. 88 00:05:31,580 --> 00:05:35,750 So let's say that we start off, we've got these three users, right? 89 00:05:35,750 --> 00:05:36,980 So blue. 90 00:05:37,800 --> 00:05:39,720 Follows Charlie Brown. 91 00:05:41,110 --> 00:05:42,730 So that's going to be one comment, too. 92 00:05:43,480 --> 00:05:45,400 And we'll also have blue follow me. 93 00:05:45,400 --> 00:05:46,690 That's one comma three. 94 00:05:47,440 --> 00:05:50,320 And then let's say I also follow blue back. 95 00:05:50,320 --> 00:05:52,570 So that is three comma one. 96 00:05:53,770 --> 00:05:56,440 And then let's say that who do we have? 97 00:05:56,440 --> 00:05:58,870 Charlie Brown follows blue. 98 00:05:59,300 --> 00:06:00,550 Let's say Charlie Brown follows me. 99 00:06:00,550 --> 00:06:03,550 So that's two comma three OC. 100 00:06:05,460 --> 00:06:06,630 Let's try inserting them. 101 00:06:07,120 --> 00:06:07,980 Looks like it works. 102 00:06:07,980 --> 00:06:08,340 You can do. 103 00:06:08,600 --> 00:06:10,260 Select start from users. 104 00:06:10,260 --> 00:06:10,830 Excuse me. 105 00:06:10,830 --> 00:06:11,910 From follows. 106 00:06:13,080 --> 00:06:13,770 Great. 107 00:06:14,250 --> 00:06:16,770 We got follow ID, follow the ID and created that. 108 00:06:16,890 --> 00:06:26,460 And then the true test is if I try and re insert a relationship like insert into follows getting sick 109 00:06:26,460 --> 00:06:29,670 of this follower id follow e stuff. 110 00:06:29,790 --> 00:06:30,930 We're almost done. 111 00:06:32,940 --> 00:06:36,870 So let's say try and insert this friendship one comma two. 112 00:06:37,200 --> 00:06:39,960 So that is blue following. 113 00:06:39,960 --> 00:06:41,100 Or let's do one cover three. 114 00:06:41,100 --> 00:06:42,030 It's blue following me. 115 00:06:42,030 --> 00:06:44,070 She loves me so much she wants to follow me again. 116 00:06:44,550 --> 00:06:46,620 If we try and do that, it's a duplicate entry. 117 00:06:46,740 --> 00:06:51,720 But as we saw, you can see here, we already have three comma one. 118 00:06:51,720 --> 00:06:57,810 So if I did do if I do one common to this time, which we also already have, that's blue following 119 00:06:57,840 --> 00:06:58,650 Charlie Brown. 120 00:06:59,560 --> 00:07:00,790 It's a duplicate entry. 121 00:07:01,510 --> 00:07:07,360 But if I switch the order and do two comma one, that's permitted because we don't have that in here 122 00:07:07,360 --> 00:07:07,810 yet. 123 00:07:07,810 --> 00:07:10,660 But now of course, it's duplicate entry. 124 00:07:10,660 --> 00:07:12,010 So the order does matter, right? 125 00:07:12,040 --> 00:07:13,150 Who's following who? 126 00:07:13,700 --> 00:07:16,160 Okay, so hopefully that makes sense. 127 00:07:16,180 --> 00:07:20,050 We're moving on now to our final piece, which is tags, hash tags.