minte9

LearnRemember

Context

Bugs and Features tables share the same attributes. We would like to design software that can adapt fluidly to future changes. What if the date is stored in the date_reported or report_date column? This happens you employ the antipattern known as Entity-Attribute-Value.

Attribute Table antipattern

The solution that appeals to some programmers is to create a second table. Storing attributes as rows. This appears to be an improved design. However, the simple database structure doesn’t make up for the difficulty of using it.

 
CREATE TABLE Issues (
    issue_id SERIAL PRIMARY KEY
);

INSERT INTO Issues (issue_id) VALUES (1234);

CREATE TABLE <span class='keyword_code'>IssueAttributes</span> (
    issue_id BIGINT UNSIGNED NOT NULL,
    <span class='keyword_code'>attr_name</span> VARCHAR(100) NOT NULL,
    <span class='keyword_code'>attr_value</span> VARCHAR(100),
    PRIMARY KEY (issue_id, attr_name),
    FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);        

If you are stuck with EAV design, familiarize yourself with the trouble areas. Above all, don’t try to write queries that fetch entities as a single row.

Conventional DB advantages

When you use EAV, you sacrifice many advantages of a conventional DB. You can't make mandatory attributes. It should be simple to enforce a mandatory column, declaring it NOT NULL. You can't use SQL data types. In a conventional database, you can prevent this if you declared the column. You can't enforce referential integrity. You should restrict the range of some attributes with foreign key to a lookup table.

 
INSERT INTO Issues (date_reported) VALUES ('banana'); -- <span class='keyword_code'>ERROR!</span>    

Solution

Class table Inheritance. Mimics inheritance, as though tables were object-oriented classes. Create a single table for the base type, containing attributes common to all subtypes. Then create another table for each subtype.

  
CREATE TABLE Issues (
    <span class='keyword_code'>issue_id</span> SERIAL PRIMARY KEY,
    reported_by BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED,
    priority VARCHAR(20),
    version_resolved VARCHAR(20),
    status VARCHAR(20),
    FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE Bugs (
    issue_id BIGINT UNSIGNED PRIMARY KEY,
    <span class='keyword_code'>severity</span> VARCHAR(20),
    version_affected VARCHAR(20),
    <span class='keyword_code'>FOREIGN KEY (issue_id)</span> REFERENCES Issues(issue_id)
);

CREATE TABLE FeatureRequests (
    issue_id BIGINT UNSIGNED PRIMARY KEY,
    <span class='keyword_code'>sponsor</span> VARCHAR(50),
    <span class='keyword_code'>FOREIGN KEY (issue_id)</span> REFERENCES Issues(issue_id)
);