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