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