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:

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

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.

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:

2. One entry per store view is created in the eav_attribute_label table with the Label of the attribute:

3. A new entry is created in the catalog_eav_attribute table:

Dropdown

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.

• Rows are added to eav_attribute_option_value:

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

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

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

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