REMEMBER ANTIPATTERNS

MINTE9
2REMEMBER




Last update:   09-07-2021

Context

You might be needed a bug tracking application for products. Your original design allowed only one user to be the contact for each product.
 CodeCopy
CREATE TABLE Products (
    ...
    account_id BIGINT UNSIGNED, # many-to-one relationship
    FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);
... 5 lines
˄˄˄

Antipattern

As project growth, you were requested to support assigning multiple users. You choose to store a list of user account identifiers separated by commas.
 CodeCopy
CREATE TABLE Products (
    ...
    account_id VARCHAR(100), #comma-separated list
);    
... 4 lines
˄˄˄
 CodeCopy
SELECT * FROM Products 
    WHERE account_id REGEXP '[[:<:]]12[[:>:]]';    
... 2 lines
˄˄˄

Bugs

Soon your boss approaches: - They tell me they can add five people only!

SOLUTION

Use an intersection table with foreign keys. Some people resist queries that contain a join, thinking that they perform poorly. However, this query uses indexes much better.
 CodeCopy
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)
);

SELECT p.*
FROM Products AS p JOIN Contacts AS c ON (p.account_id = c.account_id)
WHERE c.account_id = 34;    
... 10 lines
˄˄˄
Be conservative if you decide to employ denormalization. Start by using a normalized database organization.
Questions    
No. 2   Parent ID
CARD
01

        A B C D E F
🔔
1/4