PROGRAMMING

MINTE9
REMEMBERS




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)
);

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
);    
 CodeCopy
SELECT * FROM Products 
    WHERE account_id REGEXP '[[:<:]]12[[:>:]]';    

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;    
... 4 lines
˄˄˄
Be conservative if you decide to employ denormalization. Start by using a normalized database organization.
Questions    
2. Parent ID
01 . Denormalization
Last update:   09-07-2021

        A B C D E F
🔔
1/4