Using Many to Many SQL Relationships and Intermediate Tables

Introduction

When working in a complex database, you can use an intermediate table to create a many-to-many(M:N) relationship between two tables. In such a model every single record in table A, relates to zero, one, or many instances of entries in table B. Likewise, for every record in table B, there exists zero, one, or many related records in table A.

To put this into perspective. Here are some examples of many-to-many relationships.

• In a hiring platform database, a single contractor can work for many companies, and each different company can have many contractors working for them.
• In a college database, a single student might enroll in multiple courses, and many students might take a single course.
• In a multi-office/multi-store shopping-cart database, a single product might be available for sale in many different offices, while a single office might sell many different products.
• In a movie database, a single star can act many movies while many actors might act in a single movie.
• In a real estate database, many different tenants can occupy a single apartment, while a single tenant can rent many different apartments.

To come up with an optimized database schema to model the above scenarios, you must use an intermediate table. In this guide, you'll create a sample database for an online shopping cart and create a many-to-many relationship. In this sample database, you'll model a scenario where one product might be available for sale in different outlets while a single office might also sell different products.

Prerequisites

To complete this tutorial, make sure you have the following.

• An Ubuntu 20.04 server.
• A sudo user.
• A LAMP Stack. For this tutorial, you'll only need to install MySQL or a MariaDB server.

Create a sample Database

First, SSH to your server and log in to MySQL as root.

$ sudo mysql -u root -p

When prompted, key in your MySQL server's root password and hit Enter to proceed. Next, run the command below to set up a sample database.

mysql> CREATE DATABASE sample;

Switch to the sample database.

mysql> USE sample;

With the database in place, you'll now move on to creating the base tables for your shopping-cart.

Create and Populate the Base Tables

In this step, you'll create the offices and products table. The offices table will contain a list of all offices where your business operates, while the products tables will list all items available for sale in the different offices.

Please note, not all products will be available for sale in the different offices. In a real-world example, your store might operate in different jurisdictions where the sale of certain products may not be allowed. Another scenario that might force you to disable the availability of the products in some stores is logistical issues such as high shipping costs or lack of customers.

First, create the offices table. Later in this guide, you'll see how using an intermediary table will be the best option for managing the products' availability in your different offices.

mysql> CREATE TABLE offices
       (
           office_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           office_name VARCHAR(50)
       ) ENGINE = InnoDB;

For this guide, assume your company operates in three offices. Populate the table with the following records.

mysql> INSERT INTO offices(office_name) VALUES ('NEW YORK');
mysql> INSERT INTO offices(office_name) VALUES ('LOS ANGELES');
mysql> INSERT INTO offices(office_name) VALUES ('CHICAGO');

Query the offices table to make sure that the data was inserted successfully.

mysql> SELECT
       office_id,
       office_name
       FROM offices;

Ensure you get the list below.

+-----------+-------------+
| office_id | office_name |
+-----------+-------------+
|         1 | NEW YORK    |
|         2 | LOS ANGELES |
|         3 | CHICAGO     |
+-----------+-------------+
3 rows in set (0.00 sec)

Next, create the products table.

mysql> CREATE TABLE products
       (
           product_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           product_name VARCHAR(50),
           retail_price DOUBLE
       ) ENGINE = InnoDB;

Populate the products table with some records

mysql> INSERT INTO products(product_name, retail_price) VALUES ('WINTER COAT', '59.55');
mysql> INSERT INTO products(product_name, retail_price) VALUES ('SMART WATCH', '199.45');
mysql> INSERT INTO products(product_name, retail_price) VALUES ('UNIVERSAL REMOTE CONTROL', '9.95');
mysql> INSERT INTO products(product_name, retail_price) VALUES ('METAL CASE FLASK', '12.75');

Query the products table to make sure that all the items are inserted successfully.

mysql> SELECT
       product_id,
       product_name,
       retail_price
       FROM products;

Make sure all the products are displayed as shown below.

+------------+--------------------------+--------------+
| product_id | product_name             | retail_price |
+------------+--------------------------+--------------+
|          1 | WINTER COAT              |        59.55 |
|          2 | SMART WATCH              |       199.45 |
|          3 | UNIVERSAL REMOTE CONTROL |         9.95 |
|          4 | METAL CASE FLASK         |        12.75 |
+------------+--------------------------+--------------+
4 rows in set (0.00 sec)

After defining and populating the base tables, the next step is setting up an intermediary table to link them.

Create and Populate an Intermediary Table

In this step, you'll create an intermediary table to establish a many-to-many relationship between the offices and the products tables.

When creating the intermediary table, a general rule of thumb is to craft a name by concatenating the name of both tables that requires a linkage and separating them with the preposition to and the underscore character(_).

In the different examples highlighted in the introduction, here are some great names that you can use when creating the respective intermediary tables.

• Hiring platform database: contractors_to_companies table
• College database: students_to_courses table
• Multi-store shopping cart: products_to_stores table
• Movie database: actors_to_movies table
• Real estate database: tenants_to_apartments table.

Since you're using the shopping-cart example for this guide, create a products_to_offices intermediary table.

mysql> CREATE TABLE products_to_offices
       (
           reference_id  BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           product_id BIGINT NOT NULL,
           office_id INT NOT NULL,
           INDEX (product_id),
           INDEX (office_id)
       ) ENGINE = InnoDB;

Next, you'll populate the products_to_offices table. Before you do this, revisit your products catalog and see the globally available items for sale.

1. WINTER COAT
2. SMART WATCH
3. UNIVERSAL REMOTE CONTROL
4. METAL CASE FLASK

Then, you have three offices.

1. NEW YORK
2. LOS ANGELES
3. CHICAGO

Assume that the WINTER COAT(product_id no 1) will be available for sale in all three offices.

mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('1', '1');
mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('1', '2');
mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('1', '3');

Next, avail the SMART WATCH(product_id no 2) to the NEW YORK(office_id no 1) and CHICAGO(office_id no 3) offices only by running the command below.

mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('2', '1');
mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('2', '3');

Then, associate the UNIVERSAL REMOTE CONTROL(product_id no 3) to only the CHICAGO office(office_id no 3) offices:

mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('3', '3');

Then provision the METAL CASE FLASK(product_id no 4) to both the NEW YORK(office_id no 1) and CHICAGO(office_id no 3) offices:

mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('4', '1');
mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('4', '3');

After running the INSERT statements above, you've now successfully established a many-to-many relationship in your database.

Query the products_to_offices table to see how the relationships are saved.

mysql> SELECT
       reference_id
       product_id,
       office_id
       FROM products_to_offices;

Output

+------------+-----------+
| product_id | office_id |
+------------+-----------+
|          1 |         1 |
|          4 |         1 |
|          7 |         1 |
|          2 |         2 |
|          3 |         3 |
|          5 |         3 |
|          6 |         3 |
|          8 |         3 |
+------------+-----------+
8 rows in set (0.00 sec)

To see the products' availability in each office, run the commands below.

1. NEW YORK(office_id = '1') catalog:

   mysql> SELECT
          products.product_id,
          products.product_name,
          retail_price
          FROM products_to_offices
          LEFT JOIN products
          ON products_to_offices.product_id = products.product_id
          WHERE products_to_offices.office_id = '1';

   Output

   +------------+------------------+--------------+
   | product_id | product_name     | retail_price |
   +------------+------------------+--------------+
   |          1 | WINTER COAT      |        59.55 |
   |          2 | SMART WATCH      |       199.45 |
   |          4 | METAL CASE FLASK |        12.75 |
   +------------+------------------+--------------+
   3 rows in set (0.00 sec)
   

2. LOS ANGELES(office_id = '2') catalog:

   mysql> SELECT
          products.product_id,
          products.product_name,
          retail_price
          FROM products_to_offices
          LEFT JOIN products
          ON products_to_offices.product_id = products.product_id
          WHERE products_to_offices.office_id = '2';

   Output

   +------------+--------------+--------------+
   | product_id | product_name | retail_price |
   +------------+--------------+--------------+
   |          1 | WINTER COAT  |        59.55 |
   +------------+--------------+--------------+
   1 row in set (0.00 sec)
   

3. CHICAGO(office_id = '3') catalog:

   mysql> SELECT
          products.product_id,
          products.product_name,
          retail_price
          FROM products_to_offices
          LEFT JOIN products
          ON products_to_offices.product_id = products.product_id
          WHERE products_to_offices.office_id = '3';

   Output

   +------------+--------------------------+--------------+
   | product_id | product_name             | retail_price |
   +------------+--------------------------+--------------+
   |          1 | WINTER COAT              |        59.55 |
   |          2 | SMART WATCH              |       199.45 |
   |          3 | UNIVERSAL REMOTE CONTROL |         9.95 |
   |          4 | METAL CASE FLASK         |        12.75 |
   +------------+--------------------------+--------------+
   4 rows in set (0.00 sec)

The above outputs confirm that your many-to-many relationship is working as expected.

Conclusion

In this guide, you've learned how to create a many-to-many relationship using an intermediary table on MySQL. Always use the logic in this tutorial when designing a database schema where an M:N relationship is a requirement.