EAV Concept Introduction

The Problem with Traditional Tables

Consider storing products with varying attributes:

-- Traditional approach: one column per attribute
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    color VARCHAR(50),
    size VARCHAR(50),
    weight DECIMAL(10,2),
    screen_size DECIMAL(5,2),  -- Only for electronics
    battery_life INT,          -- Only for phones
    material VARCHAR(50),      -- Only for clothing
);

Problems:

• Many NULL values (products don't use all attributes)
• Adding new attributes requires ALTER TABLE
• Table becomes extremely wide
• Hard to manage attribute metadata

EAV Solution

EAV splits data into three components:

• Entity: The object (product, category, customer)
• Attribute: Properties of the entity (name, color, price)
• Value: Actual data stored per entity per attribute

-- Entity table (what it is)
CREATE TABLE catalog_product_entity (
    entity_id INT PRIMARY KEY AUTO_INCREMENT,
    entity_type_id SMALLINT,
    sku VARCHAR(255)
);

-- Attribute table (what properties exist)
CREATE TABLE eav_attribute (
    attribute_id SMALLINT PRIMARY KEY,
    attribute_code VARCHAR(255),
    backend_type VARCHAR(8),  -- varchar, int, decimal, text, datetime
    entity_type_id SMALLINT
);

-- Value tables (actual data, one per type)
CREATE TABLE catalog_product_entity_varchar (
    value_id INT PRIMARY KEY AUTO_INCREMENT,
    attribute_id SMALLINT,
    entity_id INT,
    value VARCHAR(255)
);

CREATE TABLE catalog_product_entity_int (
    value_id INT PRIMARY KEY AUTO_INCREMENT,
    attribute_id SMALLINT,
    entity_id INT,
    value INT
);

CREATE TABLE catalog_product_entity_decimal (
    value_id INT PRIMARY KEY AUTO_INCREMENT,
    attribute_id SMALLINT,
    entity_id INT,
    value DECIMAL(12,4)
);

Now each product only has rows for attributes it actually uses — no NULLs.

Flexibility for Merchants

Merchants need to add custom attributes without developer intervention:

Default attributes: name, sku, price, description, status
Merchant adds: eco_friendly, country_of_origin, warranty_period
Admin panel: Stores > Attributes > Product > Add New Attribute

No database schema change required — EAV stores any attribute dynamically.

Different Attribute Sets

Different product types need different attributes:

Electronics Attribute Set:
  - screen_size (decimal)
  - battery_life (int)
  - connectivity (varchar)

Clothing Attribute Set:
  - fabric (varchar)
  - pattern (varchar)
  - care_instructions (text)

Each product belongs to one attribute set,
but different sets can have completely different attributes.

Multi-Store Attribute Values

EAV supports storing different values per store view:

// Same product, different names per store
// catalog_product_entity_varchar
// entity_id=1, attribute_id=name, store_id=0 (default): 'T-Shirt'
// entity_id=1, attribute_id=name, store_id=1 (french): 'Tee-Shirt'
// entity_id=1, attribute_id=name, store_id=2 (german): 'T-Shirt'

$product->setName('T-Shirt');
$product->setData('name', 'Tee-Shirt');
// Store-specific override stored separately

Trade-offs

Entity Types

Magento defines these entity types:

SELECT * FROM eav_entity_type;
+----------------+----------------------+
| entity_type_id | entity_type_code      |
+----------------+----------------------+
|              1 | customer              |
|              2 | customer_address     |
|              4 | catalog_product       |
|              3 | catalog_category      |
|              5 | order                 |
|              7 | invoice               |
|              8 | creditmemo            |
|              9 | shipment              |
+----------------+----------------------+

Entity Tables

-- Product entities
SELECT entity_id, sku, type_id FROM catalog_product_entity LIMIT 5;
+-----------+--------+-----------+
| entity_id | sku    | type_id   |
+-----------+--------+-----------+
|         1 | SKU-01 | simple    |
|         2 | SKU-02 | simple    |
|         3 | SKU-03 | configurable |
+-----------+--------+-----------+

-- Category entities
SELECT entity_id, path, name FROM catalog_category_entity LIMIT 5;
+-----------+-------+-----------+
| entity_id | path  | name      |
+-----------+-------+-----------+
|         1 | 1     | Root      |
|         2 | 1/2   | Default   |
|         3 | 1/2/3 | Electronics |
+-----------+-------+-----------+

Value Tables by Type

-- varchar values (short text: name, sku, url_key)
SELECT * FROM catalog_product_entity_varchar 
WHERE entity_id = 1 AND attribute_id = 71;
+----------+--------------+-----------+----------+
| value_id | attribute_id | entity_id | store_id | value    |
+----------+--------------+-----------+----------+----------+
|     1234 |           71 |         1 |        0 | T-Shirt  |
+----------+--------------+-----------+----------+----------+

-- int values (status, visibility, quantity)
SELECT * FROM catalog_product_entity_int 
WHERE entity_id = 1 AND attribute_id = 97;
+----------+--------------+-----------+----------+-------+
| value_id | attribute_id | entity_id | store_id | value |
+----------+--------------+-----------+----------+-------+
|     5678 |           97 |         1 |        0 |     1 |
+----------+--------------+-----------+----------+-------+

-- decimal values (price, weight)
SELECT * FROM catalog_product_entity_decimal 
WHERE entity_id = 1 AND attribute_id = 75;
+----------+--------------+-----------+----------+--------+
| value_id | attribute_id | entity_id | store_id | value  |
+----------+--------------+-----------+----------+--------+
|     9012 |           75 |         1 |        0 | 29.9900 |
+----------+--------------+-----------+----------+--------+

Basic EAV Query (Without ORM)

-- Get a product with all its attributes
SELECT 
    p.entity_id,
    p.sku,
    MAX(CASE WHEN a.attribute_code = 'name' THEN v.value END) AS name,
    MAX(CASE WHEN a.attribute_code = 'price' THEN d.value END) AS price,
    MAX(CASE WHEN a.attribute_code = 'status' THEN i.value END) AS status
FROM catalog_product_entity p
LEFT JOIN eav_attribute a ON a.entity_type_id = 4
LEFT JOIN catalog_product_entity_varchar v ON v.entity_id = p.entity_id AND v.attribute_id = a.attribute_id AND a.backend_type = 'varchar'
LEFT JOIN catalog_product_entity_decimal d ON d.entity_id = p.entity_id AND d.attribute_id = a.attribute_id AND a.backend_type = 'decimal'
LEFT JOIN catalog_product_entity_int i ON i.entity_id = p.entity_id AND i.attribute_id = a.attribute_id AND a.backend_type = 'int'
WHERE p.entity_id = 1
GROUP BY p.entity_id, p.sku;

The N+1 Problem

// BAD: Loading attributes one by one
$products = $collection->load(); // 1 query
foreach ($products as $product) {
    echo $product->getName();     // N queries if not loaded
    echo $product->getPrice();    // N queries if not loaded
}

// GOOD: Load specific attributes
$collection->addAttributeToSelect(['name', 'price', 'sku']);
// Single query with JOINs on needed attributes only

With Index (Fast Path)

-- Index table provides flat access to EAV data
SELECT * FROM catalog_product_index_price 
WHERE entity_id = 1 AND customer_group_id = 0 AND website_id = 1;
+-----------+-------------------+-----------+-------+-------+--------+
| entity_id | customer_group_id | website_id | price | final_price | min_price |
+-----------+-------------------+-----------+-------+-------+--------+
|         1 |                 0 |         1 | 29.99 |    29.99 |    29.99 |
+-----------+-------------------+-----------+-------+-------+--------+

The index tables flatten EAV data for fast reads, which is how Magento achieves performance despite EAV complexity.