alone working taxes 2013 she two rooms
this video will be talking you through indexes
what they are and how you can use them so gonna play along follow the link to
the video to download section website and on taxes 2013 TV 05
a and then its opener
way up okay so if you're so last couple videos
I we normalized a table
which is we broke it down into discrete data structures
I think the technical definition organization is
and and we created
originally this wasn't a
and we had three tables songs albums and artists there's a link between artists
and songs
and then we realized that wasn't quite sufficient for the data model because
some songs
can be sung by more than one artist so we have to drop
a table in the middle and recreate to
want many relationships and the end result is
a many to many relationship so that's all well and good
but I something that can happen
a.m. is you can sometimes end up in a situation where
even to primary keys on enough
for you wanna do and a good example here are close this if we go to
or type songs military muscle me too
junction if we take the song
the sergeant pepper's Lonely Hearts Club Band which is written by McCartney
early the this song
is from the album sergeant Pepper's but it also appeared on the Blue Album
which is released some use of this is a compilation album
so what that means is that
we would tax need to have more than one record that was contained
information about the song so you're such a pepperoni October McCartney
I'm and the album sergeant pepper's and then you can have another record which
was set
sergeant pepper's Lonely Hearts Club Band McCartney and the Blue Album
so you actually know using three fields
to back to see a.m.
identify for the record key point being
that you can have this name the song the artist and the album
but that they too should rip 80 so
how do we do that we
do a similar thing like here where we create we at
are problems into here then burn extra primera Cena
well no that wouldn't work some wish I had to do it so first things first
let bus can leave that how is bill S
go to to production and it's good to design view
and stop they should be a primary key
are you insert a row
we're gonna colder junction
Friday so we're gonna give this table now a standard
kinda primary key setup where you've got
a to the standard ID which is an alternate number which in increments
we save that I'm a call this on the primary key can go today she
you we get there now please notice
the the relationships that intact
which is good organization in the crate
an extra road for an extra field for the
albums scope it back up in design view record aswan
album ID SK
and it's going to be number and we're gonna credit lookup field as well so we
know what we're dealing with
so como books a.m. row source
be warm table opens column count
to column widths 0 and for
should do it and forget the day she view
we can't use of purpose
was sergeant purpose
to things let it be cases safer
close to go back to the most whose relationships
and now lets track our mighty to this okay
so we for
done what we wanted to to
and let's go to table albums and miss at
the blue album
and if you go back table junction
we can now choose solder purposes mccartney
and the Blue Album okay
so this is how you would get it so that you can base
on three keys bar from we've got
is what happens if we to this
and we choose to same values again
okay doesn't restrict us and that's a major problem
because this goes back to the original vision problems
what do we do about repeating data we don't want repeating data so you set the
table a pair we wanna
were saying that we won I we are mine songs and artists are now been repeating
we don't mind combinations of songs and artists were paying
for artists and albums four songs and I haven't what we called have
is combination of a song and artist and album repeating
I'm and this is actually is that this can be an issue and is an issue if they
order sometimes in the
you have a table you need US three sometimes four fields to
represent key so what we do
as you drop into design view coming up here me
to cut in taxes I'm
and in books that happens indexes going to function like a
per locker most for primary case over click here
and type in Maine index
K and down here
choose unique yes
and weak choose so my day
call to start the now a mighty
and what we're saying is that you can't the what we're saying is that you making
a
combination of the song artist and album which can be repeated
keep when if you click in here is a song you know is this box that may come up
where you can choose the type of index and then you click an artist and album
that goes away
and that's gonna go away until we put something else in this
the index name pair so the
a.m. indexes thought about his intelligence know if you have main index
a
and to you put something else in Maine in know in this column
its gonna see in these to be the same index so what that mean
well that no means if we saved a got it a she
and we tryin reenter combination already exists
you get this change requests able not successful because I would create
duplicate values
okay so the press escape order
so that's an index I'm
key point is you use an index to create
a.m. a car primary key where it's
based on more than one filled specifically more than two fields in a
you can't use a many to many relationship
is very useful and it does
does happen from time to time
so thanks for watchin see nexidia