WEBVTT 00:00.150 --> 00:00.540 All right. 00:00.540 --> 00:04.230 So this will be a quick video where we just implement the two tables we need. 00:04.230 --> 00:05.550 They're both very simple. 00:05.640 --> 00:11.090 So tag's it's just an idea and a tag name photo tags is a photo ID and a tag ID. 00:11.360 --> 00:11.730 OK. 00:11.760 --> 00:13.420 So here I am in cloud 9. 00:13.530 --> 00:24.140 I will start with our tags table create table tags and all we need is an ID which is an integer auto 00:24.210 --> 00:36.450 increment primary key and then we'll also have our tag name which is just a VAR char 255 and we want 00:36.450 --> 00:38.270 to make it unique. 00:38.340 --> 00:41.480 We don't want to have oh my gosh I can spell unique. 00:41.520 --> 00:43.560 You don't want to have any duplicates in there. 00:44.280 --> 00:48.380 And then after that you know it would be nice to edit create it. 00:48.810 --> 00:54.380 Like I said so we could store it the first time a tag was used or created and that's it. 00:54.630 --> 00:56.160 So that's our tags table. 00:56.160 --> 00:57.270 This table here. 00:57.480 --> 00:59.820 Now we've got to worry about photo tags. 01:00.360 --> 01:05.690 So we'll do our create table photo tags or tagging. 01:05.790 --> 01:07.580 Which is also nice I guess. 01:07.710 --> 01:10.270 Well it is nice but also works. 01:10.350 --> 01:16.160 We don't need an ID although we need is a photo ID and then the tag ID. 01:16.310 --> 01:19.470 And those are both going to be integer not know 01:23.930 --> 01:24.800 we go. 01:25.130 --> 01:35.030 And then on top of that we need to add our foreign key constraints foreign key photo ID is referencing 01:35.760 --> 01:47.780 the photos table ID field duplicate that tag ID for his referencing tags table ID field. 01:47.780 --> 01:52.880 We also want to make sure that a user cant tag the same photo with the same hash tag multiple times. 01:53.090 --> 01:54.370 And thats an easy fix. 01:54.440 --> 01:59.060 We just do our same primary key with two items. 01:59.180 --> 02:06.550 Photo ID and tag ID and that just ensures that we can have multiple instances of the same photo tags. 02:07.100 --> 02:12.730 All right don't so my colon looks like I'm missing this comma here and there we go. 02:12.800 --> 02:13.780 Now if we try that 02:17.920 --> 02:19.070 and we look at our tables. 02:19.130 --> 02:25.400 There we go we've got comments for all those likes photo tags photos tags and users. 02:25.400 --> 02:26.790 And that's it for our schema. 02:26.810 --> 02:27.540 We're done. 02:27.740 --> 02:31.100 So in the next section if you'd like to tap out now. 02:31.130 --> 02:36.380 Move on to the next section we're going to give you a massive file with a bunch of data that I created 02:36.890 --> 02:39.050 took way too long to do that. 02:39.110 --> 02:43.960 We'll be able to do some exercises with and get some practice working with a bunch of tables. 02:44.150 --> 02:49.730 But if you want to see me just insert a couple of tags and photo tags to do that now. 02:49.760 --> 02:52.450 So if you're still here going to do that. 02:52.580 --> 02:57.290 Start with inserting a couple of tags and all we have is tag name 03:00.670 --> 03:07.270 and just to verify that you go back up to our tags it's just a tag name that we're inserting. 03:07.390 --> 03:11.140 So let's say I'm inserting the string you know adorable 03:13.810 --> 03:17.310 and we get to more say cute. 03:17.320 --> 03:20.150 And one more would be I don't know. 03:20.230 --> 03:21.840 Sun rise. 03:22.340 --> 03:22.960 OK. 03:23.340 --> 03:25.570 So that will answer our tags. 03:25.610 --> 03:28.350 Can you make sure that that works. 03:28.370 --> 03:31.840 Select star from tags. 03:31.840 --> 03:32.850 There we go. 03:32.980 --> 03:36.500 We've got three tags Now we associate them with photos. 03:36.910 --> 03:38.790 And so you've got three photos. 03:38.920 --> 03:43.750 You know it doesn't matter what they are but they have an idea of 1 2 and 3. 03:43.780 --> 03:53.980 So if you want to do an insert into spell answered correctly photo tags will start by taking the photo 03:53.980 --> 04:04.930 ID and then the tag ID OK values and let's say the first photo is tagged with adorable. 04:05.610 --> 04:12.150 And the first photo was also tagged with cute because it has an idea of two and then will say the second 04:12.150 --> 04:13.970 photo is tagged with. 04:14.040 --> 04:15.400 I don't know sunrise. 04:15.660 --> 04:21.260 And then the third photo is also tagged with cute like that. 04:21.820 --> 04:22.650 OK. 04:22.860 --> 04:26.090 So you can rerun this. 04:26.370 --> 04:27.140 Let's do this. 04:27.150 --> 04:31.280 Let's start from photo tags. 04:31.290 --> 04:31.890 There we go. 04:31.890 --> 04:33.270 Looks good. 04:33.270 --> 04:38.310 Now the true test is can we still insert something that shouldn't work like a duplicate. 04:38.310 --> 04:45.830 We already have one common one so entered into photo tags. 04:46.020 --> 04:54.360 Photo ID comma tag ID values one comma one and we get duplicate entry. 04:54.810 --> 04:55.950 So that's good. 04:55.950 --> 04:56.890 Works perfectly. 04:56.890 --> 05:02.340 But as you can see no other order works fine to come in three or three common two are not the same thing 05:02.340 --> 05:03.150 at all. 05:03.210 --> 05:04.950 This is saying the second tag. 05:04.990 --> 05:07.830 This is saying the second photo should have the third tag. 05:07.910 --> 05:10.590 This is saying third photo should have the second tag. 05:10.830 --> 05:12.030 Quite a mouthful. 05:12.060 --> 05:12.500 All right. 05:12.600 --> 05:13.950 So we're done here. 05:13.950 --> 05:16.310 Next up we're actually going to play around with data. 05:16.320 --> 05:18.440 Like I said I'm going to give you a giant file. 05:18.540 --> 05:19.490 Hopefully it's fun. 05:19.620 --> 05:20.980 And if it's not. 05:21.330 --> 05:25.110 Well we're basically in the course so just hang in there.