1 00:00:00,150 --> 00:00:00,510 All right. 2 00:00:00,510 --> 00:00:04,110 So this will be a quick video where we just implement the two tables we need. 3 00:00:04,110 --> 00:00:05,490 They're both very simple. 4 00:00:05,490 --> 00:00:08,160 So Tags is just an ID and a tag name. 5 00:00:08,160 --> 00:00:11,040 Photo tags is photo ID and a tag ID. 6 00:00:11,340 --> 00:00:19,170 Okay, so here I am in cloud nine and we'll start with our tags table, create table tags, and all 7 00:00:19,170 --> 00:00:26,010 we need is an ID, which is an integer auto increment primary key. 8 00:00:26,460 --> 00:00:33,210 And then we'll also have our tag name, which is just a var char. 9 00:00:34,130 --> 00:00:37,460 255 And we want to make it unique. 10 00:00:38,240 --> 00:00:43,160 We don't want to have oh my gosh, if I can spell unique, we don't want to have any duplicates in there. 11 00:00:44,180 --> 00:00:50,780 And then after that, it would be nice to add a created that, like I said, so we could store the first 12 00:00:50,780 --> 00:00:52,730 time a tag was used or created. 13 00:00:53,480 --> 00:00:54,380 And that's it. 14 00:00:54,590 --> 00:00:57,110 So that's our tags table, this table here. 15 00:00:57,320 --> 00:01:06,200 Now we've got to worry about photo tags, so we'll do our create table photo tags or tagging, which 16 00:01:06,200 --> 00:01:07,590 is also nice, I guess. 17 00:01:07,610 --> 00:01:10,040 Well, neither is nice, but also works. 18 00:01:10,160 --> 00:01:11,540 We don't need an ID. 19 00:01:11,570 --> 00:01:19,730 All that we need is a photo ID and then the tag ID and those are both going to be integer, not null. 20 00:01:23,920 --> 00:01:24,700 There we go. 21 00:01:24,970 --> 00:01:29,290 And then on top of that, we need to add our foreign key constraints. 22 00:01:30,580 --> 00:01:31,780 Foreign key. 23 00:01:32,320 --> 00:01:36,550 Photo ID is referencing the photos. 24 00:01:36,550 --> 00:01:43,510 Table ID field duplicate that tag ID is referencing. 25 00:01:45,480 --> 00:01:47,580 Tags table ID field. 26 00:01:47,670 --> 00:01:52,860 We also want to make sure that a user can't tag the same photo with the same hash tag multiple times. 27 00:01:52,950 --> 00:01:54,250 And that's an easy fix. 28 00:01:54,270 --> 00:02:02,760 We just do our same primary key with two items photo ID and tag ID, and that just ensures that we can't 29 00:02:02,760 --> 00:02:06,240 have multiple instances of the same photo tags. 30 00:02:07,080 --> 00:02:07,740 All right. 31 00:02:07,740 --> 00:02:09,030 Don't forget our semicolon. 32 00:02:09,180 --> 00:02:11,580 It looks like I'm missing this comma here. 33 00:02:11,580 --> 00:02:12,300 There we go. 34 00:02:12,660 --> 00:02:14,010 Now, if we try that. 35 00:02:17,960 --> 00:02:19,070 And we look at our tables. 36 00:02:19,070 --> 00:02:19,520 There we go. 37 00:02:19,520 --> 00:02:24,800 We've got comments, follows, likes, photo tags, photos, tags, end users. 38 00:02:25,250 --> 00:02:26,780 And that's it for our schema. 39 00:02:26,780 --> 00:02:27,620 We're done. 40 00:02:27,620 --> 00:02:32,540 So in the next section, if you'd like to tap out now, move on to the next section. 41 00:02:32,540 --> 00:02:36,710 I'm going to give you a massive file with a bunch of data that I created. 42 00:02:36,740 --> 00:02:39,110 Took way too long to do that. 43 00:02:39,110 --> 00:02:43,580 We'll be able to do some exercises with and get some practice working with a bunch of tables. 44 00:02:44,030 --> 00:02:48,470 But if you do want to see me, just insert a couple of tags and photo tags. 45 00:02:48,470 --> 00:02:49,520 I'm going to do that now. 46 00:02:49,610 --> 00:02:52,370 So if you're still here, I'm going to do that. 47 00:02:52,370 --> 00:02:57,590 I'll start with inserting a couple of tags and all we have is tag name. 48 00:03:00,580 --> 00:03:07,090 And just to verify that if we go back up to our tags, it's just a tag name that we're inserting. 49 00:03:07,240 --> 00:03:11,380 So let's say I'm inserting the string, you know, adorable. 50 00:03:13,780 --> 00:03:16,810 And we get to more, let's say cute. 51 00:03:17,200 --> 00:03:21,580 And one more would be, I don't know, sunrise. 52 00:03:23,290 --> 00:03:25,240 So that will insert our tags. 53 00:03:25,870 --> 00:03:27,430 Can make sure that that works. 54 00:03:28,300 --> 00:03:31,360 Select star from tags. 55 00:03:31,810 --> 00:03:32,560 There we go. 56 00:03:32,950 --> 00:03:34,030 We've got our three tags. 57 00:03:34,030 --> 00:03:34,390 Now. 58 00:03:34,390 --> 00:03:36,160 We associate them with photos. 59 00:03:36,790 --> 00:03:38,410 And so we've got three photos. 60 00:03:38,410 --> 00:03:38,890 I'm not. 61 00:03:39,400 --> 00:03:43,360 It doesn't matter what they are, but they have an idea of one, two and three. 62 00:03:43,660 --> 00:03:53,590 So if we want to do an insert into if I spell insert correctly photo tags, we'll start by taking the 63 00:03:53,590 --> 00:03:56,350 photo ID and then the tag ID 64 00:03:59,380 --> 00:04:00,190 values. 65 00:04:00,700 --> 00:04:08,230 And let's say the first photo is tagged with adorable, and the first photo is also tagged with cute 66 00:04:08,920 --> 00:04:10,450 because that has an idea of two. 67 00:04:10,870 --> 00:04:15,490 And then we'll say the second photo is tagged with, I don't know, Sunrise. 68 00:04:15,490 --> 00:04:20,860 And then the third photo is also tagged with cute like that. 69 00:04:22,810 --> 00:04:24,940 So we can rerun this. 70 00:04:26,380 --> 00:04:29,920 Let's do select star from photo tags. 71 00:04:31,270 --> 00:04:31,880 There we go. 72 00:04:31,900 --> 00:04:32,860 Looks good. 73 00:04:33,130 --> 00:04:38,120 And now the true test is can we still insert something that shouldn't work like a duplicate? 74 00:04:38,140 --> 00:04:50,530 We already have one comma one, so insert into photo tags, photo ID, comma, tag ID values one, comma 75 00:04:50,560 --> 00:04:54,040 one, and we get duplicate entry. 76 00:04:54,700 --> 00:04:55,870 So that's good. 77 00:04:55,870 --> 00:04:56,830 Works perfectly. 78 00:04:56,830 --> 00:04:59,590 But as you can see, the other order works fine too. 79 00:04:59,590 --> 00:05:03,070 Come a three or three comma two are not the same thing at all. 80 00:05:03,160 --> 00:05:07,480 This is saying the second tag or this is saying the second photo should have the third tag. 81 00:05:07,480 --> 00:05:10,690 That's this is saying the third photo should have the second tag. 82 00:05:10,720 --> 00:05:11,800 Quite a mouthful. 83 00:05:11,950 --> 00:05:12,520 All right. 84 00:05:12,520 --> 00:05:13,540 So we're done here. 85 00:05:13,900 --> 00:05:16,320 Next up, we're actually going to play around with data. 86 00:05:16,330 --> 00:05:18,450 Like I said, I'm going to give you a giant file. 87 00:05:18,460 --> 00:05:19,510 Hopefully it's fun. 88 00:05:19,510 --> 00:05:23,440 And if it's not, well, we're basically at the end of the course. 89 00:05:24,280 --> 00:05:25,330 So just hang in there.