this lesson is all about relationships
the tables that we created are only good
if they are linked we said that a
customer comes to our store and finds a
book and then makes a purchase
we have our tables that show us what
information we plan to gather and store
in each table now we just need a way to
create a relationship between these
tables here's the database we're
currently working on now I want you to
notice that none of our tables are open
that's because all of the tables must be
closed in order to create relationships
let's start creating relationships by
clicking on the database tools tab and
relationships in the show/hide group the
show table dialog box will appear and
from here you can choose which tables
you want to relate let's go ahead and
add our tables by clicking on each one
and then the Add button
now click close this table should be a
flashback to our tables and fields
diagram I'm going to move the tables
around a bit to put them in the same
order as the ones in our diagram you can
do this by simply left clicking on a
table and dragging it to a new location
you'll notice that the first field under
each one of these tables has a little
key icon that this is the primary key
field the primary key field is the first
field in each table of our database
which we previously said was a unique
identifier of Records in that particular
table for example we could have two
customers with the same name let's say
Mary Jones but because they are being
identified with their customer ID number
the database always knows which customer
is which that is why customer ID is the
primary key it ensures us that each Mary
Jones is tracked separately so in the
customers table the primary key is
customer ID in the orders table the
primary key is order ID and in the books
table its book ID if you look closely at
the orders table you'll notice that
customer ID and book ID are listed but
you don't see a key beside them now
that's because they are considered
foreign keys which just means that these
fields are the primary keys in other
tables now that's enough to make your
head spin so stay with me so we've got
our tables in the order we want them in
and now it's time to create the
relationships we've been talking about
we could use edit relationships from the
ribbon but it's much much easier to just
drag and drop from one table to the
other it's a good thing we called our
IDs customer ID book ID and order ID
earlier this just makes it easier to see
the fields we want to join if we didn't
name them this way we would just have
several fields named ID
now I'll start by joining my customer
IDs so I'll drag the primary key
customer ID from the customers table to
the foreign key customer ID in the
orders table the Edit relationships
dialog box will appear
on the left you see a header that says
table and on the right a header that
says related table in this case the
table is customers and the related table
is orders now this area here is where we
can edit our relationships if needed
below we have other options the only one
that is really important is enforce
referential integrity by checking off
referential integrity we assure that
there is never in order in our orders
table for a customer that doesn't exist
in our customers table and likewise we
would never want to have an order for a
book that doesn't appear in our books
table so clicking enforce referential
integrity forces access to check for
these types of things click create to
establish the relationship by enforcing
referential integrity you establish a
one to many relationship between the
tables now this just means that one
customer may place many orders so in the
customer table the ID shows up one time
but in the orders table we can expect
that the customer may show up many times
because they may order from us again and
again and again I'm going to set up my
other relationship this one is from book
ID to book ID again I'll check and force
referential integrity so I can establish
the one-to-many relationship so now we
can see that we have one book ID for
each book but there can be many book IDs
and in order I hope you got that now
everything looks good
but if I needed to go back and edit my
relationships I could go to edit
relationships or double click on the
links between the tables I want to show
you one more
saying before we wrap up if I move my
tables around and a relationship map
here the relationship remains intact no
matter where I move the tables I'm going
to move them back to their original
spots since this is the easiest way to
see the relationships my relationships
are set so now I can close relationships
tab you'll be prompted to save your
relationships click yes to save our
tables are related
next we'll start adding records also
known as populating the database