Metadata as Data

This smell occurs when metadata is stored as data in the form of EAV (Entity-Attribute-Value) pattern.

In a relational table, all the attributes are equally applicable for all the rows in the table. It is tempting to implement EAV pattern when a subset of attributes applicable for a subset of rows and the rest of attributes for rest of the rows. However, this arrangement introduces many deficiencies in the database; for example, one can't use native SQL data types (leading to invalid data), one can't enforce referential integrity, and one can't make up attribute names.

Example

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)
);

Tools

The following set of tools detects this smell: DbDeo(for SQL)

Reference

B. Karwin, “SQL Antipatterns: Avoiding the Pitfalls of Database Programming”. Pragmatic Bookshelf, 2010.