WEBVTT 00:00.180 --> 00:00.600 All right. 00:00.630 --> 00:03.550 So we're moving on now to comments and we did. 00:03.570 --> 00:06.170 Users first and photos for a reason. 00:06.180 --> 00:14.250 One is that they're simpler and essentially Instagram but also comments is going to rely on them because 00:14.250 --> 00:23.110 if you think about how comments work you know if we go way back here these comments over here this anti-green 00:23.110 --> 00:27.230 box to draw your attention are written by somebody. 00:27.250 --> 00:27.490 Right. 00:27.490 --> 00:34.160 So it's not just a comment like awesome or banger but there's also somebody who wrote that comment. 00:34.210 --> 00:40.220 So there's some association there so a comment needs to be related to a user. 00:40.330 --> 00:44.200 But then these comments are related to a particular photo. 00:44.290 --> 00:47.950 These comments here are for this photo not this one. 00:48.280 --> 00:48.790 Right. 00:48.820 --> 00:53.020 So they're not just floating in thin air but they're also related to a photo which means we're actually 00:53.020 --> 00:55.630 going to have two foreign keys. 00:56.200 --> 00:57.970 So looks like this. 00:58.330 --> 01:05.510 We have a comments table comments will have an ID primary key or comment text which is just you know 01:05.530 --> 01:07.520 the text of the comment. 01:07.750 --> 01:11.570 I didn't want to call it text because that's reserved word in my ask. 01:11.580 --> 01:18.520 Well even though it might work for you which is not a good idea to mess with that in reserve word meaning 01:18.520 --> 01:23.480 that you know text is a particular data type it's something that's used in my. 01:23.550 --> 01:28.210 Well in the language so naming a column in a table is not a great idea. 01:28.240 --> 01:30.910 It's like naming a column you know integer. 01:31.350 --> 01:39.910 OK then we've got user ID which is a foreign key related to the user's dot ID. 01:39.910 --> 01:42.020 So that's how we know who wrote it. 01:42.360 --> 01:45.240 Then we've got photo photo-ID which is also foreign key. 01:45.240 --> 01:50.270 This time two photos that ID which is how we know which photo the comment is on. 01:50.410 --> 01:53.700 And then we've got our good old friend created it. 01:54.040 --> 01:56.120 So let's hop over to cloud 9. 01:56.770 --> 01:57.810 Do it down here. 01:57.970 --> 02:03.850 After photos so it will do a create table comments. 02:05.230 --> 02:17.110 And our main things we've got ID we've got comment text and we've got user ID photo ID and created it. 02:17.200 --> 02:24.670 So we'll start with ID we'll do integer autoincrement primary key. 02:24.700 --> 02:34.120 Pretty standard comment text will just be our char 2:55 as well as we want to make sure it's not know 02:34.240 --> 02:37.430 we don't want anyone leaving empty comments. 02:37.600 --> 02:43.820 If you try them on Instagram and just won't post it then we've got user ID which is just an integer. 02:44.020 --> 02:49.480 But we also want that to be not know we don't want to have a comment that's an orphan that's not related 02:49.480 --> 02:52.410 to a user. 02:52.420 --> 02:53.780 Same thing for photo-ID. 02:53.790 --> 02:54.990 It's going to be an integer. 02:55.040 --> 02:58.310 And we also don't want it to be not know or we don't want it to be No. 02:58.600 --> 03:05.490 We want to make sure it's not all because then we'd have another type of an orphan comment without apparent 03:05.580 --> 03:06.210 photo. 03:06.310 --> 03:11.230 So we have these two relationships here user and photo are related to comments. 03:11.320 --> 03:11.880 OK. 03:12.160 --> 03:18.350 And we don't want either one to be no and then created that good old time stamped default now. 03:19.540 --> 03:27.310 But we're missing our foreign key constraints so foreign key will start with user underscore ID reference's 03:27.760 --> 03:31.630 users ID comma. 03:32.020 --> 03:34.210 Then we've got another foreign key. 03:34.270 --> 03:45.500 This time we're working with photo ID which referer and says photos Id. 03:45.560 --> 03:46.110 There we go. 03:47.930 --> 03:48.520 OK. 03:48.650 --> 03:51.840 So just to make sure I don't have any typos here. 03:52.090 --> 03:57.230 My semi colon and I'm just going to resource that I can find it. 03:57.230 --> 03:58.790 Here we go. 03:58.790 --> 04:00.400 Looks good so far. 04:00.440 --> 04:02.810 I tried to show tables. 04:02.810 --> 04:08.200 I've got comments and I can do describe comments and we're good. 04:08.210 --> 04:08.510 All right. 04:08.510 --> 04:10.080 So just like the last few videos. 04:10.100 --> 04:11.820 Stop here if you feel good with this. 04:11.840 --> 04:17.150 If you want to see me inserts and data and see how we relate things when we actually insert the data 04:17.470 --> 04:18.370 I'm going to do that. 04:18.470 --> 04:21.250 But of course move on if you feel comfortable. 04:21.250 --> 04:26.390 So if you're still here what I'll do is insert a couple of comments between here related to you know 04:26.450 --> 04:32.330 these three users and these these three photos and this is where it gets kind of difficult to keep everything 04:32.330 --> 04:35.450 straight keep all the IDs and everything straight in your head. 04:35.450 --> 04:42.230 So we'll do an insert into comments and we'll have comment text then we'll start with a user id who's 04:42.650 --> 04:43.600 posting it. 04:43.760 --> 04:47.700 And then the photo ID values. 04:47.810 --> 04:52.270 So let's say our first comment is going to be blue. 04:52.370 --> 05:02.320 Commenting on this photo here saying this is our comment it's just me. 05:02.930 --> 05:03.580 OK. 05:03.840 --> 05:05.610 So that's the comment text and the user ID is. 05:05.620 --> 05:10.200 Who's saying it flew the cat has an idea of one. 05:10.510 --> 05:15.670 And I just know you can tell because we inserted her first but of course if we went back to our select 05:15.750 --> 05:23.260 way back here you can see now you can see there you can see here her IDs one. 05:23.530 --> 05:24.120 OK. 05:24.430 --> 05:33.160 And then the photo ID will say she's commenting on this one here a photo ID of two and I'll duplicate 05:33.160 --> 05:33.960 that. 05:33.970 --> 05:36.280 So let's say somebody else commented. 05:36.280 --> 05:44.980 Say someone says Amazing shot on the same photo photo here and say I commented on that. 05:45.010 --> 05:47.740 So that would be my user ID 3. 05:48.670 --> 05:50.930 And we'll do one more this time. 05:51.160 --> 05:56.300 Let's say Charlie Brown was commenting. 05:57.190 --> 05:59.940 I HEART. 06:00.970 --> 06:03.970 And Charlie Brown is easier I do too. 06:04.240 --> 06:10.990 And let's say he's commenting on the first photo that was submitted by blue user id one so you can see 06:10.990 --> 06:13.620 how it gets a little bit crazy. 06:13.870 --> 06:21.520 But we can do that now and make sure I have my semi colon that of a comma there this time if we source 06:21.520 --> 06:23.080 it everything looks good. 06:23.080 --> 06:26.950 We can do is select star from comments. 06:26.950 --> 06:27.580 There we go. 06:27.580 --> 06:32.180 We've got three comments we could put a user ID in and a photo ID. 06:32.200 --> 06:33.280 I want to do that now. 06:33.310 --> 06:36.360 We'll do it as an exercise later with some more complex data. 06:36.430 --> 06:38.920 But of course you can do that if you want a little bit of practice.