Context: Bugs and Features share the same attributesWe would like to design software that can adapt fluidly to future changes with little or no intervention. What if the date is stored in the date_reported or report_date column? You may encounter these problems and others when you employ the antipattern known as Entity-Attribute-Value.
Antipattern: Attribute TableThe 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 IssueAttributes ( issue_id BIGINT UNSIGNED NOT NULL, attr_name VARCHAR(100) NOT NULL, attr_value VARCHAR(100), PRIMARY KEY (issue_id, attr_name), FOREIGN KEY (issue_id) REFERENCES Issues(issue_id) );When you use EAV, you sacrifice many advantages that a conventional database design would have given you. 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 as though data were stored in a conventional table. -- You can't make mandatory attributes. In a conventional database design, it would be simple to enforce a mandatory column by declaring the column NOT NULL. -- You can't use SQL data types. In a conventional database, you can prevent this if you declared the column with the DATE data type. -- You can't enforce referential integrity. In a conventional database, you can restrict the range of some attributes by defining a foreign key to a lookup table.
INSERT INTO Issues (date_reported) VALUES ('banana'); -- ERROR!
Solution: Class table InheritanceMimics 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 ( issue_id 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, severity VARCHAR(20), version_affected VARCHAR(20), FOREIGN KEY (issue_id) REFERENCES Issues(issue_id) ); CREATE TABLE FeatureRequests ( issue_id BIGINT UNSIGNED PRIMARY KEY, sponsor VARCHAR(50), FOREIGN KEY (issue_id) REFERENCES Issues(issue_id) );