In this publication, I will review Magento 2 data structures that support a concept such as EAV . Developers sometimes need to get out of the wilds of code and try to survey their places of life from the height of an eagle's flight - this allows you to focus on things that are really important or just big. So I got out.

The acronym EAV is disclosed as Entity - Attribute - Value (this is for those who did not follow the link above). The main “ benefit ” of EAV is the efficient use of the database space in cases where the possible number of different attributes (properties, parameters) that can be used to describe things (entities) is very wide, but the number of attributes, which actually refers to individual object is relatively small. A good example of such a case in e-commerce is the concept of “product” - the significant attributes of the products “ TV ” (screen size) differ from the significant attributes of the products “ sleeping bag"(minimum comfortable temperature).

So what does Magento 2 offer for storing data in EAV format?

'eav_' namespace

In the freshly deployed Magento 2.3 database, there are 21 tables with a prefix eav_. All of them can be divided into three groups:

• eav_attribute
• eav_entity
• eav_form

The easiest way is with eav_form- these tables relate to the display of some EAV data on the UI and do not relate directly to the placement of EAV data in the database (I consider only data structures and only from the point of view of information storage, not its display). For the experiment, I deleted the space tables eav_formfrom the database and this did not stop me from placing an order in the store. So you still need to look for where the data from this table space is used and how much it is needed for Magento to function.

Of the remaining two, the group eav_attributerefers to the letter A (ttribute) , and the group eav_entityto the letter E (ntity) . Where is the letter V (alue) ?

Values ​​for entity attributes must be searched in the table name suffixes:

• _datetime
• _decimal
• _int
• _text
• _ varchar

You can see that tables that start with:

• catalog_category_entity_
• catalog_product_entity_
• customer_address_entity_
• customer_entity_
• eav_entity_

A simple multiplication of the number of suffixes (5) by the number of prefixes (5) gives us the total number of tables (25) in which the storage of values- data is supposed.

'eav_entity_type': entity type registry

The beginning of EAV in Magento needs to be looked for in the table eav_entity_type. This is where it is set for what types of entities attribute values ​​will be stored in the EAV structure. So, initially Magento 2.3 offers this option for the following eight entities:

• customer
• customer_address
• catalog_category
• catalog_product
• order
• invoice
• creditmemo
• shipment

'eav_attribute': attribute registry

The next step is to find out what attributes these types of entities can characterize. This information is in the table eav_attribute. The attribute registry has a closure on the register of entity types by foreign key . Initially, the attribute registry contains 135 entries belonging to 4 types of entities:

• customer
• customer_address
• catalog_category

• catalog_product

  
  

  What is this talking about? Well, at least that other types of entities:

  
  
• order
• invoice
• creditmemo
• shipment

Do not use the EAV structure to store data. That is, at some stage, enthusiasm was present and the use of EAV was planned for eight types of entities, but in fact they stopped at 4.

'eav_entity_': ghost space

The table space eav_entityresembles Chinese ghost cities - out of 9 tables of space, data is contained in only two:

• eav_entity_type: this is a register of entity types that I mentioned above;
• eav_entity_attribute: used to organize attributes in groups (closer to the display of data than to their storage); this information relates more directly to the attributes themselves than to the entities (i.e., obviously not from this parish - it has a place in space eav_attribute_);

The remaining 7 tables are empty:

• eav_entity
• eav_entity_datetime
• eav_entity_decimal
• eav_entity_int
• eav_entity_store
• eav_entity_text
• eav_entity_varchar

Very similar to attempt unification process for storing values of the entities attributes into a set of tables ( datetime, decimal, int, text, varchar), instead of having 5 suffixes with corresponding tables for each type of entity. On an unsuccessful attempt? Or is it the future of EAV in Magento?

Anyway, The earth was formless and empty, and darkness over the abyss, and the Spirit of God these tables are not originally used in the present.

Attribute Value Types

The following table eav_entity_typeidentifies the types of entities in the table eav_attributeset themselves the attributes and link them to the appropriate types of entities. And how to determine where to look for the value for such an attribute of such an entity?

The field will help us with this eav_attribute.backend_type. It shows where the attribute values ​​are stored:

• static : in a table with data about the entity itself (for example, values ​​for attribute # 9 - customer.email, you need to look in the client table customer_entityin the column email);

For the remaining types, the values ​​are stored in separate tables, in the names of which the prefix corresponds to the entity type ( customer_, ...) and the suffix to one of the data types:

• datetime
• decimal
• int
• text
• varchar

That is, the values ​​for type attribute # 79 catalog_product.special_from_dateare datetimestored in the table catalog_product_entity_datetime. Values ​​for attribute # 77 catalog_product.priceare in the table catalog_product_entity_decimal.

What interesting things can be seen in the table eav_attributein connection with the types of values? As I noted above, this table describes the attributes for only 4 of the 8 types of entities registered in eav_entity_type. Moreover, for entities of the type customerand customer_addressall attributes that were initially defined, they have a value type static- that is, they are ordinary columns in the table and do not take advantage of the EAV approach. Tables:

• customer_entity_datetime
• customer_entity_decimal
• customer_entity_int
• customer_entity_text
• customer_entity_varchar
• customer_address_entity_datetime
• customer_address_entity_decimal
• customer_address_entity_int
• customer_address_entity_text
• customer_address_entity_varchar

are initially empty and can only be used programmatically (i.e., through the admin panel, without third-party extensions, there is no way to write anything to these tables).

EAV for categories

Catalog categories - this is the first entity that more or less uses the EAV approach in Magento. Entity type - catalog_categoryjust initial attributes - 30, from which the non-static - 26. That is, only 4 values of attributes ( children_count, level, path, position) are stored in the table catalog_category_entity, the other tables are stored in the set catalog_category_entity_[ datetime| decimal| int| text| varchar].

The structure of the tables from this set is very similar both to each other and to similar tables of other types of entities (clients, their addresses, etc.):

CREATETABLE`catalog_category_entity_datetime` (
  `value_id`int(11) NOTNULL AUTO_INCREMENT,
  `attribute_id`smallint(5) unsignedNOTNULLDEFAULT'0',
  `store_id`smallint(5) unsignedNOTNULLDEFAULT'0',
  `entity_id`int(10) unsignedNOTNULLDEFAULT'0',
  `value` datetime DEFAULTNULL,
  PRIMARY KEY (`value_id`),
  UNIQUEKEY`...` (`entity_id`,`attribute_id`,`store_id`),
  ...
) ...

For any type of stored values ( datetime, decimal, int, text, varchar) varies only type column value. This structure allows you to save a separate value ( value) of a separate attribute ( attribute_id) of a separate entity ( entity_id) for a separate storefront ( store_id).

In connection with the architectural features of Magento, an additional connection with the shop window is added store_id. Thus, it is possible to localize the values ​​of the same attribute of the same entity for different storefronts. Catalog categories are the first entities in Magento for which you can use the EAV subsystem directly out of the box. You can set values ​​for directory attributes through the admin panel.

You can not only give different values ​​for text attributes, translating into the language of the corresponding storefront, but also localize attributes of other types. For example, on the eve of the Christmas holidays on ru storefront for the attribute, catalog_category.custom_design_fromyou can set the values ​​on January 7 of next year, and on en storefront on December 24 of this.

EAV for products

In general, this is the same type of entity for which EAV was launched in Magento. Entity type - catalog_product, of the total initial attributes - 63, of which non-static - 56. The structure of tables supporting EAV for products is similar to the structure of tables for directories. But there is one significant difference. For products, you can create new attributes through the admin panel - this is the default Magento functionality out of the box. If Magento provides only EAV data structures for other entities based on their software filling, then for products an interface is implemented that allows you to do this at the user level (store manager) - Stores / Attributes / Product .

For the products, there are two more tables related to EAV:

• eav_attribute_set
• eav_attribute_group

By and large, they are more likely to display information than to store it. Product attributes are combined into sets ( set) and when creating a product, a set of attributes is assigned to it, which allows you to select a set of attributes that relates specifically to household appliances (or even “TVs” for a product group) when filling out a product card for, for example, a TV. Merging attributes into sets occurs in the Stores / Attributes / Product / Attribute Set :

Total

IMHO, Magento is a good example of the fact that the appropriateness of using EAV is quite narrow. When bookmarking the use of EAV for 8 entities ( eav_entity_type), EAV notation is used only for 4 entities ( eav_attribute), of which only 2 entities have truly EAV attributes - catalog_categoryand catalog_product. Moreover, for catalog_categoryEAV-attributes are used not for its intended purpose (a large number of different attributes to describe the entity with a small number of attributes related to a single instance ), but for "showcase localization" of attribute values ​​( the same set of attributes for an entity " catalog category "with the option of an attribute of an attribute to have different values ​​for different storefronts ).

The full use of EAV is used only for catalog_product(though there is also an admixture of "storefront localization", but this is an extension of the EAV model, and not its profanation, as is the case with categories). But with products Magento reveals EAV in full - Magento-application can be safely used to demonstrate the principles of EAV.