WEBVTT 00:00.150 --> 00:03.780 All right moving right along here we're working with photos this time. 00:03.930 --> 00:08.430 And the easiest way to store photos is just a single table photos. 00:08.700 --> 00:13.100 But of course they need to be related to a particular user who created the photos. 00:13.530 --> 00:20.550 So we'll have an idea and all will store about an image itself is the image you Arel which will just 00:20.550 --> 00:21.560 be a string. 00:21.630 --> 00:26.590 You know we could have added a caption as if our char excuse me not string could have added to caption. 00:26.690 --> 00:31.790 We could have added location data on Instagram you can say where it was taken. 00:32.070 --> 00:36.900 You know there's all sorts of things that we could do but we're just focusing on image you are all in 00:36.900 --> 00:37.890 our case. 00:37.890 --> 00:45.020 And then a user id very important foreign key pointing to users dot ID. 00:45.570 --> 00:50.710 And then we also have created ad just like we had with users just a nice way to be able to you know 00:51.030 --> 00:59.310 sort photos by most recent to least recent or figure out the first photos somebody posted or if one 00:59.310 --> 01:04.210 photo was posted before another if you know what one looks like it's this is actually happened to me. 01:04.200 --> 01:08.650 People take a photo of mine and repost it claiming that it's their own. 01:08.660 --> 01:15.600 Doesn't happen often but two times actually the same person and the person actually flipped the image 01:15.600 --> 01:18.360 like mirrored it posted it claimed that it was their own. 01:18.360 --> 01:25.030 But of course it could just show that mine was posted first thankfully because there's a date. 01:25.080 --> 01:26.450 I mean it didn't really matter anyways. 01:26.460 --> 01:27.010 Right. 01:27.050 --> 01:28.500 Just photos on their social network. 01:28.530 --> 01:32.730 But still having that created out there helps me there. 01:33.090 --> 01:34.530 So let's implement it now. 01:34.890 --> 01:36.480 It's pretty straightforward. 01:36.630 --> 01:39.660 Just create table photos 01:42.290 --> 01:51.230 we're going to have an I.D. We'll also have our photo or image you or ever we want to call that will 01:51.260 --> 01:56.430 have a user ID and then created that. 01:56.650 --> 02:07.940 So ID is an integer or it's autoincrement primary key will be referencing photos that ID from other 02:07.940 --> 02:14.960 tables because things like comments are associated with a particular photo or likes our and tags as 02:14.990 --> 02:15.470 well. 02:15.470 --> 02:23.210 So we need that primary key there image you or will just be a Vardar 255 book. 02:23.270 --> 02:24.630 We should add not no. 02:24.890 --> 02:31.760 We don't want that to be empty or to be all that's a big problem because there would be no photo to 02:31.760 --> 02:37.100 display and then user ID that would be an integer. 02:37.430 --> 02:41.780 And let's also make that not know a photo needs to be associated with the user. 02:41.780 --> 02:49.580 We can't have an orphan photo that has no user and then created that just be another time stamp that 02:49.610 --> 02:50.770 the default value of. 02:50.780 --> 02:52.490 Now the time is created. 02:52.970 --> 03:00.980 But we still need one more piece which is our foreign key constraint which is going to pertain to user 03:01.100 --> 03:12.070 underscore ID which is referencing references the users table the ID field as the referer and there 03:12.070 --> 03:12.860 you go. 03:13.370 --> 03:14.230 OK. 03:14.810 --> 03:17.180 And that will create our photos table. 03:17.600 --> 03:26.740 So if I just restarts this whole thing now if I do show tables we have both tables in there if I do 03:26.740 --> 03:33.170 or describe photos you've got photos there everything looks good and just like before. 03:33.220 --> 03:38.350 If you want to stick around I'm going to insert one or two photos in here associated with our three 03:38.350 --> 03:39.130 users. 03:39.130 --> 03:44.230 But I'm done with creating the schema so you feel free to move on to the next one which is comments 03:44.260 --> 03:46.860 but if you want you can stick around as I do. 03:46.870 --> 03:54.130 Insert into photos and DiVall just to an image URL and a user ID. 03:54.130 --> 04:01.150 Those are the two things we have to supply our self values and our first one let's say blue. 04:01.210 --> 04:05.130 Submit it and we'll come back to you or else. 04:05.710 --> 04:08.080 But Blue's idea is going to be one. 04:08.160 --> 04:14.940 And if we weren't sure could do you know select star from users can see blue SP1. 04:15.130 --> 04:16.400 Charlie Brown is too cold. 04:16.410 --> 04:17.170 It's three. 04:17.530 --> 04:19.230 So we'll just do something silly. 04:19.240 --> 04:20.120 Let's just say that you are. 04:20.120 --> 04:22.820 I was just random like that. 04:23.200 --> 04:31.710 I'll duplicate that a couple of times in card it's command shift D or control shift on a PC and then. 04:31.870 --> 04:38.030 But you know here's another random thing this time it's submitted by Charlie Brown. 04:38.260 --> 04:42.640 Let's say Charlie Brown has another one as well like that. 04:43.570 --> 04:43.880 OK. 04:43.930 --> 04:53.710 And I haven't submitted any photos that should work if we run our source again this time. 04:53.710 --> 04:59.680 So I'd drop the database recreated it switched to it use it with the use command created or two tables 04:59.830 --> 05:02.590 inserted all of this data. 05:02.590 --> 05:09.280 Now if we can do select star from photos we should have three simple photos there with a user ID. 05:09.280 --> 05:15.110 We're now at the point where we could do a simple join if we wanted to figure out who posted these photos. 05:15.370 --> 05:21.180 So again indefinitely if you feel good about this move on and don't waste time watching this. 05:21.180 --> 05:24.320 But if you want the practice we could just you select. 05:24.400 --> 05:30.220 Let's say I wanted to have the image you were all created at and the name of the username of the person 05:30.220 --> 05:31.730 instead of a user ID. 05:32.020 --> 05:47.130 So we'll just do a select start to start from photos join to a default inner join users on and we're 05:47.130 --> 05:52.780 going to do it where photos don't use your ID equals users dot ID. 05:53.040 --> 05:54.090 So if we start with that 05:57.050 --> 06:06.620 there's a lot there some let's send it out to just be photos that image you are now com users. 06:06.920 --> 06:09.840 User name savageness username. 06:10.400 --> 06:11.660 Yes just like that 06:14.710 --> 06:16.070 copy. 06:16.970 --> 06:17.390 OK. 06:17.460 --> 06:21.330 Now we can see that the cat created that and Charlie Brown created those two. 06:21.330 --> 06:22.110 All right. 06:22.110 --> 06:27.380 And what I'm going to do just to set a pattern for the next few videos is move these you know Joines 06:27.380 --> 06:30.610 and things that I'm just playing around with down to the bottom and comment them out. 06:30.840 --> 06:36.120 We don't want those to run but I'll leave the insert statements here as well as to create tables. 06:36.120 --> 06:36.390 All right. 06:36.390 --> 06:36.970 Moving on.