minte9

LearnRemember

Context

Categorized tags

Add categorized tags to bugs. Some bugs may be categorized by the software subsystem (printing, reports, email). Other bugs may be categorized by the nature of the defect (crash, performance).

Antipattern

Create multiple columns.

 
CREATE TABLE Bugs (
    bug_id SERIAL PRIMARY KEY,
    description VARCHAR(1000),
    <span class='keyword_code'>tag1</span> VARCHAR(20),
    tag2 VARCHAR(20),
    tag3 VARCHAR(20)
);    

As you assign tags to a given bug, you’d put values in one of these three columns. Unused columns remain null. You probably don't want the same value to appear in multiple columns, but when you use the Multicolumn Attributes antipattern, the database can't prevent this.

 
INSERT INTO Bugs (description, tag1, tag2)
    VALUES ('printing is slow', 'printing', 'performance');

Another weakness of this design is that three columns might not be enough. Database table that already contains data may require locking the entire table. Also, you must revisit every SQL statement in every application {{that uses this table}. If you miss any queries that need edits, it can lead to bugs that are difficult to detect.

 
ALTER TABLE Bugs ADD COLUMN tag4 VARCHAR(20);    

Legitimate Uses

In some cases, an attribute may have a fixed number of choices (reported_by, assign_to, tested_by).

Solution

Dependent Table

Best solution is to create a dependent table with one column for the multivalue attribute. Store the multiple values in multiple rows instead of multiple columns.

 
CREATE TABLE <span class='keyword_code'>Tags</span> (
    bug_id BIGINT UNSIGNED NOT NULL
    tag VARCHAR(20),
    PRIMARY KEY (bug_id, tag),
    FOREIGN KEY (bug_id) <span class='keyword_code'>REFERENCES Bugs(bug_id)</span>
);

INSERT INTO Tags (bug_id, tag)
    <span class='keyword_code'>VALUES</span> (1234, 'crash'), (3456, 'printing'), (3456, 'performance');    

Searching for bugs with a given tag is more straightforward. Even more complex searches, such as a bug that relates to two specific tags, is easy to read.

 
SELECT * FROM Bugs JOIN Tags USING (bug_id)
    WHERE tag = 'performance';

SELECT * FROM Bugs
    JOIN Tags AS t1 <span class='keyword_code'>USING</span> (bug_id)
    JOIN Tags AS t2 <span class='keyword_code'>USING</span> (bug_id)
WHERE t1.tag = '<span class='keyword_code'>printing</span>' AND t2.tag = '<span class='keyword_code'>performance</span>';    

Simply insert or delete a row from the dependent table. The PRIMARY KEY constraint ensures that no duplication is allowed. You are not limited to three tags per bug.