1 00:00:00,150 --> 00:00:00,540 All right. 2 00:00:00,540 --> 00:00:01,810 Moving right along here. 3 00:00:01,830 --> 00:00:08,189 We're working with photos this time, and the easiest way to store photos is just a single table photos. 4 00:00:08,670 --> 00:00:12,900 But, of course, they need to be related to a particular user who created the photos. 5 00:00:13,380 --> 00:00:20,730 So we'll have an ID and all we'll store about an image itself is the image URL, which will just be 6 00:00:20,730 --> 00:00:21,510 a string. 7 00:00:21,810 --> 00:00:24,360 We could have added a caption as a var char. 8 00:00:24,360 --> 00:00:25,290 Excuse me, not a string. 9 00:00:25,300 --> 00:00:26,610 We could have added to caption. 10 00:00:26,610 --> 00:00:29,520 We could have added location data on Instagram. 11 00:00:29,520 --> 00:00:31,260 You can say where it was taken. 12 00:00:32,040 --> 00:00:37,050 You know, there's all sorts of things that we could do, but we're just focusing on image URL in our 13 00:00:37,050 --> 00:00:47,150 case and then a user ID, very important foreign key pointing to users ID, And then we also have created 14 00:00:47,160 --> 00:00:48,930 it just like we had with users. 15 00:00:48,930 --> 00:00:57,000 Just a nice way to be able to sort photos by most recent, to least recent or figure out the first photo 16 00:00:57,000 --> 00:01:03,930 somebody posted or if one photo is posted before another, if one looks like it's this is actually happened 17 00:01:03,930 --> 00:01:08,580 to me, or people take a photo of mine and repost it claiming that it's their own. 18 00:01:08,610 --> 00:01:11,940 It doesn't happen often, but two times actually by the same person. 19 00:01:12,750 --> 00:01:18,060 And the person actually like flipped the image like mirrored it, posted it and claimed that it was 20 00:01:18,060 --> 00:01:18,360 their own. 21 00:01:18,360 --> 00:01:24,960 But of course I could just show that mine was posted first, thankfully, because there's a date. 22 00:01:24,960 --> 00:01:26,990 I mean, it didn't really matter anyways, right? 23 00:01:27,000 --> 00:01:32,520 It's just photos on a social network, but still having that created ADD in there helped me there. 24 00:01:32,970 --> 00:01:34,530 So let's implement it now. 25 00:01:34,890 --> 00:01:36,300 It's pretty straightforward. 26 00:01:36,630 --> 00:01:39,930 Just create table photos. 27 00:01:42,290 --> 00:01:50,570 We're going to have an ID, we'll also have our photo or image URL, whatever we want to call that. 28 00:01:50,870 --> 00:01:56,120 We'll have user ID and then create a DAT. 29 00:01:56,690 --> 00:02:01,310 So ID is an integer or int auto increment. 30 00:02:01,460 --> 00:02:11,420 Primary key will be referencing photos dot ID from other tables because things like comments are associated 31 00:02:11,420 --> 00:02:15,410 with a particular photo or likes are and tags as well. 32 00:02:15,410 --> 00:02:17,210 So we need that primary key. 33 00:02:17,210 --> 00:02:22,100 Their image URL will just be a var char 255. 34 00:02:22,880 --> 00:02:24,700 But we should add not null. 35 00:02:24,710 --> 00:02:28,520 We don't want that to be empty or to be null. 36 00:02:28,520 --> 00:02:36,980 That's a big problem because there'd be no photo to display and then user ID that would be an integer. 37 00:02:37,280 --> 00:02:39,050 And let's also make that not null. 38 00:02:39,050 --> 00:02:41,660 A photo needs to be associated with the user. 39 00:02:41,660 --> 00:02:47,570 We can't have an orphan photo that has no user and then created that. 40 00:02:47,570 --> 00:02:53,600 We'll just be another timestamp with the default value of Now the time is created, but we still need 41 00:02:53,600 --> 00:03:01,550 one more piece, which is our foreign key constraint, which is going to pertain to user underscore 42 00:03:01,550 --> 00:03:11,900 ID, which is referencing or references the user's table, the ID field spell that right reference ID, 43 00:03:11,900 --> 00:03:12,620 there we go. 44 00:03:14,690 --> 00:03:16,970 And that will create our photos table. 45 00:03:17,600 --> 00:03:20,360 So if I just resource this whole thing. 46 00:03:22,140 --> 00:03:26,400 Now, if I do show tables, we have both tables in there. 47 00:03:26,400 --> 00:03:30,480 If I do a describe photos, we've got photos there. 48 00:03:30,480 --> 00:03:31,640 Everything looks good. 49 00:03:31,650 --> 00:03:37,410 And just like before, if you want to stick around, I'm going to insert one or two photos in here associated 50 00:03:37,410 --> 00:03:39,000 with our three users. 51 00:03:39,000 --> 00:03:44,250 But I'm done with creating the schema so you feel free to move on to the next one, which is comments. 52 00:03:44,250 --> 00:03:52,140 But if you want, you can stick around as I do an insert into photos and we'll just do an image URL 53 00:03:52,320 --> 00:03:54,120 and a user ID. 54 00:03:54,150 --> 00:03:58,620 Those are the two things we have to supply ourself values. 55 00:03:58,620 --> 00:04:06,870 And our first one, let's say Blue submits it and we'll come back to the URL, but Blue's ID is going 56 00:04:06,870 --> 00:04:15,630 to be one and if we weren't sure, could do select star from users can see Blue is one, Charlie Brown 57 00:04:15,630 --> 00:04:17,070 is two and Colt is three. 58 00:04:17,430 --> 00:04:19,260 So we'll just do something silly. 59 00:04:19,260 --> 00:04:22,290 Let's just say the URL is just random like that. 60 00:04:23,160 --> 00:04:25,110 I'll duplicate that a couple of times. 61 00:04:25,110 --> 00:04:29,880 And cloud nine it's command shift dx or control shift dx on a PC. 62 00:04:30,810 --> 00:04:34,020 And then but here's another random thing. 63 00:04:34,020 --> 00:04:38,010 This time it's submitted by Charlie Brown. 64 00:04:38,160 --> 00:04:42,120 Let's say Charlie Brown has another one as well like that. 65 00:04:43,560 --> 00:04:43,870 Okay. 66 00:04:43,920 --> 00:04:45,960 And I haven't submitted any photos. 67 00:04:47,370 --> 00:04:53,550 That should work if we run our source again this time. 68 00:04:53,700 --> 00:04:59,760 So it dropped the database, recreated it, switched to it with the user command, created our two tables, 69 00:04:59,760 --> 00:05:01,800 inserted all of this data. 70 00:05:02,490 --> 00:05:08,460 Now, if we can do select star from photos and we should have three simple photos there with a user 71 00:05:08,460 --> 00:05:09,000 ID. 72 00:05:09,120 --> 00:05:14,400 We're now at the point where we could do a simple join if we wanted to, to figure out who posted these 73 00:05:14,400 --> 00:05:15,090 photos. 74 00:05:15,180 --> 00:05:21,180 So again, definitely if you feel good about this, move on and don't waste time watching this. 75 00:05:21,180 --> 00:05:24,340 But if you want the practice, we could just do a select. 76 00:05:24,360 --> 00:05:30,540 Let's say I wanted to have the image URL created at and the name the username of the person instead 77 00:05:30,540 --> 00:05:31,680 of the user ID. 78 00:05:31,830 --> 00:05:34,170 So we'll just do a select. 79 00:05:35,700 --> 00:05:37,890 Star to start from. 80 00:05:38,910 --> 00:05:49,050 Photos join and we'll just do a default inner join users on and we're going to do it where photos dot 81 00:05:49,350 --> 00:05:52,670 user ID equals user's ID. 82 00:05:52,890 --> 00:05:54,360 So if we start with that. 83 00:05:57,020 --> 00:05:57,830 There's a lot there. 84 00:05:57,830 --> 00:06:07,910 So let's thin it out to just be photos, dot image URL, comma users, dot username. 85 00:06:08,300 --> 00:06:08,960 Is that what it is? 86 00:06:08,960 --> 00:06:09,680 Username? 87 00:06:10,280 --> 00:06:10,970 Yes. 88 00:06:10,970 --> 00:06:11,900 Just like that. 89 00:06:14,710 --> 00:06:15,550 Copy it. 90 00:06:17,010 --> 00:06:17,260 Okay. 91 00:06:17,400 --> 00:06:21,030 And now we can see the cat created that and Charlie Brown created those two. 92 00:06:21,300 --> 00:06:21,930 All right. 93 00:06:21,930 --> 00:06:27,750 And what I'm going to do just to set a pattern for the next few videos is move these joints and things 94 00:06:27,750 --> 00:06:30,450 that I'm just playing around with down to the bottom and comment them out. 95 00:06:30,690 --> 00:06:35,640 We don't want those to run, but I'll leave the insert statements here as well as to create tables. 96 00:06:36,030 --> 00:06:36,390 All right. 97 00:06:36,390 --> 00:06:37,230 Moving on.