01 – Many-To-Many Relationships Review

Understanding many-to-many relationships is critical to creating a database that doesn’t suffer from issues such as data redundancy, inefficiency and bloat. In this series of blog posts, rather than focus too much on what many-to-many relationships are, we will be taking a practical look at how they can be implemented in an Access database.

Knowing how to structure your tables correctly is one thing, but how exactly should data be entered? What do we do when we have multiple many-to-many relationships related to one table?

In this mini-series we will be answering questions such as these.

A Common Problem

We would, of course, be remiss not to include some kind of overview of how many-to-many relationships work. We will, though, be doing this by showing you how a common relational problem is arrived at.

To follow this post, please download the 01_dbManyToMany file.

Open up the database and let’s take a look at the table structure by clicking on Relationships in the Database Tools group of the Ribbon.

Screenshot 2014-04-09 15.35.26

We have 3 tables, Films, Directors and Actors. The “main” table is the Films table and Films contains a one-to-many relationship with the Directors table (i.e. one director can appear many times in the Films table BUT one Film can only have one director). If we view the FilmsDS form we can see, for example, that Memento was directed by Nolan, E.T. by Spielberg and Gravity by Cuaron. This relationship suffices because films have only one director (ignore The Matrix for now).

Screenshot 2014-04-09 15.40.59

We also have a one-to-many relationship with Actors and we need to enter some actor data in the Films table as we have neglected to do so up to now.

Ok, so Guy Pearce was in Memento. Great!

Screenshot 2014-04-09 15.44.14

Now, who else was in Memento? Oh yeah, Joe Pantaliano. Ok, so how do we enter Joe? Well, we can’t. We have told the database that one film can have only one actor!!! A data modelling error for sure.

So, what do we do? Well we need to create a many-to-many relationship! Which is interesting as they don’t actually exist!!!

Do Many-To-Many Relationships Exist?

Technically no, funny enough. In order to get the functionality we desire, we will need to create an “in-between” table that contains two one-to-many relationships. In this case, the table we need will hold information about which actors appeared in which films and within this table actors can be duplicated, films can be duplicated but any given combination of the two CANNOT be duplicated. That sounds unnecessarily complicated so why don’t we uncomplicate it by extending the previous example.

First let us delete the relationship between Films and Actors:

  • Go to the Database Tools tab on the Ribbon.
  • Select Relationships from the Relationships group.
  • Right-click on the link between Films and Actors (this can be tricky as you have to be precise).

Screenshot 2014-04-13 21.51.21

Choose Delete from the drop-down menu that appears.
A form will pop up asking you if you are sure you want to delete the relationship. Select Yes.

Screenshot 2014-04-13 21.51.35

Your window will now look like this.

 Screenshot 2014-04-13 21.52.14

Now for the linking table. We are going to create a table that contains two primary keys and serves as a link between the Film’s and Actor’s tables.

Open up a new table in design view.

Screenshot 2014-04-13 22.02.26

Add two fields – FilmID and ActorID (both Long integer data type).

Screenshot 2014-04-13 22.02.58

Highlight both fields and click on the Primary Key button.

Screenshot 2014-04-13 22.03.36

Save the table and call it linkFilmsActors.

Screenshot 2014-04-13 22.03.48

Ok, now we need to set the relationships between the relevant tables.
Open up the relationships window again and right-click anywhere in the designer.
Choose Show Table from the drop-down menu.

Screenshot 2014-04-13 22.14.53

Select linkFilmsActors and click on Add. Close the Show Table dialog box.

Screenshot 2014-04-13 22.15.23

You are now able to see linkFilmsActors.
Place it between Films and Actors.
Click on FilmID in linkFilmsActors and drag it over to ID in Films. You must not release the mouse until you are over ID in Films.

A dialog box will open.
Click on Enforce Referential Integrity and click on create.

Screenshot 2014-04-13 22.22.49

You will now have a link between linkFilmsActors and Films.

Screenshot 2014-04-13 22.19.45

Repeat for linkFilmsActors.ActorID and Actors.ID.

Screenshot 2014-04-13 22.20.00Congratulations! You have created your first many-to-many relationship. Well, probably not actually. If this is your first time then go have a beer, if not, go have one anyway.

So, now we have reviewed what a many-to-many relationship is, how on earth do you enter data in one?

Go to the next post to find out.

Leave a Reply

Your email address will not be published. Required fields are marked *

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube