Show Many-to-many relationships are the most commonly used table relationships. They provide crucial information, such as which customers your salespeople have contacted and which products are in customer orders. A many-to-many relationship exists when one or more items in one table can have a relationship to one or more items in another table. For example:
For example, customer Elizabeth Andersen’s order number 1012 might contain one each of products 12 and 15 and five of product 30. You create many-to-many relationships differently than you do one-to-one or one-to-many. For those relationships, you simply connect the appropriate fields with a line. To create many-to-many relationships, you need to create a new table to connect the other two. This new table is called an intermediate table (or sometimes a linking or junction table). In the scenario described earlier, you create an Order Details table with records that contain, for each item in any given order, the ID from the Order table and the ID from the Products table. You create a primary key for that table using the combined keys from the two tables. In our scenario, Elizabeth Andersen’s order number 1012 consists of products 12, 15, and 30. That means our Order Details entries look like this:
Elizabeth ordered one each of products 12 and 15, and five of product 30. We can’t add another row with 1012 and 30 because the Order ID and the Product ID combined make up our primary key, and primary keys must be unique. Instead, we add a Quantity field to the Order Details table.
As the first table column, Access automatically adds an ID field. Change that field to match the ID of the first table in your many-to-many relationship. For example, if the first table is an Orders table called Order ID, and its primary key is a number, change the name of the ID field in the new table to Order ID and, for the data type, use Number.
Now that you have fields containing IDs from the two tables you want to connect, create a primary key, based on those IDs, in the intermediate table.
To complete the many-to-many relationship, create a one-to-many relationship between the primary key field in each table and the matching field in the intermediate table. For details on how to do this, see Get started with table relationships. When you finish, the relationships should look something like this:
Get started with table relationships Create, edit or delete a relationship
What is a many-to-many relationship in database modeling? How do you implement this relationship in a database? The examples in this article will answer these questions. Many-to-many relationships are probably the trickiest relationships to show in a database. So, my first step in this article will be to explain what they are. Then I’ll move on to giving you several examples of many-to-many relationships, keeping them as close to real life as possible. Finally, I’ll use those examples to show you how to implement many-to-many relationships in a relational database. Ready? Let’s get started. Many-to-Many Relationship in TheoryA many-to-many (or M:N) relationship is one of the three database relationships. The other two are:
By definition, a many-to-many relationship is where more than one record in a table is related to more than one record in another table. Such a relationship can be tricky to represent in the database, so I’ll show you how to do it in the following example. You might also want to read about entities, attributes, and how to define them. I’ll use those concepts a lot, so reading the article won’t hurt. Many-to-Many Relationship in PracticeLet’s move on to the three examples. I’ll show these many-to-many relationships in ER diagrams drawn in the Vertabelo Database Modeler. Example 1: University DatabaseIn this example, your task is to build a university database. You’ve just started, but you’re already stuck. You have to show the professors and the subjects they teach, but how will you do it? Let me show you how to represent this relationship by first defining the entities. One should be the professor entity, and the other is subject. Let’s check if this could be a many-to-many relationship. One professor could teach one or many subjects, but one subject could also be taught by one or many professors. It seems it is an M:N relationship, so here’s how your logical model should look: The professor entity has the following attributes:
The subject entity has the attributes:
Many-to-many relationships are not ideal. If left as it is in the above example, the data would be duplicated. For instance, if there’s a professor that teaches six subjects, you would have him or her listed in the table six times, every time for a different subject. This is quite inefficient. So, how would you resolve this many-to-many relationship between these two entities? By introducing a junction table into your model. It will resolve the many-to-many relationship into multiple one-to-many relationships. Let’s transpose this logical model to a physical data model and see what happens. First, the entities professor and subject will become tables, and their attributes will be the tables’ columns. It should look like this: As you can see, there’s a new table called professor_subject. It contains the following attributes:
The column professor_id is a foreign key to the table professor. The same goes with the subject_id; it’s a foreign key to the table subject. At the same time, the pair professor_id, subject_id is the primary key for the table professor_subject. The columns professor_id and subject_id together form a composite primary key (i.e. the primary key consists of two or more columns). This composite primary key ensures that a professor can be assigned to one subject one time. Each pair of values (professor_id, subject_id) can be in the table no more than once. The same goes for the subjects; each one can be assigned to one professor one time. The composite key ensures the uniqueness of the attribute combinations. Let’s check that the junction table solves the many-to-many relationship. One professor can be allocated only once to the same subject. On the other hand, one subject can be assigned only once to the same professor. It seems that the junction table serves its purpose. That’s the first bit of good news! The next piece of good news is that I didn’t have to draw this physical model all over again. There is a nice little trick provided by the Vertabelo Database Modeler that transforms a logical model into a physical one in four clicks. Here’s how to do it: 1. In your Documents, right-click on the model you want to make into a physical model. Choose the option “Generate physical model…” 2. Then you’ll see the dialogue box where you have to choose your target database engine. Select it (that’s your third click), and click “Generate Physical Model”. 3. After only four clicks, you’ll get the lovely physical database model I already showed you. If building a data model from scratch is not your forte, here are some tips to make it easier. Let’s move to the next example. It’s a little more complex, but don’t be afraid! Example 2: Product Ordering DatabaseIn this example, your task is to create a database that will help a company store information about their suppliers. The database will also contain info on all the products/services ordered from the suppliers. The logical data model could look something like this: Once again, we have two entities: In the supplier entity, there are four attributes:
The attributes in the product entity are as follows:
The relationship between those two entities is again many-to-many. One or many products can be ordered from one supplier. At the same time, the company can order the same product from many suppliers, e.g. services from different legal firms, tires from different manufacturers, etc. How would this logical model look when transformed into a relational database model? Like this: Once again, instead of many-to-many, there’s a new table that’s automatically been named supplier_product. It has only two attributes:
Again, the pair supplier_id, product_id is the primary key of the table. However, this might not be enough! If the task is to create a database that will record orders from suppliers, it would be better to expand the table supplier_product a bit, as I’ve done below: It looks much better now! First of all, I changed the table’s name to something more descriptive; it’s now named order. I’ve also added several new attributes to the table. It consists of the following:
Remember that there are two possibilities when creating a junction table. One is that it contains only foreign keys that reference other tables, which happens often enough. However, sometimes the junction table becomes its own entity, as in this example where it also contains other attributes. You should always adapt the model to your needs. If you want to practice a bit more on a similar example, see this article. Now that you’ve become so good at this, let’s take a look at one more example! Example 3: Book Publisher DatabaseIn this example, you’re in the publishing business and you need to maintain a record of the books you’ve published. Many people are involved in producing a book, so you also want to have a record of these people and their roles. The logical model could look something like this: This time, there are three entities: The book entity contains these attributes:
The next entity is staff:
Between these two entities, there’s a many-to-many relationship. Let’s check the logic. One staff member can work on one or many books. One book can be handled by one person (well, hardly) or by many people. It seems the relationship logic works! On to the third entity, role! It consists of:
Again, there is a many-to-many relationship between the entities staff and role. Logic says that one staff member can fill one or many roles when working on a book and that one role can be performed by one or many staff members. When I say role, I mean something like author, co-author, editor, proofreader, translator, illustrator, etc. For instance, the author of one book can also be an illustrator on another book, translator on a third, and proofreader on a fourth. This example seems even more complicated than the first two. Until now, there were only two tables in the logical model. How will the physical model look in this case? Like this: Does that look scary? Well, maybe a little bit. What happened here is that you were introduced to ternary relationships without knowing it. A ternary relationship is when three entities participate in the relationship, as in this example. Here, the junction table again has a composite primary key that is made up of foreign keys. This time, however, the primary key consists of three columns, not two. The table book is important here; it’s the starting point from which the creators of the book have to be determined. When I say creators, I mean the staff members who participated in that book as well as their roles in that particular book. Next, let’s analyze the junction table book_creators. It has three attributes:
The primary key of the table is the unique combination of the attributes book_isbn, staff_id, and role_id. By creating this junction table, you’ve solved the many-to-many relationship, which is enough to declare this task finished! What Are Your Thoughts on Many-to-Many Relationships?In these three examples, I’ve tried to show you what many-to-many relationships are in a logical database model. We’ve also discussed how to approach your physical data model. You’ve learned that many-to-many relationships in relational databases can be solved by implementing a junction table. And finally, we also demonstrated that you don’t have to draw logical and physical models separately. The Vertabelo Database Modeler can help you generate a physical model from the logical one, sometimes without any additional tweaks. |