1 00:00:02,180 --> 00:00:03,550 [Maximilian} So let's continue 2 00:00:03,550 --> 00:00:05,470 with the restaurants table. 3 00:00:05,470 --> 00:00:07,911 We do already have a restaurants table, 4 00:00:07,911 --> 00:00:10,863 but it doesn't have the structure we want. 5 00:00:10,863 --> 00:00:13,396 It has the name field, which we need, 6 00:00:13,396 --> 00:00:16,270 but then it has a type field, 7 00:00:16,270 --> 00:00:18,090 which I don't wanna have any more 8 00:00:18,090 --> 00:00:21,253 instead I wanna link to a separate types table. 9 00:00:22,140 --> 00:00:23,410 And therefore, actually, 10 00:00:23,410 --> 00:00:25,030 before I continue working on restaurants, 11 00:00:25,030 --> 00:00:27,560 I'll create another new table. 12 00:00:27,560 --> 00:00:29,430 And that will be the table that holds 13 00:00:29,430 --> 00:00:32,800 all the different restaurants types we'll have. 14 00:00:32,800 --> 00:00:35,630 And therefore, I will name this table types. 15 00:00:35,630 --> 00:00:39,590 You could also name it, restaurant_types 16 00:00:39,590 --> 00:00:42,823 if you want to, but I'll just go for types. 17 00:00:44,120 --> 00:00:45,660 All the defaults here are fine. 18 00:00:45,660 --> 00:00:47,623 And now we can add our columns. 19 00:00:48,570 --> 00:00:50,387 As in, basically, all my tables, 20 00:00:50,387 --> 00:00:53,790 I wanna have a unique id, which is an integer, 21 00:00:53,790 --> 00:00:55,950 which is my primary key and not null 22 00:00:55,950 --> 00:00:57,933 and which should be auto incrementing. 23 00:00:59,170 --> 00:01:01,230 And I'll then add a second column, 24 00:01:01,230 --> 00:01:04,780 which is the name of the type. 25 00:01:04,780 --> 00:01:08,260 So where I stored the actual type description, 26 00:01:08,260 --> 00:01:09,973 something like Italian. 27 00:01:11,240 --> 00:01:12,203 You could also name this description, 28 00:01:12,203 --> 00:01:15,150 therefore, I'll go for name. 29 00:01:15,150 --> 00:01:17,270 And here, let's say, we allow 30 00:01:17,270 --> 00:01:20,043 words up to 100 characters long. 31 00:01:21,160 --> 00:01:24,810 And I also wanna have NOT NULL values here 32 00:01:24,810 --> 00:01:27,073 so that this may never be omitted. 33 00:01:28,370 --> 00:01:29,700 And that's already all. 34 00:01:29,700 --> 00:01:31,140 It's a quite simple table, 35 00:01:31,140 --> 00:01:33,380 only has an id and then the name itself. 36 00:01:33,380 --> 00:01:35,917 And I only created for the single reason 37 00:01:35,917 --> 00:01:37,781 that I then have a table 38 00:01:37,781 --> 00:01:40,353 where we can have predefined types 39 00:01:40,353 --> 00:01:44,630 so that you can't just enter any arbitrary values 40 00:01:44,630 --> 00:01:46,640 when you add a new restaurant, 41 00:01:46,640 --> 00:01:48,340 but that you instead have to link 42 00:01:48,340 --> 00:01:50,010 to one of these predefined types, 43 00:01:50,010 --> 00:01:53,554 which could be managed by some global administrator 44 00:01:53,554 --> 00:01:55,150 or anything like that. 45 00:01:56,060 --> 00:01:58,070 We could have done the same for countries. 46 00:01:58,070 --> 00:01:59,791 As I mentioned to the last lecture, 47 00:01:59,791 --> 00:02:02,540 there I didn't do it so that we can finish 48 00:02:02,540 --> 00:02:04,730 creating the addresses table like that. 49 00:02:04,730 --> 00:02:07,396 But for types here, I wanna show you this approach 50 00:02:07,396 --> 00:02:11,093 of having such a predefined list of values. 51 00:02:12,063 --> 00:02:14,220 Therefore, here I'll click Apply. 52 00:02:14,220 --> 00:02:16,960 That's the SQL statement that will be executed. 53 00:02:16,960 --> 00:02:20,223 And we apply that to executed. 54 00:02:21,650 --> 00:02:23,450 Now, we got the types table. 55 00:02:23,450 --> 00:02:26,440 Now, we can work on the restaurants table. 56 00:02:26,440 --> 00:02:29,430 And now I wanna change the structure 57 00:02:29,430 --> 00:02:30,990 of the restaurants table. 58 00:02:30,990 --> 00:02:33,260 And that, of course, is something we haven't done 59 00:02:33,260 --> 00:02:34,343 up to this point. 60 00:02:35,310 --> 00:02:39,900 Now, to change it, we can right-click on restaurants here 61 00:02:39,900 --> 00:02:42,823 and actually choose Alter Table. 62 00:02:43,920 --> 00:02:48,213 Alternatively, we can drop the table and recreate it. 63 00:02:49,490 --> 00:02:51,530 Now, if I would choose Alter Table, 64 00:02:51,530 --> 00:02:54,540 we get almost the same wizard, 65 00:02:54,540 --> 00:02:56,186 which we get for creating a table, 66 00:02:56,186 --> 00:02:58,543 but we have some columns already. 67 00:02:59,400 --> 00:03:03,410 But here we could now also delete one of the columns. 68 00:03:03,410 --> 00:03:05,468 For example, delete the type column 69 00:03:05,468 --> 00:03:08,683 and then add any new columns that we want. 70 00:03:09,560 --> 00:03:11,820 Of course, if we do that on a table 71 00:03:11,820 --> 00:03:13,945 that already has some data stored, 72 00:03:13,945 --> 00:03:16,921 that means that if we add new columns, 73 00:03:16,921 --> 00:03:21,480 the existing data won't have any values in those columns. 74 00:03:21,480 --> 00:03:24,410 So altering a table can always be a bit tricky 75 00:03:24,410 --> 00:03:26,170 if it's a non-empty table, 76 00:03:26,170 --> 00:03:29,323 because you need to deal with that existing data in there. 77 00:03:30,250 --> 00:03:33,440 And therefore here, since we did only create a table 78 00:03:33,440 --> 00:03:35,233 to play around with it anyways, 79 00:03:35,233 --> 00:03:39,190 I'll instead rather drop it and recreate it. 80 00:03:39,190 --> 00:03:41,603 So I'll choose Drop Table here. 81 00:03:43,720 --> 00:03:47,610 We can then review the SQL statement that will be executed. 82 00:03:47,610 --> 00:03:50,020 So that's the code you could execute on your own 83 00:03:50,020 --> 00:03:51,330 to drop a table. 84 00:03:51,330 --> 00:03:54,613 And if you executed, that table gets deleted. 85 00:03:55,520 --> 00:03:58,987 This can't be undone, so you should be careful about that. 86 00:03:58,987 --> 00:04:02,590 Now, that it was deleted, I'll create a new table. 87 00:04:02,590 --> 00:04:05,100 And of course, again name it restaurants, 88 00:04:05,100 --> 00:04:06,770 keep all the defaults up there. 89 00:04:06,770 --> 00:04:09,910 And add my good old id field again, 90 00:04:09,910 --> 00:04:13,293 which is auto incrementing, a primary key, and NOT NULL. 91 00:04:14,340 --> 00:04:17,560 And then in there, I wanna have the name of the restaurant. 92 00:04:17,560 --> 00:04:22,210 And that again, should be a VARCHAR of 255 as a length. 93 00:04:22,210 --> 00:04:23,817 It should also be not null. 94 00:04:23,817 --> 00:04:27,080 But now things will get interesting. 95 00:04:27,080 --> 00:04:30,364 Now, I also wanna link to a specific address 96 00:04:30,364 --> 00:04:32,423 and a specific type. 97 00:04:33,510 --> 00:04:35,570 And for this, we add a brand new column 98 00:04:35,570 --> 00:04:37,880 and the convention for the column name. 99 00:04:37,880 --> 00:04:41,780 Here, typically is to reference the table 100 00:04:41,780 --> 00:04:43,190 to which you wanna link. 101 00:04:43,190 --> 00:04:44,598 For example, address. 102 00:04:44,598 --> 00:04:47,370 However, not the plural form, 103 00:04:47,370 --> 00:04:49,266 which is the actual table name, 104 00:04:49,266 --> 00:04:51,230 but the singular form, 105 00:04:51,230 --> 00:04:54,110 because we will link to a single record 106 00:04:54,110 --> 00:04:55,820 in that related table. 107 00:04:55,820 --> 00:04:58,140 So in this case, just address, 108 00:04:58,140 --> 00:05:00,193 and then underscore id, 109 00:05:01,260 --> 00:05:04,030 or whatever the unique key column name 110 00:05:04,030 --> 00:05:07,250 is you chose in that other table. 111 00:05:07,250 --> 00:05:08,968 In my case, in the addresses table, 112 00:05:08,968 --> 00:05:13,100 I have an id as a unique value for every record. 113 00:05:13,100 --> 00:05:15,790 And therefore here, I will link to address_id. 114 00:05:16,729 --> 00:05:19,170 Now, that's just a naming convention. 115 00:05:19,170 --> 00:05:23,140 Choosing this name on its own, doesn't do anything. 116 00:05:23,140 --> 00:05:25,257 It's just a convention to signal 117 00:05:25,257 --> 00:05:29,129 that the value that will be stored for this column 118 00:05:29,129 --> 00:05:30,741 in the restaurants table 119 00:05:30,741 --> 00:05:34,230 will actually be a key value 120 00:05:34,230 --> 00:05:37,935 that can be found in that other related table. 121 00:05:37,935 --> 00:05:40,403 In this case, the addresses table. 122 00:05:41,750 --> 00:05:44,600 Therefore, since I plan on storing 123 00:05:44,600 --> 00:05:47,680 one of the id's that I have an addresses, 124 00:05:47,680 --> 00:05:50,630 in this address_id column in restaurants, 125 00:05:50,630 --> 00:05:53,336 of course, the data type should be the same data type 126 00:05:53,336 --> 00:05:57,420 as I have it for my id in addresses. 127 00:05:57,420 --> 00:05:59,380 And there, I have an integer, 128 00:05:59,380 --> 00:06:01,763 so I'll choose integer here as well. 129 00:06:04,004 --> 00:06:08,560 Now, we can make this NOT NULL if we wanna enforce 130 00:06:08,560 --> 00:06:12,680 that every restaurant must have a related address. 131 00:06:12,680 --> 00:06:15,990 And here I wanna enforce that, but other than that 132 00:06:15,990 --> 00:06:17,663 that's basically it. 133 00:06:18,730 --> 00:06:21,470 Now, you might note that here at the bottom, 134 00:06:21,470 --> 00:06:25,040 you have this Foreign Keys tab in this wizard. 135 00:06:25,040 --> 00:06:28,310 You have a bunch of tabs, which we haven't touched yet. 136 00:06:28,310 --> 00:06:30,700 And most of these tabs are a bit more advanced 137 00:06:30,700 --> 00:06:32,030 and we don't need them, 138 00:06:32,030 --> 00:06:35,543 but I wanna have a look at the Foreign Keys tab here. 139 00:06:36,430 --> 00:06:37,870 Here, indeed, 140 00:06:37,870 --> 00:06:40,610 you can set up more details 141 00:06:40,610 --> 00:06:43,620 about such a foreign key relationship. 142 00:06:43,620 --> 00:06:44,560 Of for example, 143 00:06:44,560 --> 00:06:47,097 how related records should behave 144 00:06:47,097 --> 00:06:50,333 if one of the related records gets deleted? 145 00:06:51,370 --> 00:06:54,680 It's a bit more advanced and not something you have to do, 146 00:06:54,680 --> 00:06:56,770 and therefore, we will ignore it here. 147 00:06:56,770 --> 00:07:00,118 We dive deeper into that in a dedicated SQL course 148 00:07:00,118 --> 00:07:02,383 or in the official SQL documentation. 149 00:07:02,383 --> 00:07:05,610 Of course, in case you wanna learn more about that. 150 00:07:05,610 --> 00:07:08,603 For the moment, we'll ignore it and go back to our columns. 151 00:07:09,830 --> 00:07:12,580 And therefore, in the end, that's all we have to do. 152 00:07:12,580 --> 00:07:14,890 There isn't anything special about it. 153 00:07:14,890 --> 00:07:17,793 It's a regular column just with a special name 154 00:07:17,793 --> 00:07:20,320 that follows a certain convention, 155 00:07:20,320 --> 00:07:23,930 but only for following the conventions sake, 156 00:07:23,930 --> 00:07:28,460 not because that name would do anything special on its own. 157 00:07:28,460 --> 00:07:31,600 And we wanna ensure that we picked the right data type 158 00:07:31,600 --> 00:07:36,600 so that we later can store id's from our addresses table 159 00:07:36,730 --> 00:07:39,363 in that restaurants table, in that column here. 160 00:07:40,880 --> 00:07:44,010 And therefore, that will enable us to later link 161 00:07:44,010 --> 00:07:46,830 to a unique id from the addresses table. 162 00:07:46,830 --> 00:07:48,740 And I wanna do the same for my types. 163 00:07:48,740 --> 00:07:51,600 So therefore, I'll add a type_id. 164 00:07:51,600 --> 00:07:55,640 And I'll choose integer here and make this NOT NULL, 165 00:07:55,640 --> 00:08:00,640 so that I later can also link to types in my types table. 166 00:08:03,010 --> 00:08:04,460 With that, if I click Apply, 167 00:08:04,460 --> 00:08:08,210 you see it's still a regular SQL statement 168 00:08:08,210 --> 00:08:09,612 for my restaurants, 169 00:08:09,612 --> 00:08:13,490 but now we have these two special columns here, 170 00:08:13,490 --> 00:08:16,330 which don't look special, but which we know 171 00:08:16,330 --> 00:08:21,330 will be used for storing id's of records in other tables. 172 00:08:23,150 --> 00:08:25,370 And therefore, I'll apply this to recreate 173 00:08:25,370 --> 00:08:29,563 my new restaurants table with these special columns added. 174 00:08:31,449 --> 00:08:36,150 Now, as a last step, I wanna add the reviews table. 175 00:08:36,150 --> 00:08:38,750 And for this I'll create a number new table. 176 00:08:38,750 --> 00:08:41,393 And that table will be named reviews. 177 00:08:42,510 --> 00:08:43,760 Now, as all my tables, 178 00:08:43,760 --> 00:08:47,900 it will have an id field and that will be a primary key, 179 00:08:47,900 --> 00:08:51,620 NOT NULL, auto incrementing and an integer. 180 00:08:51,620 --> 00:08:54,701 And then we saw the kind of data we wanna store 181 00:08:54,701 --> 00:08:57,573 for a review on that slide. 182 00:08:59,820 --> 00:09:02,253 For example, we wanna store the name of the reviewer, 183 00:09:02,253 --> 00:09:05,270 the person who left the review, 184 00:09:05,270 --> 00:09:09,110 we could also work with a separate table for that, 185 00:09:09,110 --> 00:09:11,236 so we could store our reviewers, 186 00:09:11,236 --> 00:09:14,980 the persons who leave a review in a separate table 187 00:09:14,980 --> 00:09:16,224 and link to them. 188 00:09:16,224 --> 00:09:19,766 But I wanna keep things a bit simpler here, 189 00:09:19,766 --> 00:09:24,253 so I'll just store a reviewer_name here. 190 00:09:25,520 --> 00:09:28,700 And that can be a VARCHAR because I wanna store a string, 191 00:09:28,700 --> 00:09:31,210 I just wanna reserve more bytes here 192 00:09:31,210 --> 00:09:34,620 and allow for longer strings to be stored. 193 00:09:34,620 --> 00:09:36,093 This should also not be null. 194 00:09:37,500 --> 00:09:41,370 Then we need the actual rating and that should be a number. 195 00:09:41,370 --> 00:09:44,690 It should be a number, an integer, 196 00:09:44,690 --> 00:09:46,940 because we wanna leave a rating 197 00:09:46,940 --> 00:09:50,340 of one, two, three, four, or five. 198 00:09:50,340 --> 00:09:52,690 So I'll choose INT here. 199 00:09:52,690 --> 00:09:55,120 INT here make this not null, 200 00:09:55,120 --> 00:09:57,080 because I always wanna have a rating. 201 00:09:57,080 --> 00:10:00,660 And of course, I don't wanna allow any integer in there, 202 00:10:00,660 --> 00:10:03,625 but only one, two, three, four, or five. 203 00:10:03,625 --> 00:10:06,361 But that is something we can enforce 204 00:10:06,361 --> 00:10:10,760 in the application that would talk to the database. 205 00:10:10,760 --> 00:10:13,680 So in our NodeJS code, for example, 206 00:10:13,680 --> 00:10:16,590 there we could validate any user input 207 00:10:16,590 --> 00:10:20,650 and make sure that we only save a one, two, three, or five 208 00:10:20,650 --> 00:10:21,943 in our database. 209 00:10:22,830 --> 00:10:26,763 So here on the database side, this approach should suffice. 210 00:10:27,920 --> 00:10:32,690 You can also add some constraints and validation here 211 00:10:32,690 --> 00:10:37,260 when defining your tables schema, your structure, 212 00:10:37,260 --> 00:10:39,080 but that's all it's a bit more advanced 213 00:10:39,080 --> 00:10:41,093 so we'll not dive into that here. 214 00:10:43,290 --> 00:10:44,920 Instead, we now have the rating 215 00:10:44,920 --> 00:10:47,440 then next column I wanna add now 216 00:10:47,440 --> 00:10:50,283 is the text of the review. 217 00:10:51,280 --> 00:10:54,610 And this could now be a longer text. 218 00:10:54,610 --> 00:10:57,690 So whilst we could go for VARCHAR 219 00:10:57,690 --> 00:11:00,850 and allow maybe 1000 characters, 220 00:11:00,850 --> 00:11:02,545 which would probably do the trick, 221 00:11:02,545 --> 00:11:07,000 we can also choose to special text type here. 222 00:11:07,000 --> 00:11:09,163 Simply to also show this to you. 223 00:11:10,500 --> 00:11:12,840 A TEXT is a type you can use 224 00:11:12,840 --> 00:11:17,840 for storing longer text in your column. 225 00:11:17,850 --> 00:11:20,540 And here, you also don't need to specify 226 00:11:20,540 --> 00:11:22,340 how long the text should be, 227 00:11:22,340 --> 00:11:24,793 instead you can just use texts like this. 228 00:11:25,920 --> 00:11:27,226 So TEXT is a special type, 229 00:11:27,226 --> 00:11:30,450 which you would use for long pieces of texts. 230 00:11:30,450 --> 00:11:34,500 Maybe not necessarily required here for the review text, 231 00:11:34,500 --> 00:11:36,480 because it won't be that long. 232 00:11:36,480 --> 00:11:39,830 It might be more fitting for something like a blog post 233 00:11:39,830 --> 00:11:41,810 or a product description. 234 00:11:41,810 --> 00:11:44,100 But nonetheless, to also show you this type, 235 00:11:44,100 --> 00:11:45,653 here we'll go for TEXT. 236 00:11:46,525 --> 00:11:48,440 We could choose not null here, 237 00:11:48,440 --> 00:11:51,860 but actually we could also say that for the review text, 238 00:11:51,860 --> 00:11:54,110 that field might optional. 239 00:11:54,110 --> 00:11:56,679 You can leave a rating without entering a text, 240 00:11:56,679 --> 00:11:59,883 and therefore, I will not make this not null. 241 00:12:02,110 --> 00:12:05,460 Now, the next column I wanna add is the date. 242 00:12:05,460 --> 00:12:06,890 The date of a review. 243 00:12:06,890 --> 00:12:10,081 And for this, we also have special types we can choose. 244 00:12:10,081 --> 00:12:12,630 We got the DATE, and the DATETIME, and the TIME, 245 00:12:12,630 --> 00:12:16,523 and TIMESTAMP, and the YEAR value types. 246 00:12:17,490 --> 00:12:19,970 Now, they are all a bit different. 247 00:12:19,970 --> 00:12:23,920 For example, DATETIME contains a time part, 248 00:12:23,920 --> 00:12:26,220 whereas date is really just a date. 249 00:12:26,220 --> 00:12:27,777 So a single day in a year, 250 00:12:27,777 --> 00:12:30,561 which does not know anything about the time. 251 00:12:30,561 --> 00:12:34,510 Now, here I'll choose DATETIME, so that for our review, 252 00:12:34,510 --> 00:12:37,600 I do store both the date and also the time 253 00:12:37,600 --> 00:12:39,930 when the review was left. 254 00:12:39,930 --> 00:12:42,540 And here we can delete those parentheses. 255 00:12:42,540 --> 00:12:45,968 I wanna make it not null, so I did check this to ensure 256 00:12:45,968 --> 00:12:47,723 that we always have a DATETIME. 257 00:12:47,723 --> 00:12:51,659 And now I'll add a default expression here, 258 00:12:51,659 --> 00:12:55,293 where I will actually write CURRENT_TIMESTAMP, 259 00:12:57,227 --> 00:12:58,453 written like this. 260 00:13:00,370 --> 00:13:04,645 This will utilize a built-in feature provided by MySQL, 261 00:13:04,645 --> 00:13:06,629 which will ensure that this DATETIME 262 00:13:06,629 --> 00:13:11,629 is actually set automatically whenever we add a new entry, 263 00:13:12,100 --> 00:13:15,080 a new record into the reviews table. 264 00:13:15,080 --> 00:13:16,730 So that we don't have to find out 265 00:13:16,730 --> 00:13:19,850 which date we currently have and set it manually 266 00:13:19,850 --> 00:13:21,680 when we enter a new record, 267 00:13:21,680 --> 00:13:24,860 but that instead it will be set automatically. 268 00:13:24,860 --> 00:13:26,440 A very convenient feature, 269 00:13:26,440 --> 00:13:29,324 which we can unlock by setting CURRENT_TIMESTAMP 270 00:13:29,324 --> 00:13:32,893 as a default value for this date column. 271 00:13:34,570 --> 00:13:36,800 And with that, we're now almost done. 272 00:13:36,800 --> 00:13:40,833 The last column I wanna add is now my restaurant_id, 273 00:13:40,833 --> 00:13:44,003 because every review should be linked to a restaurant. 274 00:13:45,230 --> 00:13:48,600 And hence, I add the restaurant_id column 275 00:13:48,600 --> 00:13:50,090 here to the reviews table 276 00:13:50,090 --> 00:13:52,800 so that for every review that's stored here, 277 00:13:52,800 --> 00:13:55,820 we know to which restaurant it belongs. 278 00:13:55,820 --> 00:13:59,280 I don't store a review_id in the restaurants table 279 00:13:59,280 --> 00:14:00,660 for every restaurant, 280 00:14:00,660 --> 00:14:04,630 because a single restaurant can have more than one review. 281 00:14:04,630 --> 00:14:08,180 Whereas one review will only have one restaurant 282 00:14:08,180 --> 00:14:09,440 to which it belongs. 283 00:14:09,440 --> 00:14:13,680 That's why I add my restaurant_id here in the reviews table. 284 00:14:13,680 --> 00:14:16,950 And I'll set it to the same data type as my id 285 00:14:16,950 --> 00:14:19,800 is in the restaurants table, which is integer. 286 00:14:19,800 --> 00:14:21,370 And I'll set it to not null 287 00:14:21,370 --> 00:14:25,033 because every review must belong to some restaurant. 288 00:14:27,790 --> 00:14:30,550 And now with that all we can click Apply. 289 00:14:30,550 --> 00:14:33,320 That is the SQL statement that will be executed. 290 00:14:33,320 --> 00:14:35,610 And that will give us a reviews table, 291 00:14:35,610 --> 00:14:39,550 which meets the requirements outlined on the slide, 292 00:14:39,550 --> 00:14:42,518 and which will ensure that we can store all the review data, 293 00:14:42,518 --> 00:14:46,133 and that we link a single review to a restaurant. 294 00:14:47,210 --> 00:14:49,810 So let's click Apply, finished this. 295 00:14:49,810 --> 00:14:51,568 And now we got the four tables 296 00:14:51,568 --> 00:14:55,450 that I also show you on the slide before. 297 00:14:55,450 --> 00:14:57,960 Now, that we did all those tables, 298 00:14:57,960 --> 00:15:00,960 it's now finally time to add some data. 299 00:15:00,960 --> 00:15:04,483 And then of course also to see how we can query that data, 300 00:15:04,483 --> 00:15:08,300 especially also to see how we can query data 301 00:15:08,300 --> 00:15:11,943 that's related across multiple tables.