SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Solution: Create an Intersection Table

Solution: Create an Intersection Table

Let's devise a solution for Jaywalking by creating an intersection table.

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 ...