Welcome back to our course on Access 2016.
In the preceding section we started looking at relationships.
And in order to set up relationships for the Esprit de Tour database I created a new table
containing the types of trip that Esprit de Tour run and a link record, a record that’s
going to link each trip with the types that that trip has.
And in that way we’re building up indirectly a relationship between trip and type.
Using the link record and in fact the relations we set up are from Trip to TripType and from
Type to TripType.
So in order to do this we actually set up two relationships.
The first relationship we’re going to set up is Trip to TripType.
And the relationship is based on the ID in the trip table.
So each record in the trip table, each trip has a unique ID.
That’s the primary key.
And records in the trip type table for that trip will have that same trip ID in the TripID
field in the records in the trip type table.
Now in order to set up the relationship what I do is to drag ID in the trip table and drop
it onto TripID in the trip type table.
And that brings up the Edit Relationships dialog where I can actually set up the details
of the relationship between these two tables.
Now the first thing I do is to make sure that I’ve got the right tables and the right
fields.
So on the left, Table Query, Trip, ID, yep that’s the primary ID of the trip table
records.
That’s fine.
And then TripType, TripID.
That is the long integer field that I’ve set up to match the ID in the trip table.
So I’ve definitely got the right tables and fields selected.
The next important decision is do I enforce referential integrity?
Now on this occasion I’m going to say yes but I’m going to explain to you now what
referential integrity is.
Let’s imagine a situation where I’ve created a trip and corresponding to that trip I’ve
say created four trip type records.
So there’s a trip type of beach and a trip type of family and a trip type of adventure
and a trip type of escorted.
And each of those has the appropriate type, beach, etcetera, and the trip ID for my selected
trip.
What would happen if I deleted that trip from the trip table?
I’d finish up with four records in the trip type table that had no links at the other
end of this relationship.
They’d be sort of orphans.
So those four records would be referring to a trip that no longer exists.
Making sure that that doesn’t happen is called Enforcing Referential Integrity.
And once I’ve checked that if I try to delete a trip what Access 2016 will do is it will
make sure that there are no outstanding trip type records that use that trip.
So it won’t let me break the database integrity in the way that I just described.
Now there are a couple of other options.
Let me talk about one of them.
A couple of options down below Enforce Referential Integrity, Cascade Delete Related Records.
If I check this option if I deleted a trip record in the situation I just described Access
would automatically cascade delete the trip type records for that trip.
Now that of course would mean that the database wasn’t broken because those four records
would automatically get deleted.
So I could enforce referential integrity by checking that box and that would make sure
that nothing got broken by a delete.
But for the moment I’m actually going to uncheck this box, but I am going to say enforce
referential integrity.
The next piece of information here is the relationship type one to many.
And what that means in this situation is that one trip can have many trip types.
And that’s exactly the situation that I want.
So let me create that relationship and there it is.
One trip, many trip types.
Now let’s set up the second relationship.
It’s from the type in the type table.
Note that although this is a short text field it is the primary key of the type table.
And it goes to type in the trip type table.
And that sets up a different relationship.
Now we have the same questions.
So again I just check I’ve got the right fields.
So primary key in the type table is Type.
The field in the trip type table that I’m setting the relationship up with is Type so
that will be a foreign key in the trip type table.
I’m once again going to enforce referential integrity because if I were to delete a type
in the type table I would want to make sure that there weren’t any entries in the trip
type table that were still using that type.
Now once again I’m not going to check cascade delete here.
I’m going to leave that unchecked.
A consequence of this is that if I did try to delete a type that was in use Access wouldn’t
let me do it.
I’d have to get rid of all the entries in the trip type table that use that type first
before I could delete the type itself.
So that’s the alternative to cascade delete.
Now once again it’s a one to many relationship because one entry in the type table, one type,
can have many corresponding records in the trip type table.
So I click on Create and I have another one to many relationship.
The infinity symbol at the end there indicates many.
So what we’ve done here in creating these two relationships is to put in place probably
one of the most fundamental building blocks in a relational database.
And although in many ways when you first look at relationships if you’ve not seen this
before it may all seem rather baffling.
When you do understand this, and hopefully you do already, it really is one of the most
fundamental aspects of any relational database.
Certainly a fundamental aspect of Access which once you’ve got to grips with it, once you
understand what’s going on it really makes an awful lot of other things in relational
databases and Access much easier to understand.
You will also see why we don’t use things like multi-value fields because multi-value
fields can cause all sorts of problems.
Now conceptually at first what we’ve done here does take a little bit of getting used
to, but once you are used to it you’ll find it makes many other things not only much easier
but much more flexible as well.
Now one very big downside of what we’ve done so far is that visualizing now the trip
database in the light of what we’ve just done can be a little bit tricky.
And in many ways what we really need now is a couple of nice forms to show us how all
of this hangs together.
You’ve got a couple of sections to wait before we get to that point though.
And the next thing I’m going to do in this section is to actually set some data up to
reflect what I’ve just done.
And then in the next section I’m going to set you an exercise to set up an equivalent
set of relationships within the movie rental database.
So let me first of all close the relationships window, making sure that I save the changes.
And then let me just refresh my memory on the trip table.
Open it up.
There are my trips.
The IDs go from 4 to 15.
Then let me open up my type table, the one I created just recently.
And now let me open up my trip type table.
My trip type table is currently empty.
Now I hope you can cast your mind back to quite a while ago now.
I showed this first example of a trip, the GCA trip that’s currently in our trip table.
And I mentioned there the type and there are five values of type for this trip.
Family, escorted, group, activity, landscapes.
And I said we’d skip that at that time, which we did.
Well now is the time that we’re coming back and we’re going to set those up.
Now back to my trip table.
Note that I can switch between these open documents.
They’re all three tables.
But all I need to do is to click on the tab and the GCA trip has an ID of four.
So all I need to do is go to TripType, type in an ID of 4 and a type of, well one of them
is family.
That should be okay.
Press the down arrow.
Yep, we’re fine.
Now this is another one for 4.
This one is, that’s fine as well.
Let’s make a mistake.
Let’s say I’m going to try to set up for TripID 3 and that’s going to be Group.
You cannot add or change a record because a related record is required in the table
trip.
So it knows that there is no trip three.
Of course there used to be but there isn’t any longer.
So that should be four.
And similarly if I say misspelt the type it would tell me there was an error there as
well.
Okay let me just put in the rest of those.
Now at this point you’re probably looking at that and thinking, “My goodness, that
is a really slow way of setting all of this up.”
And I think anybody who doesn’t think that’s a slow way of setting things up must be much
more patient than I am because of course in the longer term we’re certainly not going
to be setting up this information like that.
The reason that I’m doing it like this here is just to emphasize exactly what happens
with these relationships and these link records.
Note the ID of the TripType records.
1, 2, 3, 4, 5.
We will rarely if ever use those but they’re there to uniquely identify TripType records.
And it may be that at some point later on we may need those IDs.
What’s important is the TripID.
So these TripType records are all for trip four and there are five of them.
So we know that trip four has five types associated with it.
So let’s see referential integrity at work shall we.
Let’s go to our trip table, let’s go to trip four, right click, Delete Record.
Dangerous this.
The record cannot be deleted or changed because the table TripType includes related records.
So I cannot delete that until I get rid of the TripType records.
If I had enabled cascade delete it would let me do it, but I haven’t so it won’t.
Now as I said just now I realize that this is a pretty manual way of doing a job like
this and you’re maybe thinking of databases with thousands of records on them and thinking,
“My goodness am I going to sit down and type all that in?”
And the answer of course is no.
And you almost certainly wouldn’t expect your users to.
So we do need to start to use forms to make all this a lot easier, quicker and safer.
But the first thing we’re going to do in the next section is you are going to do an
exercise similar to what’s been covered in this section and the preceding one but
you’re going to set up some relationships in the movie rental database.
That’s what we’re covering next.
I’ll see you then.