1 00:00:00,180 --> 00:00:00,600 All right. 2 00:00:00,600 --> 00:00:05,990 So we're moving on now to comment and we did users first and photos for a reason. 3 00:00:06,000 --> 00:00:13,260 One is that they're simpler and essential to Instagram, but also comments is going to rely on them 4 00:00:13,590 --> 00:00:20,160 because if you think about how comments work, you know, if we go way back here, these comments. 5 00:00:21,500 --> 00:00:22,220 Over here. 6 00:00:22,280 --> 00:00:27,500 This handy green box that draw your attention are written by somebody, right? 7 00:00:27,500 --> 00:00:34,010 So it's not just a comment like Awesome or Bang or but there's also somebody who wrote the comment. 8 00:00:34,010 --> 00:00:35,960 So there's some association there. 9 00:00:35,960 --> 00:00:44,090 So a comment needs to be related to a user, but then these comments are related to a particular photo. 10 00:00:44,120 --> 00:00:47,870 These comments here are for this photo, not this one. 11 00:00:48,200 --> 00:00:48,740 Right. 12 00:00:48,740 --> 00:00:53,060 So they're not just floating in thin air, but they're also related to a photo, which means we're actually 13 00:00:53,060 --> 00:00:55,310 going to have two foreign keys. 14 00:00:56,090 --> 00:00:57,350 So it looks like this. 15 00:00:58,250 --> 00:01:05,990 We have a comment table comments will have an ID primary key, our comment text, which is just the 16 00:01:05,990 --> 00:01:07,280 text of the comment. 17 00:01:07,640 --> 00:01:12,020 I didn't want to call it text because that's a reserved word in my SQL. 18 00:01:12,350 --> 00:01:18,230 Even though it might work for you, it's just not a good idea to mess with that and reserved word. 19 00:01:18,230 --> 00:01:21,710 Meaning that text is a particular data type. 20 00:01:21,710 --> 00:01:24,650 It's something that's used in my SQL in the language. 21 00:01:24,650 --> 00:01:28,250 So naming a column in a table is not a great idea. 22 00:01:28,250 --> 00:01:30,590 It's like naming a column integer. 23 00:01:32,060 --> 00:01:40,850 Then we've got user ID, which is a foreign key related to the user's ID, so that's how we know who 24 00:01:40,850 --> 00:01:41,480 wrote it. 25 00:01:42,260 --> 00:01:48,440 Then we've got photo ID, which is also a foreign key, this time to photos ID, which is how we know 26 00:01:48,440 --> 00:01:50,210 which photo the comment is on. 27 00:01:50,210 --> 00:01:53,330 And then we've got our good old friend created it. 28 00:01:53,930 --> 00:01:58,970 So let's hop over to cloud nine, do it down here after photos. 29 00:01:58,970 --> 00:02:02,960 So we'll do a create table comments. 30 00:02:05,180 --> 00:02:07,010 And our main things. 31 00:02:07,160 --> 00:02:16,250 We've got ID, we've got comment, text, then we've got user ID, photo ID and created that. 32 00:02:17,060 --> 00:02:27,110 So we'll start with ID, we'll do integer auto increment primary key, pretty standard comment text 33 00:02:27,260 --> 00:02:34,160 will just be our char 255 as well as we want to make sure it's not null. 34 00:02:34,190 --> 00:02:35,630 We don't want anyone leaving. 35 00:02:35,630 --> 00:02:36,650 Empty comments. 36 00:02:37,540 --> 00:02:40,030 If you try that on Instagram, it just won't post it. 37 00:02:40,480 --> 00:02:46,120 Then we've got user ID, which is just an integer, but we also want that to be not null. 38 00:02:46,270 --> 00:02:50,770 We don't want to have a comment that's an orphan that's not related to a user. 39 00:02:52,360 --> 00:02:53,770 Same thing for photo ID. 40 00:02:53,800 --> 00:02:54,990 It's going to be an integer. 41 00:02:55,000 --> 00:02:58,210 And we also don't want it to be not null or we don't want it to be null. 42 00:02:58,510 --> 00:03:04,990 We want to make sure it's not null because then we'd have another type of an orphan, a comment without 43 00:03:04,990 --> 00:03:06,160 a parent photo. 44 00:03:06,190 --> 00:03:08,530 So we have these two relationships here. 45 00:03:08,560 --> 00:03:14,890 User and photo are related to comments and we don't want either one to be null and then create that 46 00:03:15,460 --> 00:03:17,770 good old timestamp default now. 47 00:03:19,440 --> 00:03:21,960 But we're missing our foreign key constraints. 48 00:03:21,960 --> 00:03:31,620 So foreign key and we'll start with user underscore ID references, users ID comma. 49 00:03:31,920 --> 00:03:34,230 Then we've got another foreign key. 50 00:03:34,230 --> 00:03:40,710 This time we're working with photo ID, which references oops. 51 00:03:41,280 --> 00:03:44,250 Photos ID. 52 00:03:45,540 --> 00:03:46,200 There we go. 53 00:03:48,050 --> 00:03:48,590 Okay. 54 00:03:48,590 --> 00:03:55,550 So just to make sure I don't have any typos here, add my semicolon and I'm just going to resource that 55 00:03:56,450 --> 00:03:57,210 if I can find it. 56 00:03:57,230 --> 00:03:57,920 Here we go. 57 00:03:58,790 --> 00:04:00,200 Looks good so far. 58 00:04:00,230 --> 00:04:07,880 If I do show tables, I've got comments and I can do describe comments and we're good. 59 00:04:08,120 --> 00:04:10,640 All right, so just like the last few videos, stop here. 60 00:04:10,640 --> 00:04:15,950 If you feel good with this, if you want to see me, insert some data and see how we relate things when 61 00:04:15,950 --> 00:04:17,329 we actually insert the data. 62 00:04:17,329 --> 00:04:21,079 I'm going to do that, but of course, move on if you feel comfortable. 63 00:04:21,110 --> 00:04:26,870 So if you're still here, what I'll do is insert a couple of comments between or related to these three 64 00:04:26,870 --> 00:04:29,240 users and these three photos. 65 00:04:29,810 --> 00:04:34,280 And this is where it gets kind of difficult to keep everything straight, keep all the IDs and everything 66 00:04:34,280 --> 00:04:35,260 straight in your head. 67 00:04:35,270 --> 00:04:39,950 So we'll do an insert into comments and we'll have comment text. 68 00:04:40,160 --> 00:04:47,660 Then we'll start with the user ID who's posting it, and then the photo ID values. 69 00:04:47,660 --> 00:04:54,620 So let's say our first comment is going to be blue commenting on. 70 00:04:56,040 --> 00:05:01,950 This photo here saying this is about her comment is just meow. 71 00:05:03,660 --> 00:05:04,800 So that's the comment text. 72 00:05:04,800 --> 00:05:07,050 And the user ID is who's saying it blue. 73 00:05:07,080 --> 00:05:08,760 The cat has an idea of one. 74 00:05:10,470 --> 00:05:13,350 And I just you know, you can tell because we inserted her first. 75 00:05:13,350 --> 00:05:16,740 But of course, if we went back to our select way back here. 76 00:05:19,120 --> 00:05:20,050 You can see. 77 00:05:20,050 --> 00:05:21,580 Well, no, you can't see there. 78 00:05:21,580 --> 00:05:28,420 You can see here her ID is one OC and then the photo ID will just say she's commenting on this one here 79 00:05:29,050 --> 00:05:33,820 with photo ID of two and I'll duplicate that. 80 00:05:33,820 --> 00:05:42,280 So let's say somebody else commented, let's say someone says amazing shot on the same photo, photo 81 00:05:42,280 --> 00:05:45,010 here, and let's say I commented on that. 82 00:05:45,010 --> 00:05:51,040 So that would be my user ID three and we'll do one more this time. 83 00:05:51,070 --> 00:05:54,040 Let's say Charlie Brown is commenting, 84 00:05:57,190 --> 00:06:07,120 I heart this and Charlie Brown is user ID two and let's say he's commenting on the first photo that 85 00:06:07,120 --> 00:06:09,910 was submitted by Blue with user ID one. 86 00:06:10,300 --> 00:06:18,430 So you can see how it gets a little bit crazy, but we can do that now and make sure I have my semicolon 87 00:06:18,430 --> 00:06:19,660 instead of a comma there. 88 00:06:20,410 --> 00:06:23,440 This time, if we source it, everything looks good, we can do it. 89 00:06:23,440 --> 00:06:25,630 Select star from comments. 90 00:06:26,950 --> 00:06:27,550 There we go. 91 00:06:27,550 --> 00:06:28,810 We've got three comments. 92 00:06:28,990 --> 00:06:31,990 We could put a user ID in and a photo ID. 93 00:06:32,020 --> 00:06:33,280 I won't do that now. 94 00:06:33,280 --> 00:06:36,340 We'll do it as an exercise later with some more complex data. 95 00:06:36,340 --> 00:06:38,980 But of course you can do that if you want a little bit of practice.