Get Free Audit

How EAV Data Storage Works in Magento 2

Jul 10, 2018

17982 Andrey Litvin

How EAV Data Storage Works in Magento 2

EAV (Entity-attribute-value) is a model of storing the values of entity attributes in a certain data storage. As a data storage, Magento 2 supports MySQL-compatible databases (like MySQL, MySQL NDB Cluster, MariaDB, Percona and others).

The principles of EAV data storage work in Magento
What happens when a new attribute is added to the system

A table of the classical EAV model has 3 columns:

  1. entity (the object to which you want to set the attribute value)
  2. attribute
  3. value

In the Flat model, the attribute values are stored in the same table as the entities, for each attribute, a separate column in the table is created.

In the EAV model, attribute values are stored in a separate table, where a separate column is not created for each attribute. For each attribute value of an entity, however, a new row is created in the EAV table.

The principles of EAV data storage work in Magento

The differences between the classic EAV model and the one used by Magento:

  1. Each attribute is assigned a backend type (varchar, int, text …).
  2. Each type of EAV entities has a separate table. The types of EAV entities are stored in the eav_entity_type table. The name of the tables in which the entities are stored in the entity_table column.
  3. System EAV entities are stored in the eav_entity table.
  4. Attributes that have the backend type as static are stored in the same table as the entity. These attributes have a global scope.
  5. Each backend type of each entity type has its own table, in which the attribute values are stored. The name of this table is constructed from the pattern {entity_table}_{backend_type}, where entity_table is the name of the entity table, and backend_type is the backend type of the attribute. These tables have the following columns: value_id (int), attribute_id (int), store_id ** (int), entity_id * (int), value (depending on the backend type).

Here is an image for illustrating the connections:

image5

In order to get/write the attribute value, you need:

  1. store_id**
  2. entity_id*
  3. entity_table or entity_type_id
  4. attribute_id
migration to magento 2

Magento 2 Migration

Take your online store to the next level with BelVG Magento 2 Migration

Visit the page

If the entity_table is unknown, but you know the entity_type_id, then the entity_table can be obtained from the eav_entity_type table.

Magento gets the attributes values of the entity with one large SQL query, which is generated using the following algorithm:

  1. Get all attribute tables for a particular entity_type.
  2. For each table perform the following:
    • A select subquery is created from the current table, which requests value and attribute_id
    • a condition is added that entity_id = ID of the requested entity*
    • a condition is added for each scope that store_id IN ($scope->getValue()) **
    • sorted by store_id in descending order
  3. UNION ALL is done for all subqueries.

* The field does not have to be called entity_id. Magento uses $metadata->getLinkField() to get the name of the field.

** The field does not have to be called store_id. Magento uses $scope->getIdentifier() to get the field name.

Magento Audit

Take your online store to the next level with BelVG Magento Audit

Visit the page

What happens when a new attribute is added to the system

When a new attribute is added, new records are created in the database. Similar records are created for each attribute type.

Let’s consider some standard types of attributes.

Text Field

Let’s take a look at the example of creating a new attribute of the Text Field type.

Andrey_Dubina
Partner With Us Let's discuss how to grow your business. Get a Free Quote.
Talk to Andrey

  1. A new entry is created in the eav_attribute table:
attribute_id entity_type_id attribute_code
154 4 test_code
  1. One entry per store view is created in the eav_attribute_label table with the Label of the attribute:
attribute_label_id attribute_id store_id value
17 154 1 Test label
  1. A new entry is created in the catalog_eav_attribute table:
attribute_id frontend_input_renderer is_global
154 NULL 1

Dropdown

image4

The differences with Text Field:

  • eav_attribute.frontend_input = “select”;
  • eav_attribute.source_model = “Magento\Eav\Model\Entity\Attribute\Source\Table”;
  • eav_attribute.backend_type = “int”;
  • eav_attribute.default_value = eav_attribute_option.option_id by default;
  • one row per value is added to eav_attribute_option.
option_id attribute_id sort_order
210 155 1
211 155 2
212 155 3
  • Rows are added to eav_attribute_option_value:
value_id option_id store_id value
208 211 0 2
210 212 0 3
205 210 1 One
207 211 1 Two
209 212 1 Three
206 210 0 1

Price

The differences with Text Field:

  • eav_attribute.frontend_input = “price”
  • eav_attribute.backend_model = “Magento\Catalog\Model\Product\Attribute\Backend\Price”
  • eav_attribute.backend_type = “decimal”

Media image

The differences with Text Field:

  • eav_attribute.frontend_input = “media_image”

Text swatch

image6

The differences with Dropdown:

  • catalog_eav_attribute.additional_data have an approximate form {“swatch_input_type”:”text”,”update_product_preview_image”:”0″,”use_product_image_for_swatch”:0}
  • Rows are added to eav_attribute_option_swatch
swatch_id option_id store_id type value
1 213 0 0 1
6 215 1 0 3
2 213 1 0 1
4 214 1 0 2
3 214 0 0 2
5 215 0 0 3

This is what EAV model is all about. Please leave your questions in the comment box below if something is unclear. The second part of the EAV topic will be published next week, so stay tuned.

magento development services

Magento Development Services

Take your online store to the next level with BelVG Magento development

Click to visit the page

Looking for an expert Magento 2 development team? Turn to BelVG

Andrey Dubina
Partner With Us Looking for a partner to grow your business? We are the right company to bring your webstore to success. Talk to Andrey

9 Comments

  1. Hi, Morten. Thanks for your question.
    If you like to use Flat method more, just use it. You can do both. In the admin panel you can switch between two modes:
    Stores / Configuration / Catalog / Catalog / Storefront / Use Flat Catalog Product (Category)

  2. Hi, Morten. Thanks for your question.
    If you like to use Flat method more, just use it. You can do both. In the admin panel you can switch between two modes:
    Stores / Configuration / Catalog / Catalog / Storefront / Use Flat Catalog Product (Category)

    However, on practice (we have checked this on the example of all large projects), switching to flat doesn’t give any significant benefits. The optimization issues always lie somewhere else.

  3. Hi

    What is the benefit with EAV over flat? would’nt fetching the data be slower, since there is gonna be alot more joining of the tables? and sometimes it is faster to reach a table with alot of columns, than fetching 3-4 tables with a little amount of data with joins…

  4. Hi, Hhosseinni! Thanks for your question!

    If someone deletes a record in table eav_entity_type, all attributes which have that entity_type_id will be removed too (there are Foreign Key in table eav_attribute on field entity_type_id which has On Delete = CASCADE).
    I have deleted catalog_product record from table eav_entity_type and cleared cache.
    Now there are an exception “Invalid entity_type specified: catalog_product”. Magento tries to load catalog_product entity type.

    In your case, you should create new entity type using Setup Scripts (in Magento < = 2.2.x, but still supported in 2.3.x) or Data Patches (in Magento >= 2.3.x).
    E.g. patch Magento\Catalog\Setup\Patch\Data\InstallDefaultCategories installs entity types catalog_category and catalog_product by running $categorySetup->installEntities(). Method installEntities calls method addEntityType which creates or updates entity type.

    You can see default catalog entities with their attributes in method Magento\Catalog\Setup\CategorySetup::getDefaultEntities();

  5. what happens if eav_entity_type is missing ?
    and how can regenerate it ? is there any way to generate it ?

    thank you and best regards,

  6. Hi, Prathap!
    Thanks for your question.

    EAV pattern was implemented in Magento for a purpose. For example, in my current project I have around 100 attributes which describe products, and I am lucky that they don’t differ much in types. EAV helps me work with them.
    Yet if the catalog is already formed and more or less permanent, then you can switch to Flat.
    Obviously, EAV and Flat have their own advantages and disadvantages, and their use is the matter of preference. As for us, we’re using both.

Post a new comment

BelVG Newsletter
Subscribe to our mailing list and get interesting stuff and updates to your email inbox.
Email *