Tip:
Highlight text to annotate it
X
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, we're going 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 appears. 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. 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 the unique identifier of records in that particular table.
For example, we could have two customers with the same name, 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 it's 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.
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 ID's 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 appears. 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.
In this area is where we can edit our relationships if needed.
Below we have other options. The only one that is important is Enforce
Referential Integrity.
By checking off Referential Integrity we assure that there is never an order in our Orders
table for a customer that doesn't exist in our Customer's table.
And, likewise we would never want to have an order for a book that doesn't appear in
our Book's 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.
This 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 expect 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 Enforce Referential Integrity to 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
in an order. I hope you got that.
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 thing before we wrap up. If I move my tables around in the Relationship Map,
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 relationship. My relationships are set so now I can close the 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.