1
00:00:00,849 --> 00:00:04,610
I'm local taxes 2013 G two rooms in this video I'm gonna be talking you through

2
00:00:04,610 --> 00:00:05,810
relationships

3
00:00:05,809 --> 00:00:10,018
the computer kind of course so fun to play along for the link below the video

4
00:00:10,019 --> 00:00:13,210
to the Donna sex website and download access 2013

5
00:00:13,210 --> 00:00:16,260
the bees are four and its open at 10

6
00:00:16,260 --> 00:00:20,500
in 2013 alright so

7
00:00:20,500 --> 00:00:23,730
if you are so lost video you'll know

8
00:00:23,730 --> 00:00:27,849
that we had I'm we started a table

9
00:00:27,849 --> 00:00:32,710
stay with songs I'm and it looks good suspiciously similar to this one

10
00:00:32,710 --> 00:00:35,920
I'm except the

11
00:00:35,920 --> 00:00:39,329
vision table we had was just one table and

12
00:00:39,329 --> 00:00:43,020
every field yet to be written in and its causes a problem with

13
00:00:43,020 --> 00:00:46,760
albums an artist because European data so what we did is we broke them down

14
00:00:46,759 --> 00:00:50,659
in two separate entities and diluted have a magic trick

15
00:00:50,659 --> 00:00:53,859
lookup fields and in fact this

16
00:00:53,859 --> 00:00:57,689
food here doesn't store such a purpose and let it be stores

17
00:00:57,689 --> 00:01:00,909
the numbers she stores 1&2

18
00:01:00,909 --> 00:01:04,158
but it's looking up tonight and

19
00:01:04,159 --> 00:01:08,760
the good thing about this is if I change why it's something to sergeant purpose

20
00:01:08,760 --> 00:01:13,228
I'm ABC and then go to back here

21
00:01:13,228 --> 00:01:17,368
uses added it to all the sergeant Pepper's records in one go

22
00:01:17,368 --> 00:01:22,170
so that was the concept of normalization

23
00:01:22,170 --> 00:01:25,400
and that's great but small problem

24
00:01:25,400 --> 00:01:30,960
I'm this close a place let's take a song

25
00:01:30,959 --> 00:01:35,179
such as to

26
00:01:35,180 --> 00:01:41,470
us which is firm let it be and per month got

27
00:01:41,469 --> 00:01:45,009
preventing this song is the according to

28
00:01:45,009 --> 00:01:51,140
Wikipedia a.m. two verses written by then and

29
00:01:51,140 --> 00:01:55,509
and McCartney so however internet the

30
00:01:55,509 --> 00:01:58,569
and some McCartney and then to have us the game

31
00:01:58,569 --> 00:02:03,859
and then into London w/o might do that but there may be back to the original

32
00:02:03,859 --> 00:02:05,118
problem from the last video

33
00:02:05,118 --> 00:02:09,750
will be repeating data so we're playing the song okay what else can we do what

34
00:02:09,750 --> 00:02:11,030
about we go to table

35
00:02:11,030 --> 00:02:16,090
song and so no time songs we got a table artists and we enter

36
00:02:16,090 --> 00:02:21,340
a new artists code London

37
00:02:21,340 --> 00:02:24,370
work Carney

38
00:02:24,370 --> 00:02:28,039
which is my favorite way

39
00:02:28,039 --> 00:02:31,889
to get around this problem so bad I'm

40
00:02:31,889 --> 00:02:35,359
original but bass the from got here

41
00:02:35,360 --> 00:02:38,590
is the same problem is I just described which is where repeating day to get a

42
00:02:38,590 --> 00:02:39,009
good

43
00:02:39,009 --> 00:02:43,969
were paying Lennon and European McCartney so this is a pretty bad thing

44
00:02:43,969 --> 00:02:45,219
today so we're good at on

45
00:02:45,219 --> 00:02:49,609
so what do we do than well let's go back to the concept of a table

46
00:02:49,610 --> 00:02:53,519
table store this data

47
00:02:53,519 --> 00:02:58,819
I'm and we've got three tables 2&3 lisette data

48
00:02:58,818 --> 00:03:02,250
but the problem we've got is

49
00:03:02,250 --> 00:03:06,050
the relationships we've got between tables already sufficient to handle the

50
00:03:06,050 --> 00:03:07,780
data model now we've introduced

51
00:03:07,780 --> 00:03:11,789
a new concept because for example artists

52
00:03:11,789 --> 00:03:16,359
pass this exam from using the relation we have that one artist

53
00:03:16,360 --> 00:03:20,269
canopy many times in Table songs

54
00:03:20,269 --> 00:03:26,219
but one of his cannot appear many times in one record in Table songs is only one

55
00:03:26,219 --> 00:03:30,080
artist per record and we need to have the ability

56
00:03:30,080 --> 00:03:33,269
to have more than one artist a record in table saws

57
00:03:33,269 --> 00:03:38,209
I'm but we don't duplicate data table losses so we don't duplicate data in

58
00:03:38,209 --> 00:03:39,259
Table some so

59
00:03:39,259 --> 00:03:42,548
that already do anything for us what we do

60
00:03:42,549 --> 00:03:47,099
where the Arts up as we have to actually create an into major she table between

61
00:03:47,098 --> 00:03:47,939
the two

62
00:03:47,939 --> 00:03:51,500
tables which we can call something like table

63
00:03:51,500 --> 00:03:54,830
with junction table table some artists but if you wanna call it

64
00:03:54,830 --> 00:03:58,120
I and robson

65
00:03:58,120 --> 00:04:02,280
explain anymore just do it so facing wanna do structure care delete this

66
00:04:02,280 --> 00:04:03,110
relationship

67
00:04:03,110 --> 00:04:06,400
so now these tables are not related

68
00:04:06,400 --> 00:04:10,670
in any meaningful way annexing what you do is create the table in the middle

69
00:04:10,669 --> 00:04:13,878
got a table designed I'm

70
00:04:13,878 --> 00:04:17,579
and grew what we want is you want the song ID

71
00:04:17,579 --> 00:04:21,608
FK it's not a numbers just a number

72
00:04:21,608 --> 00:04:26,349
and we want the hearths

73
00:04:26,350 --> 00:04:29,689
fist ID FK

74
00:04:29,689 --> 00:04:32,759
again a number

75
00:04:32,759 --> 00:04:36,379
I'm gonna save this cool it table who

76
00:04:36,379 --> 00:04:43,379
junction and I don't want other primary care at the moment

77
00:04:44,439 --> 00:04:48,000
and the way the table junction works I'm

78
00:04:48,000 --> 00:04:51,939
I simply could actually do a lookup thoughts quickly because at the moment

79
00:04:51,939 --> 00:04:55,310
that this number so you can just enter numbers in them which is fine

80
00:04:55,310 --> 00:04:58,519
but song number one

81
00:04:58,519 --> 00:05:01,939
we'll go with artist number two so

82
00:05:01,939 --> 00:05:05,509
it end up with a situation someone move artistic

83
00:05:05,509 --> 00:05:09,889
I'm this is change it to lookup field so we can actually see the day so

84
00:05:09,889 --> 00:05:14,060
with the move so so might the lookup

85
00:05:14,060 --> 00:05:18,079
threatened trolls comic books on bro source

86
00:05:18,079 --> 00:05:21,430
tae-woo songs and

87
00:05:21,430 --> 00:05:24,538
pollen count to where 0 comma

88
00:05:24,538 --> 00:05:29,569
for Houston that with that setup up by then there's nothing to update

89
00:05:29,569 --> 00:05:33,019
and an artist I December right it said it s

90
00:05:33,019 --> 00:05:36,810
text books which aims to a comic books column

91
00:05:36,810 --> 00:05:40,168
row source mystery for column count now a

92
00:05:40,168 --> 00:05:43,430
roasts hooves is stable artists

93
00:05:43,430 --> 00:05:46,918
compound to

94
00:05:46,918 --> 00:05:50,519
compresses no whips 0

95
00:05:50,519 --> 00:05:54,000
and for an update now even though

96
00:05:54,000 --> 00:05:58,379
I think that they save it day she view

97
00:05:58,379 --> 00:06:01,600
a now please please please understand the

98
00:06:01,600 --> 00:06:04,919
this table table ducks in stores numbers

99
00:06:04,918 --> 00:06:08,448
the where they are

100
00:06:08,449 --> 00:06:13,009
we've done is done nobel metric and we are looking up

101
00:06:13,009 --> 00:06:16,340
the song firms other papers may display hey I just

102
00:06:16,339 --> 00:06:19,489
done this so that we can I'm

103
00:06:19,490 --> 00:06:22,400
easily see what information we're dealing with if I wasn't doing it

104
00:06:22,399 --> 00:06:22,959
writing

105
00:06:22,959 --> 00:06:26,099
video I wouldn't do that so I'll just leave it as is

106
00:06:26,100 --> 00:06:30,370
i right so the idea is the week is a that's other purposes Rand Paul

107
00:06:30,370 --> 00:06:31,050
McCartney

108
00:06:31,050 --> 00:06:36,009
and we can say that we've a little help from my friends written by starr

109
00:06:36,009 --> 00:06:40,610
but crucially if we go to to have us we can say that to a bus was written by

110
00:06:40,610 --> 00:06:42,050
lebanon

111
00:06:42,050 --> 00:06:46,288
and to have us was written by McCartney

112
00:06:46,288 --> 00:06:49,759
coming to disclose all these

113
00:06:49,759 --> 00:06:53,770
closer thing and open relationships

114
00:06:53,769 --> 00:06:57,799
and we're gonna have to do is good at sea before we can do that we got to lead

115
00:06:57,800 --> 00:06:59,199
a

116
00:06:59,199 --> 00:07:04,000
too late artists for anti-war songs

117
00:07:04,000 --> 00:07:07,240
and go to relationships again

118
00:07:07,240 --> 00:07:11,360
show table and they were junction truck then the meadow

119
00:07:11,360 --> 00:07:15,389
okay we're gonna try n relay and I'm

120
00:07:15,389 --> 00:07:18,840
so my date so I D from songs in our society from here

121
00:07:18,839 --> 00:07:22,429
now what's going to happen if we do your

122
00:07:22,430 --> 00:07:27,728
gotta feed in but there are

123
00:07:27,728 --> 00:07:31,068
yeah no unique index found for the reference feel loved primary

124
00:07:31,069 --> 00:07:36,949
to table I was kilometer artist names for

125
00:07:36,949 --> 00:07:40,329
so I'm an artist ID so that's great site

126
00:07:40,329 --> 00:07:44,459
we've before Smith French what a gritty which is great which means that we can't

127
00:07:44,459 --> 00:07:48,019
her any we can choose a song that doesn't exist in

128
00:07:48,019 --> 00:07:51,620
table songs which card to use an artist whose does exist in Table artis

129
00:07:51,620 --> 00:07:55,620
we've got one more problem left remember the whole issue we had

130
00:07:55,620 --> 00:07:59,370
was not repeating data so if that's the case

131
00:07:59,370 --> 00:08:03,610
we've got sergeant Pepper's McCartney down here

132
00:08:03,610 --> 00:08:07,288
I should be able to do this because already got this

133
00:08:07,288 --> 00:08:10,628
combination updates on new twice and I'm

134
00:08:10,629 --> 00:08:15,939
action now repeating data first to like that so what do we do

135
00:08:15,939 --> 00:08:20,240
well if we go to I'm

136
00:08:20,240 --> 00:08:24,780
designed you and we think we're okay just for primary care in

137
00:08:24,779 --> 00:08:30,189
song I T but all that will do is mean we can't repeat songs which ex be the worst

138
00:08:30,189 --> 00:08:31,529
thing cuz you already are

139
00:08:31,529 --> 00:08:35,028
I believe were speaking to us

140
00:08:35,028 --> 00:08:38,200
31 X about it either doctoral

141
00:08:38,200 --> 00:08:42,000
prove it to for change request that they were not successful

142
00:08:42,000 --> 00:08:45,070
cuzzo great trip values okay

143
00:08:45,070 --> 00:08:48,750
so we get ready for me he that's already well

144
00:08:48,750 --> 00:08:52,528
in order to protect the combination values you have to Tombo for these into

145
00:08:52,528 --> 00:08:53,879
a prior week a

146
00:08:53,879 --> 00:08:58,610
to holla by for them click primary key click Save

147
00:08:58,610 --> 00:09:02,209
and now we got a tissue were actually tearing

148
00:09:02,208 --> 00:09:05,838
is by making both from prior cases were saying to the table the

149
00:09:05,839 --> 00:09:10,160
you can repeat songs as many times as one you copy of this as many times as

150
00:09:10,159 --> 00:09:10,639
one

151
00:09:10,639 --> 00:09:15,028
we cannot do is repeat the combination so for example such a purpose

152
00:09:15,028 --> 00:09:19,889
already appears with McCartney so far try and repeat that now

153
00:09:19,889 --> 00:09:22,980
changes requested they were not successful because they work rate to get

154
00:09:22,980 --> 00:09:24,519
bodies

155
00:09:24,519 --> 00:09:30,500
i cud so let's say that close to 30 database to say one last look

156
00:09:30,500 --> 00:09:34,399
what we did well we created a many to many relationship

157
00:09:34,399 --> 00:09:37,850
and we did that because the relationship

158
00:09:37,850 --> 00:09:42,040
we had previously between songs and artists was not sufficient for a data

159
00:09:42,039 --> 00:09:43,929
model because we needed

160
00:09:43,929 --> 00:09:47,109
to hold more than one artist a

161
00:09:47,110 --> 00:09:50,870
in a single recorded table soaks we could

162
00:09:50,870 --> 00:09:56,459
a have more than one artist's in Table songs also the same eyes repeal just say

163
00:09:56,458 --> 00:10:00,169
we couldn't do is we couldn't have more than one artist but record

164
00:10:00,169 --> 00:10:04,559
so yet to do so critical to many to many relationship in a many to many

165
00:10:04,559 --> 00:10:05,799
relationship

166
00:10:05,799 --> 00:10:10,649
missus of an intermediary table and actually to one to many relationship so

167
00:10:10,649 --> 00:10:11,899
we've got one to many relationship

168
00:10:11,899 --> 00:10:15,690
it because theoretically artist ID

169
00:10:15,690 --> 00:10:19,430
yes correct artist this artist canopy many times in this table

170
00:10:19,429 --> 00:10:23,889
and some canopy many times in table junction

171
00:10:23,889 --> 00:10:28,490
and thats to one to many relationship so when you put them in the intermediate

172
00:10:28,490 --> 00:10:29,860
able

173
00:10:29,860 --> 00:10:33,289
give them both primary Kasey Kahne trippy combinations

174
00:10:33,289 --> 00:10:36,569
you create a many to many relationship I'm

175
00:10:36,570 --> 00:10:39,980
so a standard a video its

176
00:10:39,980 --> 00:10:43,350
little bit confusing many to many relationships for

177
00:10:43,350 --> 00:10:46,579
program have who levels and abilities

178
00:10:46,578 --> 00:10:49,689
see money to watch it again or check up on Cooper

179
00:10:49,690 --> 00:10:54,240
conceptus the ok sever one to many relationship doesn't see the state motto

180
00:10:54,240 --> 00:10:57,589
believe that relationship drop table in the middle when you create two

181
00:10:57,589 --> 00:11:02,070
one too many relationships and Parma case so fix what to do and see you next

182
00:11:02,070 --> 00:11:02,320
time