How EAV Data Storage Works in Magento 2: Part 2

Jul 17, 20181408
How EAV Data Storage Works in Magento 2: Part 2

There is not much difference for a developer in EAV and Flat collections. The creation of models, resource models and collections is very similar. Let’s describe the key differences between EAV and flat table collections and consider creating classes for EAV (City) and Flat (Country). This is Part 2 of the EAV data storage review, check out Part 1 to catch up on the topic.

Resource models
The differences between EAV and FLAT classes
When to use EAV for a new entity
The pros and cons of EAV architecture




Resource models





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


The differences between EAV and FLAT classes

Additionally, methods that take an attribute parameter as its code or its object have been added in the EAV collection:

  • addAttributeToSelect converts the attribute into its code and calls addFieldToSelect;
  • addAttributeToFilter converts the attribute into its code and calls addFieldToFilter;
  • addAttributeToSort converts the attribute into its code and calls addOrder.

Additionally, methods for working with attributes have been added in EAV resource models, the methods load, save, delete have been redefined.

EAV uses the following classes for working with attributes:

  • Magento\Eav\Model\ResourceModel\CreateHandler — for creating values in new entities;
  • Magento\Eav\Model\ResourceModel\UpdateHandler — for modifying / deleting / adding values to existing entities;
  • Magento\Eav\Model\ResourceModel\ReadHandler — for obtaining values.

The key difference between EAV and Flat is the storage of data. Read more about it in the previous article on EAV data storage.

There are options like Use Flat Catalog Category and Use Flat Catalog Product at STORES ➜ Settings ➜ Configuration on the Admin panel. These settings allow you to change the sources from which products and categories are loaded, from the EAV tables to the FLAT index tables.

A product’s attribute falls into a flat table if it satisfies one of the conditions:

  • backend_type is specified as static;
  • a filter for this field is allowed;
  • the attribute is used in the product list;
  • the attribute is used for sorting.

The collection of products is a Magento\Catalog\Model\ResourceModel\Product\Collection class. In this class, many methods have the following form:

That is, if Flat is enabled, then one action is performed, if it is off, then the other.

It’s different with categories. The Magento\Catalog\Model\Category model is initialized as follows:

That is, different resource models are used.

Collections are also divided into 2 different classes, in contrast to the products:

  • Magento\Catalog\Model\ResourceModel\Category\Collection
  • Magento\Catalog\Model\ResourceModel\Category\Flat\Collection

When to use EAV for a new entity

I will use EAV for the entity if at least one of the conditions is fulfilled:

  1. The scope should be presented in the entity.
  2. Administrators or modules should be able to add attributes to the entity or change the backend type of attributes.
  3. Potentially the number of columns can be more than 1017 in a Flat model.
  4. The potentially required number of indexes of the INDEX type in a Flat model can become more than 64 or so many, that the operations of adding/changing/deleting entities will be too slow.

The pros and cons of EAV architecture

The advantages of EAV over Flat

  • The implementation of SCOPE in Flat models stores a lot of unnecessary information. For example, if you want to override only one attribute in another scope, both EAV and Flat will create one line. But EAV always has a fixed number of columns, and in Flat it can reach up to 1000. Additionally, a problem arises when the value is inherited from the parent scope. You can mark such values as NULL, but then you must disallow the attributes to be NULL if they are not inherited.
  • Quick adding of a new attribute. Adding a new attribute does not change the EAV table in any way. In Flat you need to add a new column for this. The ALTER TABLE operation is slow. This is especially noticeable in large tables. *
  • Faster change of the backend type of an attribute. To change the attribute type in EAV, you need to move the data of this attribute from one table to another. For Flat, you need to execute ALTER TABLE. *
  • EAV allows you to separate attribute values from the values of the fields of the entity.
  • The following InnoDB restrictions on the table limit Flat:
    • The table can contain no more than 1017 columns
    • A table can contain no more than 64 indices of the INDEX type

The disadvantages of EAV over Flat

  • Obtaining the attributes values of an entity in EAV works slower than in Flat **
  • Search by attribute value in EAV works slower than in Flat **
  • In Flat, you can create an index for several attributes to speed up the search **

* ALTER TABLE is performed while adding or changing an attribute type in EAV too if the backend type of the attribute is static.

** To speed up operations, some attributes can be set the backend type as static and, if necessary, create indexes, but then the scope of the attribute will only be global. Another option is to use a flat table as an index if it is allowed for the data to delay in actuality.

How useful was this post?

Average rating / 5. Vote count:

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

Post a new comment

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