EAV Pattern and Its Pitfalls

Entity-Attribute-Value

EAV stores arbitrary attributes as rows (entity_id, attribute_name, value). Flexible but hard to query, slow to aggregate, and breaks type safety. Use JSON columns instead.

Example

-- EAV (anti-pattern for most cases)
CREATE TABLE product_attrs (
  product_id INT,
  attr_name  VARCHAR(50),
  attr_value VARCHAR(255)
);

-- To get color and size, requires two JOINs
-- Better: use JSON column or separate typed columns

SQL

Full Editor

Pro Tip

EAV is tempting for flexibility but creates query complexity — prefer JSON columns in MySQL 5.7+.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned