I'm local taxes 2013 G two rooms in this video I'm gonna be talking you through
relationships
the computer kind of course so fun to play along for the link below the video
to the Donna sex website and download access 2013
the bees are four and its open at 10
in 2013 alright so
if you are so lost video you'll know
that we had I'm we started a table
stay with songs I'm and it looks good suspiciously similar to this one
I'm except the
vision table we had was just one table and
every field yet to be written in and its causes a problem with
albums an artist because European data so what we did is we broke them down
in two separate entities and diluted have a magic trick
lookup fields and in fact this
food here doesn't store such a purpose and let it be stores
the numbers she stores 1&2
but it's looking up tonight and
the good thing about this is if I change why it's something to sergeant purpose
I'm ABC and then go to back here
uses added it to all the sergeant Pepper's records in one go
so that was the concept of normalization
and that's great but small problem
I'm this close a place let's take a song
such as to
us which is firm let it be and per month got
preventing this song is the according to
Wikipedia a.m. two verses written by then and
and McCartney so however internet the
and some McCartney and then to have us the game
and then into London w/o might do that but there may be back to the original
problem from the last video
will be repeating data so we're playing the song okay what else can we do what
about we go to table
song and so no time songs we got a table artists and we enter
a new artists code London
work Carney
which is my favorite way
to get around this problem so bad I'm
original but bass the from got here
is the same problem is I just described which is where repeating day to get a
good
were paying Lennon and European McCartney so this is a pretty bad thing
today so we're good at on
so what do we do than well let's go back to the concept of a table
table store this data
I'm and we've got three tables 2&3 lisette data
but the problem we've got is
the relationships we've got between tables already sufficient to handle the
data model now we've introduced
a new concept because for example artists
pass this exam from using the relation we have that one artist
canopy many times in Table songs
but one of his cannot appear many times in one record in Table songs is only one
artist per record and we need to have the ability
to have more than one artist a record in table saws
I'm but we don't duplicate data table losses so we don't duplicate data in
Table some so
that already do anything for us what we do
where the Arts up as we have to actually create an into major she table between
the two
tables which we can call something like table
with junction table table some artists but if you wanna call it
I and robson
explain anymore just do it so facing wanna do structure care delete this
relationship
so now these tables are not related
in any meaningful way annexing what you do is create the table in the middle
got a table designed I'm
and grew what we want is you want the song ID
FK it's not a numbers just a number
and we want the hearths
fist ID FK
again a number
I'm gonna save this cool it table who
junction and I don't want other primary care at the moment
and the way the table junction works I'm
I simply could actually do a lookup thoughts quickly because at the moment
that this number so you can just enter numbers in them which is fine
but song number one
we'll go with artist number two so
it end up with a situation someone move artistic
I'm this is change it to lookup field so we can actually see the day so
with the move so so might the lookup
threatened trolls comic books on bro source
tae-woo songs and
pollen count to where 0 comma
for Houston that with that setup up by then there's nothing to update
and an artist I December right it said it s
text books which aims to a comic books column
row source mystery for column count now a
roasts hooves is stable artists
compound to
compresses no whips 0
and for an update now even though
I think that they save it day she view
a now please please please understand the
this table table ducks in stores numbers
the where they are
we've done is done nobel metric and we are looking up
the song firms other papers may display hey I just
done this so that we can I'm
easily see what information we're dealing with if I wasn't doing it
writing
video I wouldn't do that so I'll just leave it as is
i right so the idea is the week is a that's other purposes Rand Paul
McCartney
and we can say that we've a little help from my friends written by starr
but crucially if we go to to have us we can say that to a bus was written by
lebanon
and to have us was written by McCartney
coming to disclose all these
closer thing and open relationships
and we're gonna have to do is good at sea before we can do that we got to lead
a
too late artists for anti-war songs
and go to relationships again
show table and they were junction truck then the meadow
okay we're gonna try n relay and I'm
so my date so I D from songs in our society from here
now what's going to happen if we do your
gotta feed in but there are
yeah no unique index found for the reference feel loved primary
to table I was kilometer artist names for
so I'm an artist ID so that's great site
we've before Smith French what a gritty which is great which means that we can't
her any we can choose a song that doesn't exist in
table songs which card to use an artist whose does exist in Table artis
we've got one more problem left remember the whole issue we had
was not repeating data so if that's the case
we've got sergeant Pepper's McCartney down here
I should be able to do this because already got this
combination updates on new twice and I'm
action now repeating data first to like that so what do we do
well if we go to I'm
designed you and we think we're okay just for primary care in
song I T but all that will do is mean we can't repeat songs which ex be the worst
thing cuz you already are
I believe were speaking to us
31 X about it either doctoral
prove it to for change request that they were not successful
cuzzo great trip values okay
so we get ready for me he that's already well
in order to protect the combination values you have to Tombo for these into
a prior week a
to holla by for them click primary key click Save
and now we got a tissue were actually tearing
is by making both from prior cases were saying to the table the
you can repeat songs as many times as one you copy of this as many times as
one
we cannot do is repeat the combination so for example such a purpose
already appears with McCartney so far try and repeat that now
changes requested they were not successful because they work rate to get
bodies
i cud so let's say that close to 30 database to say one last look
what we did well we created a many to many relationship
and we did that because the relationship
we had previously between songs and artists was not sufficient for a data
model because we needed
to hold more than one artist a
in a single recorded table soaks we could
a have more than one artist's in Table songs also the same eyes repeal just say
we couldn't do is we couldn't have more than one artist but record
so yet to do so critical to many to many relationship in a many to many
relationship
missus of an intermediary table and actually to one to many relationship so
we've got one to many relationship
it because theoretically artist ID
yes correct artist this artist canopy many times in this table
and some canopy many times in table junction
and thats to one to many relationship so when you put them in the intermediate
able
give them both primary Kasey Kahne trippy combinations
you create a many to many relationship I'm
so a standard a video its
little bit confusing many to many relationships for
program have who levels and abilities
see money to watch it again or check up on Cooper
conceptus the ok sever one to many relationship doesn't see the state motto
believe that relationship drop table in the middle when you create two
one too many relationships and Parma case so fix what to do and see you next
time