1
00:00:01,530 --> 00:00:04,480
and i'm looking taxes 2013 shoot or ooze nice video

2
00:00:04,480 --> 00:00:09,859
I'm gonna be talking you through a tables and data types associate abrams

3
00:00:09,859 --> 00:00:14,099
so if you wanna play along following bloodiest in the section website

4
00:00:14,099 --> 00:00:14,849
download

5
00:00:14,849 --> 00:00:18,890
axis 2013 DB 02 however

6
00:00:18,890 --> 00:00:23,868
if I'm being honest I don't really need to so this open this but but often

7
00:00:23,868 --> 00:00:27,070
2013 we can

8
00:00:27,070 --> 00:00:30,539
what is actually going it the all the tables from the

9
00:00:30,539 --> 00:00:33,710
photo booking system so

10
00:00:33,710 --> 00:00:38,820
as wouldn't a touch anyway DoD really need to worry about that the there

11
00:00:38,820 --> 00:00:40,140
anyway to see can check em out

12
00:00:40,140 --> 00:00:44,750
and right so tables to our tables tables are

13
00:00:44,750 --> 00:00:48,289
two-dimensional greets which is stored data

14
00:00:48,289 --> 00:00:51,590
so think about Excel spreadsheets

15
00:00:51,590 --> 00:00:56,140
and think about her list update thing about shopping list

16
00:00:56,140 --> 00:01:00,329
where you have one column in most who rose through describe that

17
00:01:00,329 --> 00:01:04,269
table terminology first row been on a second row

18
00:01:04,269 --> 00:01:08,350
tomatoes third row milk

19
00:01:08,349 --> 00:01:13,349
so this kind of table you crazy self Excel spreadsheets the same ID

20
00:01:13,349 --> 00:01:16,509
I'm except you can have multiple brokers

21
00:01:16,509 --> 00:01:19,689
I'm so multiple columns so

22
00:01:19,688 --> 00:01:22,879
you have your shopping list on the left and you could have the quantity

23
00:01:22,879 --> 00:01:26,728
the next row over on my view of the location

24
00:01:26,728 --> 00:01:29,969
the column after that I keep saying roe

25
00:01:29,969 --> 00:01:32,969
and I mean to say column what am i doing okay so

26
00:01:32,969 --> 00:01:36,140
hopefully get the idea to a point now if we go to

27
00:01:36,140 --> 00:01:39,530
cray on the tap and go to

28
00:01:39,530 --> 00:01:43,820
let's go Craig table rather than table design

29
00:01:43,819 --> 00:01:48,000
you start to see the idea of what I'm talking about

30
00:01:48,000 --> 00:01:52,430
in the you go a column here which in this case is called ID

31
00:01:52,430 --> 00:01:55,670
and let's name this second column

32
00:01:55,670 --> 00:01:58,850
I must go to from this are here

33
00:01:58,849 --> 00:02:02,339
go to show text amoco this one

34
00:02:02,340 --> 00:02:08,689
shopping list the election will decide the columns was going to lead to

35
00:02:08,689 --> 00:02:12,300
you must but later

36
00:02:12,300 --> 00:02:15,670
you can't really

37
00:02:15,669 --> 00:02:20,339
more authority than a minute and okay sir I click a save this included

38
00:02:20,340 --> 00:02:25,930
table shopping list

39
00:02:25,930 --> 00:02:30,459
and researcher from list and we want number here

40
00:02:30,459 --> 00:02:35,098
quantity and

41
00:02:35,098 --> 00:02:39,229
so far less quantity and over short text again

42
00:02:39,229 --> 00:02:43,609
location so

43
00:02:43,610 --> 00:02:46,950
now let's see if we can to lead to no

44
00:02:46,949 --> 00:02:52,459
week ago design view much time on it we can go to leave that

45
00:02:52,459 --> 00:02:56,080
good starter or never wanna see again II Scott

46
00:02:56,080 --> 00:03:02,510
so and but is it able to hit stores this that data like I said so for example

47
00:03:02,509 --> 00:03:06,919
I'm ban on this only been honest we want

48
00:03:06,919 --> 00:03:10,229
say 5

49
00:03:10,229 --> 00:03:15,030
the known as we're going to get them from the supermarkets

50
00:03:15,030 --> 00:03:18,919
I'm choosing a generic name so as to avoid any kind

51
00:03:18,919 --> 00:03:22,768
issues in the future who else do you want tomatoes

52
00:03:22,769 --> 00:03:25,810
I meet multiple

53
00:03:25,810 --> 00:03:30,598
6 tomatoes were from the fruits too

54
00:03:30,598 --> 00:03:34,310
and if if you know of a free stories

55
00:03:34,310 --> 00:03:37,890
an and well said I say

56
00:03:37,889 --> 00:03:41,658
you ok quantity

57
00:03:41,658 --> 00:03:45,489
to from the corner shop

58
00:03:45,489 --> 00:03:48,509
wait for you get it from the

59
00:03:48,509 --> 00:03:52,459
supermarket you when they're by in Boca is no new chief for

60
00:03:52,459 --> 00:03:56,049
you got it right so that's the idea ever tell you nothing

61
00:03:56,049 --> 00:04:00,129
devastatingly major about this this at a time

62
00:04:00,128 --> 00:04:03,179
so in one column we have

63
00:04:03,180 --> 00:04:06,819
the the may know if the table which is

64
00:04:06,818 --> 00:04:10,329
the armor buying the second column we're listing

65
00:04:10,330 --> 00:04:14,480
the quantity and third column will list in the location

66
00:04:14,479 --> 00:04:18,939
I'm to sit at tables now with data types come in today s

67
00:04:18,939 --> 00:04:23,579
if you drop this into design view I'm

68
00:04:23,579 --> 00:04:27,659
we're choosing the data type in this case got short text this case but number

69
00:04:27,660 --> 00:04:29,130
in this case ago short text

70
00:04:29,129 --> 00:04:32,719
basically the way it works is the I'm

71
00:04:32,720 --> 00:04:37,380
way computers work generally is the different datatypes take up different

72
00:04:37,379 --> 00:04:38,399
amounts a memory

73
00:04:38,399 --> 00:04:43,969
so this word have been honors takes up more memory

74
00:04:43,970 --> 00:04:47,449
than this number five deaths I'm and

75
00:04:47,449 --> 00:04:51,639
for that reason it's important when you make tables at you

76
00:04:51,639 --> 00:04:55,350
choose the most accurate data type is what you sent retelling

77
00:04:55,350 --> 00:04:59,410
access to do when you choose I'm sure text or number is

78
00:04:59,410 --> 00:05:04,139
to make a sort of space a in the memory for

79
00:05:04,139 --> 00:05:07,340
potential I'm items

80
00:05:07,339 --> 00:05:11,638
and so access can organize this memory better it's basically like having

81
00:05:11,639 --> 00:05:14,970
like to lockers a small locker in a large locker

82
00:05:14,970 --> 00:05:18,229
I in the small locker you wanna put small things in the large look you want

83
00:05:18,228 --> 00:05:18,769
to

84
00:05:18,769 --> 00:05:21,939
large things if you this MOU things in the large locka

85
00:05:21,939 --> 00:05:25,629
when it comes to analyzing the in there won't be any space and that's kinda the

86
00:05:25,629 --> 00:05:26,719
same with memory

87
00:05:26,720 --> 00:05:29,850
so you can streamline it down if you look at the different

88
00:05:29,850 --> 00:05:32,960
datatype you got short text and long text

89
00:05:32,959 --> 00:05:37,149
the basically the same idea which is you can put characters there

90
00:05:37,149 --> 00:05:40,859
I'm things that make up words I'm

91
00:05:40,860 --> 00:05:44,080
telephone numbers would actually be short text for example

92
00:05:44,079 --> 00:05:48,819
for the actual text rather than number could you not to calculations on them

93
00:05:48,819 --> 00:05:51,978
they def see short takes long Texas short text has

94
00:05:51,978 --> 00:05:55,449
limit of 255 characters

95
00:05:55,449 --> 00:05:59,728
and long text has a slim if something about 16,000 old

96
00:05:59,728 --> 00:06:02,758
I'm so things each store in short text

97
00:06:02,759 --> 00:06:07,129
I'm for example you go shopping list you gonna store the name to the art because

98
00:06:07,129 --> 00:06:08,279
the show

99
00:06:08,279 --> 00:06:12,948
people's first and last names for sign up an address

100
00:06:12,949 --> 00:06:16,538
think the story long text you might have a table

101
00:06:16,538 --> 00:06:19,909
about something in my van note's feels so you then you probably have

102
00:06:19,910 --> 00:06:24,289
that as long text right next to get the number

103
00:06:24,288 --> 00:06:27,529
I'm now we've got between numbers every

104
00:06:27,529 --> 00:06:30,899
actually different talk today five so when you

105
00:06:30,899 --> 00:06:35,318
to numb the default data type is long integer and integers a whole number

106
00:06:35,319 --> 00:06:38,650
so this means to here

107
00:06:38,649 --> 00:06:42,439
with quantity you can never store for example 5.5

108
00:06:42,439 --> 00:06:45,839
for can't run do anything you want should work well as tons actually

109
00:06:45,839 --> 00:06:46,549
roundout

110
00:06:46,550 --> 00:06:49,759
6 and so you can never do that

111
00:06:49,759 --> 00:06:53,050
because it's an integer now long integer

112
00:06:53,050 --> 00:06:56,509
you go into journal on integer into just those

113
00:06:56,509 --> 00:06:59,569
from some like minus 30 2000

114
00:06:59,569 --> 00:07:05,810
up to 32,000 city trying to thanks 13760

115
00:07:05,810 --> 00:07:11,418
a maybe if you tryin right number thirty 2,769

116
00:07:11,418 --> 00:07:15,339
you should get a park unless they've changed

117
00:07:15,339 --> 00:07:18,779
well we just put 33,000

118
00:07:18,779 --> 00:07:22,159
who have changed it on ask is is long integer

119
00:07:22,160 --> 00:07:28,419
don't has changed it into first though we do that

120
00:07:28,418 --> 00:07:33,508
tried again you know sector

121
00:07:33,509 --> 00:07:40,509
yeah larger than the Phil sites field size settings

122
00:07:41,009 --> 00:07:46,509
rock so you to 32767

123
00:07:46,509 --> 00:07:50,110
ira so that's the limit $32767

124
00:07:50,110 --> 00:07:55,259
these at minus thirty thousand seven hundred and 68 something like that

125
00:07:55,259 --> 00:07:58,590
going the other way so that's a different scene in SWTOR long into their

126
00:07:58,589 --> 00:07:59,538
both

127
00:07:59,538 --> 00:08:02,949
they both hold whole numbers to

128
00:08:02,949 --> 00:08:07,860
long integer go above 32768

129
00:08:07,860 --> 00:08:11,038
so when do you see shadows you'd use long integer for

130
00:08:11,038 --> 00:08:15,189
Eyed Peas say for example every time you had a record

131
00:08:15,189 --> 00:08:18,379
you had unique ID causes

132
00:08:18,379 --> 00:08:23,410
very feasible to go but for amount of ID's and use integers for things like

133
00:08:23,410 --> 00:08:26,470
straw quantities and

134
00:08:26,470 --> 00:08:29,539
so there that to I use quite a lot the third 1i use

135
00:08:29,538 --> 00:08:33,899
law from I'm you talk with other ones is double a base is double for

136
00:08:33,899 --> 00:08:37,139
storing fractional value so much change at the double

137
00:08:37,139 --> 00:08:41,610
today she be save it now I can actually turn and 5.5

138
00:08:41,610 --> 00:08:45,759
so yeah I don't honestly my view explain the other ones cuz I only ever use

139
00:08:45,759 --> 00:08:49,629
integer long integer and double singles actually

140
00:08:49,629 --> 00:08:53,179
the same ideas double fractional based or less by these budgets

141
00:08:53,179 --> 00:08:58,429
never tend to use it so that short text long text number

142
00:08:58,429 --> 00:09:04,028
date time do I need to explain a guess what's storing dates and times

143
00:09:04,028 --> 00:09:08,870
dates ok reserve would be careful

144
00:09:08,870 --> 00:09:13,959
shopping list day so this change that DateTime

145
00:09:13,958 --> 00:09:17,278
that they deceive you well we got

146
00:09:17,278 --> 00:09:21,950
I guess if you click in there now we've restoring is it a race to get a nice to

147
00:09:21,950 --> 00:09:25,580
take text books because the store times I'm

148
00:09:25,580 --> 00:09:30,240
in this sort of former here

149
00:09:30,240 --> 00:09:34,700
think served as a day in the time to talk about I want too much

150
00:09:34,700 --> 00:09:38,290
next currency currency

151
00:09:38,289 --> 00:09:43,028
those obviously for storing money a man is actually a

152
00:09:43,028 --> 00:09:46,309
is for love longing to tie for the belief

153
00:09:46,309 --> 00:09:52,199
I'm loan quote me on that and the other at save a currency

154
00:09:52,200 --> 00:09:55,410
a judas I were saying about currency

155
00:09:55,409 --> 00:10:00,528
discovered and 14,000 for 223 yeah what we're saying we're currency is the

156
00:10:00,528 --> 00:10:04,889
are based in the UK so as I've chosen currency will read of

157
00:10:04,889 --> 00:10:10,000
my currency are selected for my windows which is why China pound

158
00:10:10,000 --> 00:10:14,370
if you take this same database don't change anything in but I open up in

159
00:10:14,370 --> 00:10:17,639
United States for example that would immediately change the dollar

160
00:10:17,639 --> 00:10:22,199
where should do but 90 upset at all it does is a park sometimes it doesn't

161
00:10:22,200 --> 00:10:25,459
you know what maybe they've fixed the in 2013 who knows

162
00:10:25,458 --> 00:10:29,609
and so be on that ok it's all to number in a second

163
00:10:29,610 --> 00:10:33,560
yes and no fields are for things that require

164
00:10:33,559 --> 00:10:36,689
yes and no so I'm

165
00:10:36,690 --> 00:10:40,709
Fort so to say that we don't know burma gonna get our

166
00:10:40,708 --> 00:10:44,389
shopping we don't know at the moment if they're even going to have it come when

167
00:10:44,389 --> 00:10:46,039
we get back

168
00:10:46,039 --> 00:10:49,860
we look over the stuff with Paul and we cycle we were able to get the honors and

169
00:10:49,860 --> 00:10:51,440
broke its mottoes

170
00:10:51,440 --> 00:10:56,269
but we were unable to get milk this is what's called a billion datatype

171
00:10:56,269 --> 00:11:00,810
in that got two options Yes No true/false

172
00:11:00,809 --> 00:11:05,159
get to ago and well so we got

173
00:11:05,159 --> 00:11:09,759
an attachment filled he's so you can attach documents

174
00:11:09,759 --> 00:11:13,470
such as a Word documents excel sheets

175
00:11:13,470 --> 00:11:17,300
very useful actually one of the most useful features in excess is trying to

176
00:11:17,299 --> 00:11:19,399
do this is our secret service

177
00:11:19,399 --> 00:11:23,250
really enormous paying yeah this is actually really useful

178
00:11:23,250 --> 00:11:26,809
and the other thing you've got forget to our numbers lookup

179
00:11:26,809 --> 00:11:30,958
was it I am where you couldn't

180
00:11:30,958 --> 00:11:34,268
basically or showing 0 click on it show it to you

181
00:11:34,269 --> 00:11:38,278
I'm you couldn't go to take Ballys another table so

182
00:11:38,278 --> 00:11:41,610
or do that next so let's say

183
00:11:41,610 --> 00:11:47,190
for example table bookings not as nice a table guests

184
00:11:47,190 --> 00:11:52,480
necks and I want guest ID panel first name and last name

185
00:11:52,480 --> 00:11:55,940
so by last name

186
00:11:55,940 --> 00:11:59,760
finish so now of course we're gonna daily she

187
00:11:59,759 --> 00:12:03,490
is for click here is actually looking up

188
00:12:03,490 --> 00:12:07,810
these values in to stay with you

189
00:12:07,809 --> 00:12:11,379
I can see there's

190
00:12:11,379 --> 00:12:14,649
what you actually see is kinda like a bit over red herring

191
00:12:14,649 --> 00:12:18,259
we see the names but the actual value is storing is the

192
00:12:18,259 --> 00:12:22,379
ID value to this comment P days a little bit over

193
00:12:22,379 --> 00:12:25,929
thing about looking up values and other tables

194
00:12:25,929 --> 00:12:29,250
I'm there's nothing wrong with doing it

195
00:12:29,250 --> 00:12:33,528
per se but it's not for this purpose

196
00:12:33,528 --> 00:12:37,720
i've seen. databases where people have done all kinds of crazy stuff in that

197
00:12:37,720 --> 00:12:38,629
this storing

198
00:12:38,629 --> 00:12:42,799
be ID number but they're actually looking up

199
00:12:42,799 --> 00:12:46,179
valuation separate tables in its just

200
00:12:46,179 --> 00:12:49,679
it can cause so many issues so if you gonna use lookup fields no problem but

201
00:12:49,679 --> 00:12:52,939
keep it simple is a simple lookup field historian

202
00:12:52,940 --> 00:12:56,130
the ID value amber show display the names that simple

203
00:12:56,129 --> 00:13:00,700
keep it simple and your on urban issues and

204
00:13:00,700 --> 00:13:04,220
right last but not least it's about the Alta number

205
00:13:04,220 --> 00:13:07,810
dominance road here now is quite common

206
00:13:07,809 --> 00:13:11,028
well was actually pretty you picked this

207
00:13:11,028 --> 00:13:15,159
that every single table you make has an ID column

208
00:13:15,159 --> 00:13:18,889
and we do the tough in it.

209
00:13:18,889 --> 00:13:22,669
go to order number click on he got a design

210
00:13:22,669 --> 00:13:25,679
and click on primary key

211
00:13:25,679 --> 00:13:29,370
if you want to you save that

212
00:13:29,370 --> 00:13:33,889
and go that day she view and the idea of the ID field

213
00:13:33,889 --> 00:13:37,589
is the I'm imagine but my seat

214
00:13:37,589 --> 00:13:40,620
let's have a look at guests

215
00:13:40,620 --> 00:13:44,589
that's even such a good example and you might have two guests

216
00:13:44,589 --> 00:13:47,630
who Hannah Travis how do you know which one is which

217
00:13:47,629 --> 00:13:51,049
well the identifiable by the guest ID

218
00:13:51,049 --> 00:13:54,870
which is an aura number fill anotha number fill just increments by selves if

219
00:13:54,870 --> 00:13:56,889
I had a record down here

220
00:13:56,889 --> 00:14:00,190
you automatically at one that someone to this was a liar

221
00:14:00,190 --> 00:14:03,779
a trucker

222
00:14:03,778 --> 00:14:06,789
no I didn't do anything with the ID photo but also practically added the

223
00:14:06,789 --> 00:14:08,189
number for

224
00:14:08,190 --> 00:14:11,760
so keep increment in as we stand as you wish

225
00:14:11,759 --> 00:14:15,379
and and

226
00:14:15,379 --> 00:14:19,629
yeah that's the ID field are now safely delete this

227
00:14:19,629 --> 00:14:23,519
is prolly worth mentioning no we had another one not sure yet

228
00:14:23,519 --> 00:14:27,078
a.m. Apple's

229
00:14:27,078 --> 00:14:31,458
doesn't actually reuse the number for again they weren't reuse up from for

230
00:14:31,458 --> 00:14:32,799
ever worry use its a

231
00:14:32,799 --> 00:14:36,559
reuse we'll use the next available number which is number five

232
00:14:36,559 --> 00:14:39,638
and that's the ID for this is kind of critical to

233
00:14:39,639 --> 00:14:44,789
pretty much who love the any table you make that you have this ID field as a

234
00:14:44,789 --> 00:14:47,629
way of identifying it don't rely on

235
00:14:47,629 --> 00:14:51,379
names for example I said you can duplicate you cannot rely on is an IT

236
00:14:51,379 --> 00:14:52,379
field

237
00:14:52,379 --> 00:14:55,429
and it is possible to have by the fields as non

238
00:14:55,429 --> 00:15:00,109
alter numbers but if they're generally is a way to make in order to number

239
00:15:00,110 --> 00:15:01,269
field in excess

240
00:15:01,269 --> 00:15:06,820
you know me on the right path to heaven well structured database

241
00:15:06,820 --> 00:15:11,920
so that's a okay Thank You forces video an OC next video where we should be

242
00:15:11,919 --> 00:15:12,719
talking

243
00:15:12,720 --> 00:15:16,670
about relationships in Tables not in your life

244
00:15:16,669 --> 00:15:18,539
excellent thanks for watchin good boy