Search⌘ K
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 ...