Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 42 ยท Data and the Database

EAV: Entity-Attribute-Value

Understand Magento's EAV model: attribute tables, value tables by type, catalog_product_entity, store scope, custom attributes via patches and the trade-offs.

Open the database of a Magento store and look for a table with a product's name, price and description in columns. You will not find one. Products live in catalog_product_entity, which holds little more than an id, a SKU and a type. The name is in one table, the price in another and the description in a third. This layout is called EAV: Entity, Attribute, Value. It is the reason a merchant can add a new product field in the admin without a developer running ALTER TABLE.

In this lesson you will learn how the pieces fit together, how store scope lets one product have different values per store view, how developers add attributes with a data patch, and what EAV costs you in performance. The real Magento code and SQL are read-only here, because Magento cannot run on this site. The runnable examples are plain PHP stand-ins on SQLite that show the mechanism, not Magento's actual classes. If you are new to the data layer, read Models, Resource Models and Collections first.

The three parts of EAV

An EAV design splits a record into three kinds of rows:

  • Entity: the thing itself, such as one product. The entity table has only the columns every entity needs.
  • Attribute: a definition of one field, such as name or weight: its code, its data type, how it is edited.
  • Value: the actual data, stored one value per row, linked to an entity and an attribute.

Compare a classic table with the EAV equivalent:

TEXT
Classic:   product(id, sku, name, price, weight, color)
EAV:       entity(id, sku)
           attribute(id, code, backend_type)
           value(entity_id, attribute_id, value)

Adding color to the classic design means a schema change. In EAV it means one new row in the attribute table. That flexibility is the entire point, and it is why a catalog with thousands of different attributes across product types (a T-shirt has a size, a laptop has a screen size) is practical in Magento.

Magento's tables

Magento does not use a single value table. It stores values in one table per data type, so each value column has a proper SQL type and can be indexed sensibly. For products the names look like this:

Table Holds
catalog_product_entity One row per product: entity_id, attribute_set_id, type_id, sku, timestamps
eav_entity_type The kinds of entity that use EAV (product, category, customer, customer address)
eav_attribute One row per attribute: attribute_code, entity_type_id, backend_type, frontend_input
catalog_eav_attribute Catalog-only settings for an attribute: searchable, filterable, used in listing
catalog_product_entity_varchar Short text values (for example name, url_key)
catalog_product_entity_int Integer values (status, visibility, many dropdown options)
catalog_product_entity_decimal Numbers with decimals (price, weight)
catalog_product_entity_text Long text (description)
catalog_product_entity_datetime Dates (special price from/to)

The backend_type column in eav_attribute decides which value table an attribute uses: varchar, int, decimal, text, datetime, or static. A static attribute is stored as a real column on the entity table itself. The sku is the typical example, because it is needed constantly and is looked up directly.

Every value row has the same shape: a value_id, an attribute_id, a store_id, the entity's id, and the value. Categories and customers follow the same pattern with their own table families, such as catalog_category_entity_varchar and customer_entity_varchar. Orders, quotes and invoices are not EAV: they are flat tables. The Adobe Commerce edition adds content staging, which changes the linking column on the product tables from entity_id to row_id. The principle is unchanged.

flowchart LR
    E["catalog_product_entity<br/>entity_id, sku, type_id"] --- V1["catalog_product_entity_varchar"]
    E --- V2["catalog_product_entity_decimal"]
    E --- V3["catalog_product_entity_int"]
    A["eav_attribute<br/>code, backend_type"] -. "attribute_id" .-> V1
    A -. "attribute_id" .-> V2
    A -. "attribute_id" .-> V3

An entity row is linked to one value table per data type; the attribute table says which value table each attribute uses.

A mini EAV store you can run

The stand-in below builds the same idea on SQLite. It has an entity table, an attribute table with a backend_type, and one value table per type. save() looks up each attribute, picks the matching value table and writes one row. load() reads all values back with one query per type, the way a naive implementation would. Press Run and watch the rows.

PHP A tiny EAV store on SQLite

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

One product with three attributes became one entity row plus three value rows spread over three tables. Magento's real resource models do the same thing with more care: they load all attributes of an entity in a few queries, cache attribute metadata so the eav_attribute lookups are not repeated, and handle many more edge cases.

Attribute sets and groups

Not every product needs every attribute. Magento groups attributes into attribute sets (for example "Default" or "Laptop"), and each set is split into attribute groups, the collapsible sections of the product edit form. A product points to one attribute set through attribute_set_id. The tables involved are eav_attribute_set, eav_attribute_group and eav_entity_attribute, which assigns an attribute to a set and a group. Changing a set in the admin changes which fields appear in the form, not the structure of the value tables.

Store scope

Magento is built for several websites, stores and store views, often in different languages. The store_id column in each value table makes one product carry different values per scope:

  • store_id = 0 is the default (admin) value, used when nothing more specific exists.
  • store_id = 1, 2, and so on, override it for one store view.

When the storefront reads the product name for store view 2, Magento effectively asks: "use the row for store 2 if it exists, otherwise fall back to the row for store 0". Whether an attribute may differ per store view is a property of the attribute, its scope: global, website or store view. A price can be global or website-wide, while a name or description is usually store view scope, since translations need it.

The stand-in below adds a store_id column and resolves values with a fallback. It prints the name for the default scope and for two store views, one of which has a translated override.

PHP Store scope with default fallback

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Store view 1 has no row of its own, so it falls back to the default. Only the German view stores an override, which keeps the tables small. The real implementation does this fallback with a LEFT JOIN of the store row onto the default row and an IFNULL, which is why catalog queries can have many joins.

Adding an attribute with a data patch

In real Magento, a custom product attribute is created by a data patch (see Data Patches and Schema Patches). The patch gets an EavSetupFactory, builds an EavSetup and calls addAttribute(). This code is read-only on this site:

PHP
namespace Acme\Badge\Setup\Patch\Data;

use Magento\Catalog\Model\Product;
use Magento\Eav\Model\Entity\Attribute\ScopedAttributeInterface;
use Magento\Eav\Setup\EavSetupFactory;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\Patch\DataPatchInterface;

class AddBadgeAttribute implements DataPatchInterface
{
    public function __construct(
        private ModuleDataSetupInterface $moduleDataSetup,
        private EavSetupFactory $eavSetupFactory
    ) {
    }

    public function apply(): self
    {
        $this->moduleDataSetup->getConnection()->startSetup();
        $eavSetup = $this->eavSetupFactory->create(['setup' => $this->moduleDataSetup]);

        $eavSetup->addAttribute(Product::ENTITY, 'acme_badge', [
            'type' => 'varchar',
            'label' => 'Badge',
            'input' => 'text',
            'required' => false,
            'global' => ScopedAttributeInterface::SCOPE_STORE,
            'visible' => true,
            'user_defined' => true,
        ]);

        $this->moduleDataSetup->getConnection()->endSetup();
        return $this;
    }

    public static function getDependencies(): array { return []; }
    public function getAliases(): array { return []; }
}

After bin/magento setup:upgrade, the attribute exists in eav_attribute, and its values go to catalog_product_entity_varchar. Adding it to an attribute set (so it shows in the admin form) is a further step, done with addAttributeToSet() or by the admin user. Prefix custom attribute codes with your vendor name to avoid clashes with other modules. The exact option keys differ a little between attribute kinds and versions, so check the core patches in Magento_Catalog for the kind you need.

Notice what did not happen: no ALTER TABLE ran on any product table. The patch only inserted metadata and, later, value rows will appear on demand.

The cost of flexibility

EAV is a trade: you gain schema flexibility and lose query simplicity and speed.

  • Many joins. Reading ten attributes of a product can mean ten joins, one per attribute, or several queries. A classic table returns the same data from one row.
  • Hard to filter and sort. Filtering by color and price means joining two value tables and comparing there. Value tables have no useful single index for "all products where weight is under 2".
  • Row explosion. Millions of products times dozens of attributes times store views gives hundreds of millions of rows.
  • Awkward SQL by hand. Writing reports with raw SQL is tedious, so developers are expected to use collections and the repository layer.

Magento answers these costs with indexers. Instead of querying EAV tables for every storefront request, Magento precomputes flattened or specialized tables, such as price and category-product indexes, and the search index (OpenSearch) holds the data used for listing and filtering. An older flat catalog option denormalizes products into catalog_product_flat_* tables; it is deprecated, so do not build new work around it. Indexers are covered in Indexers and Caching.

Practical advice follows from this. Do not load a product collection with every attribute, because each addAttributeToSelect('*') pulls in all of them. Select only what you need, as shown in Collections, Filters and SearchCriteria. Do not write directly into value tables from custom SQL: go through the repository or resource model, so that indexers and caches are told that data changed.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Where does Magento store a product's name, which is a varchar attribute?

    The product table holds only core columns. The name is a row in catalog_product_entity_varchar, and the eav_attribute row for name says which value table to use.
  2. 2. What does store_id = 0 mean in a value table?

    Store 0 is the default scope. A store view row, when present, overrides it. When absent, the default value is used.
  3. 3. Which statement about adding a custom product attribute is correct?

    A data patch calls addAttribute(), which writes metadata. The product table's structure does not change, which is the main benefit of EAV.
  4. 4. Why does Magento use indexers on top of EAV?

    EAV is flexible but slow for listing and filtering. Indexers precompute data in forms that are cheap to read.

Summary

  • EAV splits a record into an entity row, attribute definitions and value rows, so new fields do not need schema changes.
  • Magento stores values in one table per data type (varchar, int, decimal, text, datetime); backend_type picks the table, and static attributes are real columns on the entity table.
  • Products, categories and customers are EAV; orders and invoices are flat tables.
  • store_id gives per-scope values: store 0 is the default and store view rows override it.
  • Custom attributes are created by data patches with EavSetup::addAttribute(), and assigned to attribute sets separately.
  • EAV costs joins and write amplification, so Magento relies on indexers, collections with selected attributes, and the repository layer.

Next: System Configuration (system.xml)