Search⌘ K
AI Features
Home
Courses
SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Solution: Create an Intersection Table

Explore how to use intersection tables to manage many-to-many relationships between Products and Accounts. Learn to query efficiently, apply foreign key constraints for data validation, and enhance performance with indexes. Understand how separating data into multiple rows resolves issues with data integrity and enables flexible querying and updates.

We'll cover the following...

Instead of storing the account_id in the Products table, we can store it in a separate table, so each individual value of that attribute occupies a separate row. This new table Contacts implements a many-to-many relationship between Products and Accounts:

MySQL
CREATE TABLE Contacts (
  product_id BIGINT UNSIGNED NOT NULL,
  account_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (product_id, account_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id),
  FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);
INSERT INTO Contacts (product_id, account_id)
     VALUES (123, 12), (123, 34), (345, 23), (567, 12), (567, 34);

When the table has foreign keys referencing two tables, it’s called an intersection tableSome people use a join table, a many-to-many table, a mapping table, or other terms to describe this table. The name doesn’t matter; the concept is the same.. This implements a many-to-many relationship between the two referenced tables. That is, each product may be associated through the intersection table to multiple accounts, and likewise, each account may be associated with multiple products. See the Intersection table Entity-Relationship Diagram below.

Let’s see how using an intersection table resolves all the problems we saw in the previous lesson.

Querying products by account and the other way around

In order to query the attributes of all products for a given account, it’s more straightforward to join the Products table with the Contacts table.

Let’s run the code in the playground below. You can also update the code written below.

MySQL
SELECT p.product_id, p.product_name
  FROM Products AS p JOIN Contacts AS c 
    ON (p.product_id = c.product_id)
 WHERE c.account_id = 34;

Some people resist queries that contain a JOIN, thinking that they perform poorly. However, this query uses indexes much better than the solution shown earlier in the previous lesson.

Querying account details is likewise easy to read and easy to optimize. It uses indexes for JOIN efficiently, instead of an esoteric use of “regular expressions.”

Let’s run the query below to see the effect of this query on the database.

MySQL
SELECT a.*
  FROM Accounts AS a JOIN Contacts AS c ON (a.account_id = c.account_id)
 WHERE c.product_id = 123;

Note: The records for account_id having the values 12, 23, and 34 are already available in the database. If we want to query some more data, we can add the data by using the standard syntax.

The same is the case with the Products table. The records for 123, 345, and 567 are already available. If we want to query some more data, we can add the data by using the standard syntax.

Making aggregate queries

Let’s run the given query in the following widget. We can also try to use some other aggregate queries that use functions like COUNT(), SUM(), and AVG(). The following example returns the number of accounts per product:

MySQL
SELECT product_id, COUNT(*) AS accounts_per_product
  FROM Contacts
 GROUP BY product_id;

The number of products per account is just as simple:

MySQL
SELECT account_id, COUNT(*) AS products_per_account
  FROM Contacts
 GROUP BY account_id;

Note: Try to run the query given above. You can edit the code and see the effect on the database.

Other more sophisticated reports are possible too, such as the product with the greatest number of accounts:

MySQL
SELECT  c.product_id, c.contacts_per_product
  FROM (
       SELECT product_id, COUNT(*) AS contacts_per_product
         FROM Contacts
        GROUP BY product_id
  ) AS c
 ORDER BY c.contacts_per_product DESC LIMIT 1

Updating contacts for a specific product

We can add or remove entries in the list by inserting or deleting rows in the intersection table. Each product reference is stored in a separate row in the Contacts table, so we can add or remove them one at a time.

MySQL
INSERT INTO Contacts (product_id, account_id) 
     VALUES (456, 34);
SELECT * FROM Contacts;

Note: If we want to insert data other than what is written in the playground, we must first check the available data by querying the specific table.

Now, let’s try to delete the same data that we added in the previous playground.

MySQL
INSERT INTO Contacts (product_id, account_id) 
     VALUES (456, 34);
DELETE FROM Contacts 
      WHERE product_id = 456 AND account_id = 34;
SELECT * FROM Contacts;

Validating product IDs

We can use a foreign key to validate the entries against a set of legitimate values in another table. We declare that Contacts.account_id references Accounts.account_id, and therefore rely on the database to enforce referential integrity. Now we can be sure that the intersection table contains only account IDs that exist.

We can also use SQL data types to restrict entries. For example, if the entries in the list should be valid INTEGER or DATE values and we declare the column using those data types, we can be sure that all entries are legal values of that type (not nonsense entries like “banana”).

Let’s add “banana” instead of “456” product_id and see what happens.

MySQL
INSERT INTO Contacts (product_id, account_id) 
     VALUES ('banana', 34);

Choosing a separator character

We don’t use any separator character since we store each entry on a separate row. In this way, there’s no ambiguity even if the entries themselves contain commas or other characters that may be used as separators.

List length limitations

Since each entry is in a separate row in the intersection table, the list is limited only by the number of rows that can physically exist in one table. If it’s appropriate to limit the number of entries, we should enforce the policy in our application using the count of entries rather than the collective length of the list.

Other advantages of the intersection table

Creating an index on Contacts.account_id makes performance better than matching a substring in a comma-separated list. Declaring a foreign key on a column implicitly creates an index on that column in many database brands.

We can also create additional attributes for each entry by adding columns to the intersection table. For example, we can record the date a contact was added for a given product, or we can specify an attribute to differentiate primary contacts from secondary contacts. We can’t do this in a comma-separated list.