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.