minte9
LearnRemember   ANTIPATTERNS





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. 
 
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. 
 
CREATE TABLE Products (
    ...
    account_id VARCHAR(100), #comma-separated list
);    

 
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. 
 
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;
Be conservative if you decide to employ denormalization. Start by using a normalized database organization.





Questions and answers:
Clink on Option to Answer




1. Comma-separated lists as values in DB are used ...

  • a) to avoid creating an intersection table
  • b) to avoid a many-to-one relationship

2. What's the problem when using comma-separated lists?

  • a) you cannot use use indexes
  • b) you cannot use denormalization

3. Which one doesn't refer to this antipattern

  • a) account_id BIGINT
  • b) account_id VARCHAR(100)

4. Programmers correct this antipattern with:

  • a) regex
  • b) intersection table and foreign keys


References: