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.
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).
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!
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).
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.
Your window will now look like this.
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.
Add two fields – FilmID and ActorID (both Long integer data type).
Highlight both fields and click on the Primary Key button.
Save the table and call it linkFilmsActors.
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.
Select linkFilmsActors and click on Add. Close the Show Table dialog box.
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.
You will now have a link between linkFilmsActors and Films.
Repeat for linkFilmsActors.ActorID and Actors.ID.
Congratulations! 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.
