1 00:00:00,180 --> 00:00:00,600 All right. 2 00:00:00,600 --> 00:00:06,000 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,230 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,250 --> 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:40,190 So a comment needs to be related to a user. 10 00:00:40,190 --> 00:00:44,120 But then these comments are related to a particular photo. 11 00:00:44,120 --> 00:00:47,870 These comments here are for this photo, not this one. 12 00:00:48,200 --> 00:00:48,740 Right. 13 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 14 00:00:53,060 --> 00:00:55,310 going to have two foreign keys. 15 00:00:56,090 --> 00:00:57,350 So it looks like this. 16 00:00:58,220 --> 00:01:02,840 We have a comment table comments will have an ID primary key. 17 00:01:02,840 --> 00:01:09,950 Our comment text, which is just the text of the comment, I didn't want to call it text because that's 18 00:01:09,950 --> 00:01:14,300 a reserved word in my SQL, even though it might work for you. 19 00:01:14,300 --> 00:01:21,380 It's just not a good idea to mess with that and reserved word meaning that text is a particular data 20 00:01:21,380 --> 00:01:21,710 type. 21 00:01:21,710 --> 00:01:24,650 It's something that's used in my SQL in the language. 22 00:01:24,650 --> 00:01:28,250 So naming a column in a table is not a great idea. 23 00:01:28,250 --> 00:01:30,590 It's like naming a column integer. 24 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 25 00:01:40,850 --> 00:01:41,480 wrote it. 26 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 27 00:01:48,440 --> 00:01:50,210 which photo the comment is on. 28 00:01:50,210 --> 00:01:53,330 And then we've got our good old friend created it. 29 00:01:53,900 --> 00:01:55,640 So let's hop over to cloud nine. 30 00:01:56,570 --> 00:02:02,960 I'll do it down here after photos, so we'll do a create table comments. 31 00:02:05,180 --> 00:02:07,010 And our main things. 32 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. 33 00:02:17,060 --> 00:02:23,990 So we'll start with ID, we'll do integer auto increment primary key. 34 00:02:24,650 --> 00:02:34,180 Pretty standard comment text will just be our char 255 as well as we want to make sure it's not null. 35 00:02:34,190 --> 00:02:36,650 We don't want anyone leaving empty comments. 36 00:02:37,570 --> 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:03,700 We want to make sure it's not null because then we'd have another type of an orphan. 43 00:03:03,850 --> 00:03:06,160 A comment without 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,890 --> 00:03:34,230 Then we've got another foreign key. 50 00:03:34,230 --> 00:03:44,250 This time we're working with photo ID which references oops photos ID. 51 00:03:45,540 --> 00:03:46,230 There we go. 52 00:03:48,050 --> 00:03:54,950 Okay, So just to make sure I don't have any typos here, add my semicolon and I'm just going to resource 53 00:03:54,950 --> 00:03:57,210 that if I can find it. 54 00:03:57,230 --> 00:03:57,920 Here we go. 55 00:03:58,790 --> 00:04:00,200 Looks good so far. 56 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. 57 00:04:08,120 --> 00:04:08,510 All right. 58 00:04:08,510 --> 00:04:10,640 So just like the last few videos, stop here. 59 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 60 00:04:15,950 --> 00:04:18,350 we actually insert the data, I'm going to do that. 61 00:04:18,350 --> 00:04:21,079 But of course, move on if you feel comfortable. 62 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 63 00:04:26,870 --> 00:04:29,210 users and these three photos. 64 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 65 00:04:34,280 --> 00:04:35,260 straight in your head. 66 00:04:35,270 --> 00:04:39,950 So we'll do an insert into comments and we'll have comment text. 67 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. 68 00:04:47,660 --> 00:04:54,650 So let's say our first comment is going to be Blue commenting on. 69 00:04:56,040 --> 00:05:01,950 This photo here saying this is about her comment is just meow. 70 00:05:03,660 --> 00:05:07,050 So that's the comment text and the user ID is who's saying it blue. 71 00:05:07,080 --> 00:05:08,760 The cat has an idea of one. 72 00:05:10,470 --> 00:05:13,350 And I just you know, you can tell because we inserted her first. 73 00:05:13,350 --> 00:05:16,740 But of course, if we went back to our select way back here. 74 00:05:19,120 --> 00:05:20,050 You can see. 75 00:05:20,050 --> 00:05:21,580 Well, no, you can't see there. 76 00:05:21,580 --> 00:05:27,820 You can see here her ID is one OC, and then the photo ID will just say she's commenting on this one 77 00:05:27,820 --> 00:05:33,820 here with photo ID of two and I'll duplicate that. 78 00:05:33,820 --> 00:05:42,910 So let's say somebody else commented, let's say someone says amazing shot on the same photo photo here 79 00:05:43,300 --> 00:05:45,010 and let's say I commented on that. 80 00:05:45,010 --> 00:05:47,020 So that would be my user ID three. 81 00:05:48,600 --> 00:05:51,040 And we'll do one more this time. 82 00:05:51,060 --> 00:05:54,060 Let's say Charlie Brown is commenting. 83 00:05:57,180 --> 00:06:04,050 I heart this and Charlie Brown is user ID, too. 84 00:06:04,140 --> 00:06:09,930 And let's say he's commenting on the first photo that was submitted by Blue with user ID one. 85 00:06:10,290 --> 00:06:18,420 So you can see how it gets a little bit crazy, but we can do that now and make sure I have my semicolon 86 00:06:18,420 --> 00:06:19,680 instead of a comma there. 87 00:06:20,400 --> 00:06:23,070 This time, if we source it, everything looks good. 88 00:06:23,070 --> 00:06:25,650 We can do a select star from comments. 89 00:06:26,940 --> 00:06:27,530 There we go. 90 00:06:27,540 --> 00:06:28,800 We've got three comments. 91 00:06:28,980 --> 00:06:31,980 We could put a user ID and a photo ID. 92 00:06:32,010 --> 00:06:33,270 I won't do that now. 93 00:06:33,270 --> 00:06:36,320 We'll do it as an exercise later with some more complex data. 94 00:06:36,330 --> 00:06:39,000 But of course you can do that if you want a little bit of practice.