Understanding Access 2013 Relationships between Tables – Part 1

The most important goal of any database design is to ensure that there is absolutely no data redundancy. The best way to achieve this is by creating relationships between different tables, which have some common fields. Access 2013 offers you an easy way of doing this. All you have to do is select tables, drag fields, and enforce referential integrity (if required). The type of relationships and the overall structure of the database including the connections are diagrammatically illustrated in relationships design view. All you have to do is to make sure that the data types of foreign keys match.

Watch the free video here, transcripts for the entire video follow:

Need effective Access 2013 training? Get our 9-hour of Microsoft Access 2013 training – click here.

Video transcripts:

Hello again and welcome back to our course on Access 2013.  In the previous section we looked at setting up relationships between tables in an Access 2013 database and in particular we looked at recording the genre for a movie in our movie database.  In this section we’re going to look at setting up the relationship between a movie and the actors in it.

Now to some extent this is a similar exercise to the one where we setup the genre but there are a couple of important differences.  And one of them is that when we define the actors that appear in a movie we define some other information, notably for example their role in the movie.  This is not the actors name but the name of the character played by the actor.  So some of this is pretty much the same procedure.  We’re going to create a new table.  So we can click on Create.  We’ll go into table design.  We’ll start off with an ID field.  Note that when you go into table design you don’t get one of these by default but we are going to have one.  So you can define it yourself if it’s not all ready there.  ID defined as AutoNumber.  The next thing we’re going to want will be the movie.  So we put in here movie.  Now we know that the movie is the foreign key pointing to the primary key in the movie table so we know that this needs to be a long integer.  So type is number, check we get long integer down there which we do by default, that’s fine.  And the next thing is the actor.  Basically for the actor table the actor ID is also an AutoNumber field.  So again this will need to be a number and a long integer so that’s fine.  But now I’m going to add an additional field for this one and this field for the moment we’re just going to put in the role, the role of the actor in the movie.  And this will be short text, normally the name of the character.  So I’m just about ready to save that.  Note that if I’ve manually added an AutoNumbered ID field to act as the unique key I need to mark it as the primary key.  So let me just click there, click on primary key, and then in order to save it with the name that I want if I right click on the tab, table 1 in this case, and click on Save.  Type in my table name MovieActor, click on OK, and then I’m going to close that.  And let’s now setup the relationship.

So we go to database tools, click on relationships, that’s the diagram that we drew before.  Now we need to show another table so we click on Show table.  We want to show actor and we want to show MovieActor.  Close again.  Now you can arrange these boxes representing these tables in any way you like.  You just drag them round by their headers.  I normally try to arrange them in such a way that they’re easy to read through the relationships.  You could put, for instance, the movie table on here twice if you wanted to.  There’s nothing to stop you having the same table several times.  But while this database is not yet particularly complicated let’s just stick with one movie table and what we really need to do now is to do the link from movie to MovieActor which will be movie ID to movie, referential integrity yes, click on Create.  And then the link from actor, actor ID to actor there, enforce referential integrity, Create.  So we’ve now got our next relationship defined.

Now we’re going to enter some data now relating to this new relationship, but let me just point something out to you about this now, one way of reading this diagram that I think helps.  If you look at the actor table down here and the line to MovieActor then you see that for one actor, so for each actor there are many movie actor records.  So an actor can be in many of these MovieActor records.  And for each MovieActor record for that actor it represents one of the movies that they’re in.  So an actor has well infinity movies.  What it really means is that an actor has many movies.  But this end when you look at it from the movie table says a movie table has many actors.  So one movie many actors, one actor many movies and that’s how it works.

So let’s close the relationships diagram, save the changes, and let’s go to the actor table.  Let’s just open it up, find an actor, Will Smith is actor number six, and let’s setup a MovieActor record.  Open it up, movie number we had before was six for Independence Day, actor number by coincidence is actor number six and the role played was Captain, I’ll just make that column a little bit wider so you can see it all, and there we have the first actor in a movie and the role that they played in that movie.  So let me just do one more example of that.  Actor, actor Ingrid Bergman, that’s actor number four.  Let’s go back into movies, Casablanca’s number five, MovieActor, movie number five, actor number four, and the role is Ilsa Lund.  That’s the role that Ingrid Bergman played in Casablanca.

Now hopefully from that you get the general idea of how these relationships work, but you’re probably also looking at this and thinking that’s actually quite difficult to keep track of isn’t it?  Because you’ve got to worry about all those numbers.  Well in this basic form that’s true but as we will find out in a little while and particularly when we start to look at forms in detail there are various ways to make this whole process much easier.  But the important thing to know is to know what’s going on under the hood or as you say in the U.K. under the bonnet.  It is important to understand how this linkage is working but there are ways to make it all an awful lot easier to use and that’s what we’re going to look at a bit later on in the course.

So now it’s time for the next piece of work for you to do.  I want you to take this which is currently example-03 in the supplied files and I want you to make sure that we have data, at least one actor for every movie and at least one movie for every actor.  So every actor should be in at least one movie and every movie should have at least one actor in it.  You may have to add an actor.  You may need to add a movie.  I’ll leave that to your discretion.  And the answer to that will be example-04, but in addition I would like you to add genre values for each of the movies that is in this version of the database.  Now most of them you can get three or four genre values probably by looking at IMDB.  It doesn’t particularly matter that they’re accurate.  It’s more important that you understand how the MovieGenre mechanism works.

So that’s it for this section.  I’ll see you in the next one.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!