Establishing a many-to-many relationship between the tables in a database is usually done for ensuring efficient data processing and data integrity, as well as for database normalization and data analysis tasks.
Since relational databases don’t allow implementing a direct many-to-many relationship between two tables, handling that kind of relationship can be an intimidating task. In this article, we’ll consider how to quickly and easily establish a many-to-many relationship between the tables in MySQL database.
What is a many-to-many relationship
Let us first understand what many-to-many relationships are all about. A many-to-many relationship happens in the situation when the records in one table are associated with the records in another one. A many-to-many relationship example in a database can be a table containing a list of books and a table with a list of authors. Each book may have one or more than one author, and each author may have written one or more than one book. In this case, a row in one table has many related rows in a second table. And at the same time, the rows in the second table have related rows in the first table.
Fig. 1 Many-to-many relationship example
A many-to-many relationship between tables is accommodated in databases by means of junction tables. A junction table contains the primary key columns of the two tables you want to relate.
Junction table
When you need to establish a many-to-many relationship between two or more tables, the simplest way is to use a Junction Table. A Junction table in a database, also referred to as a Bridge table or Associative Table, bridges the tables together by referencing the primary keys of each data table.
Fig. 2 A Junction Table example
How to create a many-to-many relationship in dbForge Studio for MySQL
With dbForge Studio for MySQL, you can create a many-to-many relationship between tables in a database quickly and easily. Below is a comprehensive algorithm that will help you cope with this task.
1. To start establishing a many-to-many relationship in MySQL, first, create a new or open an existing database diagram.
Fig. 3 Creating a database diagram
2. Add the tables you want to create a many-to-many relationship between.
Fig. 4 Adding the tables to create a many-to-many relationship between
3. Create a third table: right-click the database diagram body and select New Table from the context menu that appears. This table will serve as a junction table.
Fig. 5 Creating a junction table to establish a many-to-many relationship
4. In the Table Editor dialog box, enter a name for the table. For example, the junction table between the Authors table and the Books table can be named Books_Authors.
Fig. 6 Entering a name for the junction table
5. Copy the primary key columns from each of the other two tables to the junction table. You can add other columns to this table, just as to any other table.
Fig. 7 Building the junction table
6. In the junction table, set the primary key to include all the primary key columns from the other two tables.
Fig. 8 Creating new relations
7. Define a one-to-many relationship between each of the two primary tables and the junction table.
Fig. 9 Creating a many-to-many relationship between the tables
Note: The creation of a junction table in a database diagram does not insert data from the related tables into the junction table. You can copy rows from one table to another or within a table using an Insert From query.
INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;Conclusion
There are three different types of data relationships in a database: one-to-one, one-to-many, and many-to-many. And if you can handle the first two quite simply, coping with a many-to-many relationship can be a daunting task. In this article, we provided a step-by-step tutorial showing how to easily establish a many-to-many relationship model with the help of our superior tool, dbForge Studio for MySQL.
Download a free 30-day trial of dbForge Studio for MySQL right now and try the database diagram functionality along with many other mighty features of the best MySQL GUI tool you can find! Also, you can watch this video tutorial:
9.1.4.1 Adding Foreign Key Relationships Using an EER Diagram
The vertical toolbar on the left side of an EER Diagram has six foreign key tools:
one-to-one non-identifying relationship
one-to-many non-identifying relationship
one-to-one identifying relationship
one-to-many identifying relationship
many-to-many identifying relationship
Place a Relationship Using Existing Columns
Differences include:
-
An identifying relationship: identified by a solid line between tables
An identifying relationship is one where the child table cannot be uniquely identified without its parent. Typically this occurs where an intermediary table is created to resolve a many-to-many relationship. In such cases, the primary key is usually a composite key made up of the primary keys from the two original tables.
A non-identifying relationship: identified by a broken (dashed) line between tables
Create or drag and drop the tables that you wish to connect. Ensure that there is a primary key in the table that will be on the “one” side of the relationship. Click on the appropriate tool for the type of relationship you wish to create. If you are creating a one-to-many relationship, first click the table that is on the “many” side of the relationship, then on the table containing the referenced key. This creates a column in the table on the many side of the relationship. The default name of this column is table_name_key_name where the table name and the key name both refer to the table containing the referenced key.
When the many-to-many tool is active, double-clicking a table creates an associative table with a many-to-many relationship. For this tool to function there must be a primary key defined in the initial table.
Use the menu, menu item to set a project-specific default name for the foreign key column (see Section 9.1.1.1.5.4, “The Relationship Notation Submenu”). To change the global default, see Section 3.2.4, “Modeling Preferences”.
To edit the properties of a foreign key, double-click anywhere on the connection line that joins the two tables. This opens the relationship editor.
Pausing your mouse pointer over a relationship connector highlights the connector and the related keys as shown in the following figure. The film and the film_actor tables are related on the film_id field and these fields are highlighted in both tables. Since the film_id field is part of the primary key in the film_actor table, a solid line is used for the connector between the two tables. After pausing over a relationship for a second, a yellow box is displayed that provides additional information.
Figure 9.16 The Relationship Connector
If the placement of a connection's caption is not suitable, you can change its position by dragging it to a different location. If you have set a secondary caption, its position can also be changed. For more information about secondary captions, see Section 9.1.4.3, “Connection Properties”. Where the notation style permits, Classic for example, the cardinality indicators can also be repositioned.
The relationship notation style in Figure 9.16, “The Relationship Connector” is the default, crow's foot. You can change this if you are using a Commercial Edition of MySQL Workbench. For more information, see Section 9.1.1.1.5.4, “The Relationship Notation Submenu”.
You can select multiple connections by holding down the Control key as you click a connection. This can be useful for highlighting specific relationships on an EER diagram.