Memory Refresh
SQLAntipatterns



Multicolumn attributes





Context: 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). A bad color choice could be tagged with cosmetic.

Antipattern: Create Multiple Columns

CREATE TABLE Bugs ( bug_id SERIAL PRIMARY KEY, description VARCHAR(1000), tag1 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, tag3) VALUES ('printing is slow', 'printing', 'performance', '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 of the Antipattern: In some cases, an attribute may have a fixed number of choices (reported_by, assign_to, tested_by).

Solution: Create 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 Tags ( bug_id BIGINT UNSIGNED NOT NULL tag VARCHAR(20), PRIMARY KEY (bug_id, tag), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id) ); INSERT INTO Tags (bug_id, tag) VALUES (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 USING (bug_id) JOIN Tags AS t2 USING (bug_id) WHERE t1.tag = 'printing' AND t2.tag = 'performance'; 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.