O'Reilly logo

SQL Antipatterns by Bill Karwin

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Solution: Create an Intersection Table

Instead of storing the account_id in the Products table, 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:

Jaywalking/soln/create.sql
 
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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required