1
00:00:02,100 --> 00:00:04,860
Now to implement the table design,

2
00:00:04,860 --> 00:00:07,340
or the database design I showed you,

3
00:00:07,340 --> 00:00:10,410
I'm back here in MySQL Workbench.

4
00:00:10,410 --> 00:00:12,260
And, here, I have this restaurant's table,

5
00:00:12,260 --> 00:00:14,790
we worked on before, and, for the moment,

6
00:00:14,790 --> 00:00:16,239
we'll just ignore that.

7
00:00:16,239 --> 00:00:18,950
But I wanna add a new table now,

8
00:00:18,950 --> 00:00:21,210
and there are different ways of doing that.

9
00:00:21,210 --> 00:00:25,130
One of the easiest ways, here in MySQL Workbench,

10
00:00:25,130 --> 00:00:29,970
is to just right-click on Tables and choose Create Table.

11
00:00:29,970 --> 00:00:32,710
Alternatively, you can write the SQL command

12
00:00:32,710 --> 00:00:37,120
from the ground up yourself by entering a new SQL query,

13
00:00:37,120 --> 00:00:40,783
but I'll go for the easier, more graphical approach here.

14
00:00:42,190 --> 00:00:44,520
Now we need a name for that table,

15
00:00:44,520 --> 00:00:46,930
and since I'll store a bunch of addresses in there

16
00:00:46,930 --> 00:00:50,330
I'll choose addresses as a table name.

17
00:00:50,330 --> 00:00:53,680
I'll keep the Default Charset and Collation,

18
00:00:53,680 --> 00:00:56,520
and the Default Engine, and, now, we can add

19
00:00:56,520 --> 00:00:59,373
the different columns that we wanna have in there.

20
00:01:02,040 --> 00:01:04,420
For this, I'll increase this a little bit in size.

21
00:01:04,420 --> 00:01:07,080
And the first column, which I'll add,

22
00:01:07,080 --> 00:01:10,810
indeed will be my IDs for the addresses.

23
00:01:10,810 --> 00:01:14,170
So I wanna have an ID column so that every address

24
00:01:14,170 --> 00:01:18,830
will have a unique ID, and, hence, I'll name this column id.

25
00:01:18,830 --> 00:01:22,700
The Datatype can be INT, it should be my primary key

26
00:01:22,700 --> 00:01:26,940
and not now, and I'll also select auto incrementing,

27
00:01:26,940 --> 00:01:30,680
so that this is an integer, which automatically increments

28
00:01:30,680 --> 00:01:33,790
with every new item that we add into this table

29
00:01:35,180 --> 00:01:38,033
so that we never have to set the ID ourselves.

30
00:01:39,540 --> 00:01:43,860
Now we also want to add a street, a street number, a city,

31
00:01:43,860 --> 00:01:46,083
a postal code, and a country.

32
00:01:47,460 --> 00:01:50,620
And, for this here, I'll continue with the street,

33
00:01:50,620 --> 00:01:53,660
and, hence, I'll name the column, street.

34
00:01:53,660 --> 00:01:56,160
And now the question is which datatype

35
00:01:56,160 --> 00:01:58,200
do we wanna choose here?

36
00:01:58,200 --> 00:02:00,130
And I would say a VARCHAR.

37
00:02:00,130 --> 00:02:02,220
So a string is quite fitting

38
00:02:02,220 --> 00:02:05,320
because a street definitely is a string.

39
00:02:05,320 --> 00:02:08,660
I'll just allocate a bit more characters, here,

40
00:02:08,660 --> 00:02:13,227
255 should do the trick because that will ensure

41
00:02:13,227 --> 00:02:16,433
that we can also enter longer streets.

42
00:02:17,950 --> 00:02:20,360
Now I will also check not now

43
00:02:20,360 --> 00:02:23,320
because my street should always be set,

44
00:02:23,320 --> 00:02:25,993
this value should never be omitted,

45
00:02:26,950 --> 00:02:29,090
I always wanna have a street.

46
00:02:29,090 --> 00:02:31,110
But other than that, I'll not set anything

47
00:02:31,110 --> 00:02:32,843
and, now, we do have that street.

48
00:02:34,440 --> 00:02:36,300
Now we have the id and the street.

49
00:02:36,300 --> 00:02:40,310
Now I also, of course, wanna add my street number,

50
00:02:40,310 --> 00:02:43,080
and I'll name this street_number,

51
00:02:43,080 --> 00:02:45,260
and that's a common way of naming this.

52
00:02:45,260 --> 00:02:49,870
You typically don't choose street number like this,

53
00:02:49,870 --> 00:02:54,320
this CamelCase notation, which you know from JavaScript,

54
00:02:54,320 --> 00:02:57,930
but, instead, typically you separate multiple words

55
00:02:57,930 --> 00:03:01,290
with an underscore so that you have street number,

56
00:03:01,290 --> 00:03:03,663
all lower case written like that.

57
00:03:05,740 --> 00:03:09,490
Now, the question is which kind of data is a street number?

58
00:03:09,490 --> 00:03:12,290
And, given its name, you might say it's a number

59
00:03:12,290 --> 00:03:13,893
so we could choose an integer.

60
00:03:15,410 --> 00:03:19,070
That could work, but in some countries,

61
00:03:19,070 --> 00:03:21,610
or probably in most countries,

62
00:03:21,610 --> 00:03:26,610
you can also have numbers like 23 A and B.

63
00:03:26,620 --> 00:03:28,570
So you don't just have numbers,

64
00:03:28,570 --> 00:03:32,650
but you can have annotations to those street numbers.

65
00:03:32,650 --> 00:03:34,560
And, therefore, I actually, again,

66
00:03:34,560 --> 00:03:38,780
wanna store some text here, hence, I'll go for varchar

67
00:03:38,780 --> 00:03:43,780
and then maybe just something like 45 will do

68
00:03:44,230 --> 00:03:46,750
because I don't need a super long

69
00:03:47,610 --> 00:03:49,463
street number stored in there.

70
00:03:51,440 --> 00:03:53,840
Now it already added the next column for me, here,

71
00:03:53,840 --> 00:03:55,580
of course I'll override it, though,

72
00:03:55,580 --> 00:03:59,010
because the next column I wanna store is the city.

73
00:03:59,010 --> 00:04:00,590
And that, again, should be some texts,

74
00:04:00,590 --> 00:04:05,590
so I'll go for VARCHAR, here, and then just store some text.

75
00:04:07,230 --> 00:04:10,910
And, again, here, I wanna add a little bit more flexibility

76
00:04:10,910 --> 00:04:14,680
and allow the storage of longer pieces of texts,

77
00:04:14,680 --> 00:04:16,483
so I'll go for 255.

78
00:04:17,660 --> 00:04:21,440
This also should be not now, and, because I forgot it,

79
00:04:21,440 --> 00:04:25,580
I also wanna have not now, here, for my street number.

80
00:04:25,580 --> 00:04:28,513
So, basically, all these columns should be not now.

81
00:04:30,600 --> 00:04:34,390
Now, as a next step, we wanna store the postal code.

82
00:04:34,390 --> 00:04:37,143
And, again, I'll name it postal_code.

83
00:04:38,850 --> 00:04:40,743
Now, which data type is this?

84
00:04:42,010 --> 00:04:46,050
Now, here, I have to say that I don't know if this concept

85
00:04:46,050 --> 00:04:48,340
even exists in all countries in the world

86
00:04:48,340 --> 00:04:51,440
and if it works the same way in all countries.

87
00:04:51,440 --> 00:04:54,650
Here, in Germany, we could store an integer

88
00:04:54,650 --> 00:04:57,190
because, here in Germany, where I live,

89
00:04:57,190 --> 00:05:00,380
a postal code always is a number,

90
00:05:00,380 --> 00:05:03,920
which is four or five digits long.

91
00:05:03,920 --> 00:05:06,500
So, here, an integer would work, and, therefore,

92
00:05:06,500 --> 00:05:10,140
I'll go for an integer, here, also to mix things up.

93
00:05:10,140 --> 00:05:13,820
But I will say that we could also go for a varchar,

94
00:05:13,820 --> 00:05:17,580
again, to store a string to have more flexibility

95
00:05:17,580 --> 00:05:22,530
in case a postal code could contain non-numeric characters,

96
00:05:22,530 --> 00:05:26,270
or we could also argue that we wanna store a varchar

97
00:05:26,270 --> 00:05:30,443
instead of an integer because we might not do any

98
00:05:30,443 --> 00:05:34,790
mathematical work with the postal code.

99
00:05:34,790 --> 00:05:37,840
So we don't really need it as a number,

100
00:05:37,840 --> 00:05:41,440
instead having it as a string makes perfect sense

101
00:05:41,440 --> 00:05:44,800
because we read it more like text than a number,

102
00:05:44,800 --> 00:05:48,453
we don't do any number operations with a postal code.

103
00:05:49,440 --> 00:05:53,300
Nonetheless, to mix things up here, I'll go for an integer,

104
00:05:53,300 --> 00:05:55,410
but a varchar would also be fine,

105
00:05:55,410 --> 00:05:56,913
and I'll set it to not now.

106
00:05:58,720 --> 00:06:00,850
And, therefore, now as a last field

107
00:06:00,850 --> 00:06:04,100
I'll add the country, and the country, again,

108
00:06:04,100 --> 00:06:07,780
will be a VARCHAR for me because it will be some text,

109
00:06:07,780 --> 00:06:10,063
and I'll go for 255 here.

110
00:06:11,390 --> 00:06:12,940
As a little side note,

111
00:06:12,940 --> 00:06:17,440
we could also store countries in a separate table

112
00:06:17,440 --> 00:06:20,910
so that we have a predefined list of countries

113
00:06:20,910 --> 00:06:24,195
and we then only link to these countries, here,

114
00:06:24,195 --> 00:06:27,500
from inside the addresses table.

115
00:06:27,500 --> 00:06:31,700
I'll leave that as an extra exercise and task to you

116
00:06:31,700 --> 00:06:34,180
at the end of this course section,

117
00:06:34,180 --> 00:06:37,547
once you learned how you can link to other tables.

118
00:06:37,547 --> 00:06:41,320
For the moment I'll just define country as some text,

119
00:06:41,320 --> 00:06:42,943
which should also not be empty.

120
00:06:44,940 --> 00:06:48,270
And that, therefore, that's now my final structure

121
00:06:48,270 --> 00:06:49,950
for this table.

122
00:06:49,950 --> 00:06:53,730
A bunch of strings, possibly strings only,

123
00:06:53,730 --> 00:06:57,360
except for the id, if we would turn postal code

124
00:06:57,360 --> 00:07:00,050
into a string, in my case it's an integer,

125
00:07:00,050 --> 00:07:01,960
but no other value types

126
00:07:01,960 --> 00:07:05,760
because it turns out that for my address data

127
00:07:05,760 --> 00:07:07,803
I basically only have texts.

128
00:07:09,597 --> 00:07:12,910
Now, with that, we can click Apply, here,

129
00:07:12,910 --> 00:07:16,260
and this now shows us the SQL statement

130
00:07:16,260 --> 00:07:19,630
that we could have also written ourselves.

131
00:07:19,630 --> 00:07:24,010
And if I now run Apply, this table gets added.

132
00:07:24,010 --> 00:07:25,700
Here you see it.

133
00:07:25,700 --> 00:07:28,910
And now if you click on this icon, here,

134
00:07:28,910 --> 00:07:31,720
you can automatically run a query on it.

135
00:07:31,720 --> 00:07:35,500
Alternatively, you write this select query on your own,

136
00:07:35,500 --> 00:07:37,860
and then you see the structure here

137
00:07:37,860 --> 00:07:40,010
and any data that's stored in it,

138
00:07:40,010 --> 00:07:42,620
but, at the moment, we have no entries, and, therefore,

139
00:07:42,620 --> 00:07:44,263
we have no data in there.

140
00:07:45,240 --> 00:07:47,550
But that's now the addresses table added,

141
00:07:47,550 --> 00:07:50,170
that's how I added it here.

142
00:07:50,170 --> 00:07:53,130
And, with that, I would say let's now work

143
00:07:53,130 --> 00:07:56,320
on the rest of this slide I showed you,

144
00:07:56,320 --> 00:07:59,450
and let's also adjust the restaurant's table

145
00:07:59,450 --> 00:08:01,990
to fit our new requirements,

146
00:08:01,990 --> 00:08:05,853
and let's also add our types and reviews tables.