Magento 2 EAV Model – Things You May Not Know

Magento 2 EAV Model is a valuable feature. You should pay more attention to it.

Now, it’s so glad to come back and continue providing all of you with more useful Magento 2 tutorials from now on. 

Today, we will reveal a complete tutorial of the Magento 2 Entity-Attribute-Value model (Magento EAV), which is essential for every developer to build products or do projects at a higher customization level. This article mainly concentrates on explaining seven concerns as below:  

1. Why Is Magento 2 EAV Model?

The EAV database structure is employed because it is substantially more scalable than a normalised database structure. Without changing the basic database structure, developers can add properties to any entity (product, category, customer, order, etc.).

As can be seen from the above image, this is the most simple and less flexible data structure between catalog and products of a store (Flat model). All products are stored in the product table.

It doesn’t matter at all if the store owner just sells one product containing some attributes. In case the store owner widens the business scope by selling more products and each of them includes specific attributes (color, size, height,…), this simple structure still meets usage demand and data organization to implement necessary queries.

However, if you use this structure to add a new attribute for the product, you have to change much database and code (add more columns or create a new table to save attributes).

Furthermore, there are unnecessary attributes you don’t need to enter data for a product, causing a larger number of columns without data that is no need to apply to the current product. 

⇒ Let’s take an example for easier understanding: 

A website is selling clothes and wants to widen business by providing drinks for the market also. Hence, it is essential to add other attributes for describing drinks such as calories, carbohydrate amount,…). To have this data, we need to follow one of the two following methods: 

• Method 1: Create more columns in the catalog table which are not applied to Clothes products.
• Method 2: Create more tables for each attribute (Calories table, for instance). As a result, a product needs so many tables to describe it and you have to modify the query logic to get data for each corresponding attribute. 

Therefore, Magento came up with the EAV model as an optimized solution for such problems. 

2. Magento EAV Model Definition 

EAV stands for Entity-Attribute-Value, a data model to encode entities in a space-efficient manner where the number of attributes (properties, parameters) that can be used to describe them is potentially vast. Still, the number that will apply to a given entity is relatively modest. Such entities correspond to the mathematical notion of a sparse matrix. (Wikipedia)

In which: 

• Entity: Magento data objects such as products, categories, customers, and orders are represented as entities. In the database, each entity (product, category, etc.) will have its own entity record.
• Attribute: An attribute is a data piece that belongs to a specific entity. The product entity, for example, has attributes such as name, price, and status, among others.
• Value: The value is the most straightforward to comprehend, as it is simply a value associated with an attribute. Consider the product entity for a better understanding. A set of properties will be assigned to each product entity, one of which is the name attribute. The name attribute will then have a value for each product (and all other attributes).

Continue reading if you’re not sure what I’m talking about.

The EAV attribute Magento 2 model will divide data into various tables corresponding to attribute types. As mentioned above, this data organization is similar to the matrix method: each couple of attributes is the description of an entity and is placed in a row of the EAV table. 

Magento EAV works based on the three main tables: 

• Entity table 
• Attribute table
• Value table 

In case you want to add more attributes, you just need to put these attributes to a table, including all declared attributes in advance. For example, you add first_name and last_name attributes to the eav_attribute like the following image: 

Also, the Magento EAV model supports storing data corresponding to each store, via the relationship between attribute data tables and store tables. When you desire to add attribute data for each store, you just need to add store_id for that attribute data.

⇒ Comparison between the simple structure (Flat model) and EAV model: 

• In the Flat model, the attribute values and the entities are both stored in the same table. A new column in the table is inserted for each attribute. 
• In the Magento EAV model, the attribute values are stored in a specific table in which there is no column created for each attribute. A new row is now added in the EAV model for each attribute value of an entity. 

3. Benefits Of Magento 2 EAV Model  

EAV model helps to resolve a lot of issues related to designing databases for websites, especially when you want to manage, modify or create new attributes for entities: 

• Easily create new attributes and manage adjustment. If you desire to add attributes for the entity, there is no need to modify table structures, but the only thing you should do is to add new data to available tables. 
• Support managing and storing attribute data based on multi-nation, multi-languages, corresponding to Store or Website.
• Have higher customization than Flat data structure.

4. Structure Of Magento 2 EAV Tables And Data 

Let’s take a look at the following diagram of catalog_product eav to know how Magento builds the EAV system: 

• catalog_product_entity: determines entity containing attribute data (with attribute type as static)  – It is equivalent to the product table. 
• eav_attribute: is the table of attribute information.
• catalog_product_entity_datetime, catalog_product_entity_decimal, catalog_product_entity_int, catalog_product_entity_text, catalog_product_entity_varchar: contains attribute values – It is equivalent to attribute_value table.
• Besides, when you add an attribute, it is put to the default attribute set and attribute group according to the information in the table (if this attribute is not declared):

+ eav_entity_type: stores information of the attribute model, default attribute set, entity table,…   

+ eav_attribute_set: stores attribute sets.

• eav_attribute_group: stores attribute groups and connects with eav_attribute_set via a foreign key (attribute_set_id).

+ eav_entity_attribute: stores attribute information of attribute set and attribute group. It connects with the eav_attribute table via attribute_id and the eav_attribute_group table via a foreign key (attribute_group_id).

• An attribute set contains a lot of various attribute groups. Let’s take an example about the default attribute set of products to help you get more understanding: 

As you can see, the default attribute set of products includes many attribute groups like product details, content, bundle items, and so on. Children attributes are also covered in these groups. You can assign/remove attributes to/from groups and create a specific attribute set for each entity as well. This helps to solve the problem when you want to assign attributes for each entity or entity with the same attribute.

5. EAV Entity Type In Magento 2 

Magento 2 divides entity into 8 types as below: 

• customer – Entity Id = 1
• customer_address – Entity Id = 2
• catalog_category – Entity Id = 3
• catalog – Entity Id = 4
• order – Entity Id = 5
• invoice – Entity Id = 6
• creditmemo – Entity Id = 7
• shipment – Entity Id = 8

(You can see in the entity_value_type table)

Each entity type has its own table. For example: customer – Entity consists of customer_eav_attribute, customer_eav_attribute_website, customer_entity, customer_entity_datetime, customer_entity_decimal, customer_entity_int, customer_entity_text, customer_entity_varchar (each table is corresponding to each attribute). 

⇒ How to create a new Entity type?

We will create a new entity type as a banner by using a simple module: 

• Firstly, get the source code: https://github.com/thanhnguyenbss/bss-new-entity-m2
• Then run the command: 

php bin/magento setup:upgrade
php bin/magento setup:static-content:deploy
php bin/magento cache:flush

Results are displayed as in the following three images: 

6. Create Magento EAV Attributes 

We take a simple example to help you create eav attributes for the entity type: catalog. (you do similarly for other entity types)

6.1. Create via Setup Script 

Magento EAV attribute can be created via the two main setup files as InstallData and UpgradeData. This is a code snippet to create via InstallData by adding a file: Vendor_Module\Setup\InstallData.php

<?php
namespace Vendor\Module\Setup;
use Magento\Framework\Setup\InstallDataInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;
/**
 * Class InstallData
 * @package Vendor\Module\Setup
 */
class InstallData implements InstallDataInterface
{
    /**
     * @var \Magento\Eav\Setup\EavSetupFactory
     */
    private $eavSetupFactory;
    /**
     * InstallData constructor.
     * @param \Magento\Eav\Setup\EavSetupFactory $eavSetupFactory
     */
    public function __construct(\Magento\Eav\Setup\EavSetupFactory $eavSetupFactory)
    {
        $this->eavSetupFactory = $eavSetupFactory;
    }
    /**
     * @param ModuleDataSetupInterface $setup
     * @param ModuleContextInterface $context
     *
     * @SuppressWarnings(PHPMD.UnusedFormalParameter)
     */
    public function install(
        ModuleDataSetupInterface $setup,
        ModuleContextInterface $context
    ) {
        $setup->startSetup();
        $eavSetup = $this->eavSetupFactory->create();
        $eavSetup->addAttribute(
            \Magento\Catalog\Model\Product::ENTITY,
            'simple_test',
            [
                'type' => 'text',
                'group' => 'Product Details',
                'label' => 'Bss Simple Test',
                'input' => 'text',
                'backend' => '',
                'frontend' => '',
                'required' => false,
                'global' => \Magento\Eav\Model\Entity\Attribute\ScopedAttributeInterface::SCOPE_GLOBAL,
                'is_used_in_grid' => false,
                'is_visible_in_grid' => false,
                'is_filterable_in_grid' => false,
                'visible' => false,
                'user_defined' => false,
                'searchable' => true,
                'filterable' => true,
                'comparable' => true,
                'filterable_in_search' => true,
                'is_html_allowed_on_front' => false,
                'used_for_promo_rules' => true,
                'visible_on_front' => false,
                'used_for_sort_by' => true,
                'unique' => false,
                'default' => '',
                'used_in_product_listing' => true,
     'source' => '',
     'option' => '',
                'sort_order' => 10,
                'apply_to' => 'simple,configurable,virtual,bundle,downloadable,grouped'
            ]
        );
        $setup->endSetup();
    }
}

6.2. Create via Declarative Schema

In Magento 2.3 and above, you can create attributes via DataPatch.  For example, create a file: Vendor_Module\Setup\Patch\Data\ApplyNewAttribute.php

<?php
namespace Vendor\Module\Setup\Patch\Data;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\Patch\DataPatchInterface;
use Magento\Framework\Setup\Patch\PatchVersionInterface;
use Magento\Eav\Setup\EavSetup;
use Magento\Eav\Setup\EavSetupFactory;
/**
 * Class ApplyNewAttribute
 * @package Vendor\Module\Setup\Patch\Data
 */
class ApplyNewAttribute implements DataPatchInterface, PatchVersionInterface
{
    /**
     * @var ModuleDataSetupInterface
     */
    private $moduleDataSetup;
    /**
     * @var EavSetupFactory
     */
    private $eavSetupFactory;
    /**
     * ApplyNewAttribute constructor.
     *
     * @param ModuleDataSetupInterface $moduleDataSetup
     * @param EavSetupFactory $eavSetupFactory
     */
    public function __construct(
        ModuleDataSetupInterface $moduleDataSetup,
        EavSetupFactory $eavSetupFactory
    ) {
        $this->moduleDataSetup = $moduleDataSetup;
        $this->eavSetupFactory = $eavSetupFactory;
    }
    /**
     * {@inheritdoc}
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
     */
    public function apply()
    {
        /** @var EavSetup $eavSetup */
        $eavSetup = $this->eavSetupFactory->create(['setup' => $this->moduleDataSetup]);
        $eavSetup->addAttribute(
            \Magento\Catalog\Model\Product::ENTITY,
            /**
Add your attribute here.
*/
……….
       );
        $setup->endSetup();
    }
}

6.3. More explanation of Data types and their meanings

Options are required to declare: attribute_code, and type.  Other options can be declared or not, depending on your usage purposes.  For options with values (Yes/No), the default value is False if you don’t declare. 

• type: define format of attributes (static, varchar, int, decimal, datetime). Static type is a little bit different because attribute data with this input type is stored in the main table (catalog_product_entity, customer_entity, customer_address_entity, …), not in the text, varchar, decimal, int, datetime tables.
  
• group: define the group of attributes (see more in the  eav_attribute_group table). Magento 2 classifies attributes into attribute groups and attribute sets corresponding to product lines or each product. If you don’t declare an attribute, it will belong to the default group depending on attribute types.
• label: specify the display label of the attribute.
• input: text, int, select, multiselect, date, hidden, boolean, multiline,…
• backend: another name is Backend Model which is used to modify attribute data when saving attribute, including validate, afterLoad, beforeSave, afterSave, beforeDelete, afterDelete (see more at Magento\Eav\Model\Entity\Attribute\Backend\AbstractBackend).
• frontend: you use it to change data as you want after getting data from database ( see more at Magento\Eav\Model\Entity\Attribute\Frontend\AbstractFrontend).
• required: there are two values as True and False.
• global: specifies the scope of the attribute. 3 scopes as website, store, global are declared at \Magento\Eav\Model\Entity\Attribute\ScopedAttributeInterface. The default scope is global.
• is_used_in_grid: enter True or False to determine whether the attribute uses grid or not (depending on entity types to have corresponding grids).
• is_visible_in_grid: enter True or False to determine whether the attribute is visible in the grid or not (depending on entity types to have corresponding grids).
• is_filterable_in_grid: enter True or False to determine whether the attribute is filterable in the grid or not (depending on entity types to have corresponding grids).
• visible: enter True or False to determine whether the attribute is visible in both frontend and backend or not.
• user_defined: enter True or False to determine whether the admin can change/remove the attribute or not.
• searchable: enter True or False to determine whether customers can use attribute for searching in the frontend or not.
• filterable: enter True or False to determine whether customers can use attribute for filtering in the frontend or not.
• comparable: enter True or False to determine whether customers can use attribute for comparing in the frontend or not.
• filterable_in_search: enter True or False to determine whether the attribute is used to filter in the frontend layer navigation or not. (only applying to attributes with input type as int).
• is_html_allowed_on_front: enter True or False to determine whether the attribute value is formatted by HTML tags or not.
• used_for_promo_rules: enter True or False to determine whether the attribute is used in promotion rules or not.
• visible_on_front: enter True or False to determine whether the attribute is shown in the Additional Information tab of the frontend product page or not. (only applying to Simple Product or Virtual Product and also depending on visible mentioned above).
• used_for_sort_by: enter True or False to determine whether the attribute is used to sort on the category page or not.
• unique: enter True or False to determine whether the attribute value is unique (no repeat) or not.
• default: the default value of the attribute. After saving, if the attribute has no value, it takes this default value.
• used_in_product_listing: enter Yes or No (Yes: the attribute is added when loading the collection of product, category,…)
• source: it is used to change display data of the attribute. (mainly change attribute options for input types as select, multiselect, dropdown, radio – you need to use class to modify).
• option: you can use option as the way you use source, but you need to declare option in the setup file.  For example: ‘option’ => [‘values’ => [‘Test1’, ‘Test2’, ‘Test3’]]
• sort_order: the order of the current attribute versus others
• apply_to: determine which product type to apply

7. Limitation Of Magento EAV Model 

Although the EAV model is beneficial, it still has some drawbacks such as: 

• Data is fragmented. When searching or getting attribute data, you must implement many queries into various tables to receive the last result. As a consequence, queries become more complicated and take you longer to process. 

To prove it, we take an example to get information such as status, name, price, and description (knowledge of product with ID = 6). 

SELECT
 IF(
   t_s.value_id IS NULL, t_d.value, t_s.value
 ) AS `value`
FROM  `catalog_product_entity_int` AS `t_d`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = t_d.entity_id
 LEFT JOIN `catalog_product_entity_int` AS `t_s` ON t_s.attribute_id = t_d.attribute_id
 AND t_s.entity_id = t_d.entity_id
 AND t_s.store_id = 1
WHERE (e.entity_id IN (6) )
 AND ( t_d.attribute_id IN ( '97'))
 AND (t_d.store_id = IFNULL(t_s.store_id, 0))
UNION ALL
SELECT
 IF(
   t_s.value_id IS NULL, t_d.value, t_s.value
 ) AS `value`
FROM  `catalog_product_entity_varchar` AS `t_d`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = t_d.entity_id
 LEFT JOIN `catalog_product_entity_varchar` AS `t_s` ON t_s.attribute_id = t_d.attribute_id
 AND t_s.entity_id = t_d.entity_id
 AND t_s.store_id = 1
WHERE ( e.entity_id IN (6) )
 AND (t_d.attribute_id IN ('73'))
 AND ( t_d.store_id = IFNULL(t_s.store_id, 0) )
UNION ALL
SELECT
 IF(
   t_s.value_id IS NULL, t_d.value, t_s.value
 ) AS `value`
FROM `catalog_product_entity_decimal` AS `t_d`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = t_d.entity_id
 LEFT JOIN `catalog_product_entity_decimal` AS `t_s` ON t_s.attribute_id = t_d.attribute_id
 AND t_s.entity_id = t_d.entity_id
 AND t_s.store_id = 1
WHERE ( e.entity_id IN (6))
 AND (t_d.attribute_id IN ('77'))
 AND ( t_d.store_id = IFNULL(t_s.store_id, 0))
UNION ALL
SELECT
 IF(
   t_s.value_id IS NULL, t_d.value, t_s.value
 ) AS `value`
FROM  `catalog_product_entity_text` AS `t_d`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = t_d.entity_id
 LEFT JOIN `catalog_product_entity_text` AS `t_s` ON t_s.attribute_id = t_d.attribute_id
 AND t_s.entity_id = t_d.entity_id
 AND t_s.store_id = 1
WHERE ( e.entity_id IN (6))
 AND ( t_d.attribute_id IN ('75'))
 AND ( t_d.store_id = IFNULL(t_s.store_id, 0))

⇒ Result is: 

• More complex queries require more processing time, leading to more waiting time to load a page and worse site performance. When you have only one website with a few products, the impact on site performance maybe not be clear. However, if you have multi-websites with thousands of products, it is really a big problem you need to solve. 

To overcome these drawbacks, Magento 2 comes up with a new solution by combining data into a Flat table: catalog_product_flat, for example.(When you enable Use Magento 2 Flat Catalog Product config at Store ⇒ Configuration ⇒Catalog ⇒Catalog ⇒Storefront). 

In this table, attribute data is updated/inserted when you run index (via cron or cli) or save the product, depending on the Product Flat Data config in Index Management. Hence, you just need to implement queries to get data via the catalog_product_flat table. And the query is now much more straightforward:

SELECT
 1 AS `status`,
 `e`.`name`,
 `e`.`price`,
 `e`.`description`
FROM
 `catalog_product_flat_1` AS `e`
WHERE `e`.`entity_id` = 6

⇒ Result is: 

Nonetheless, using Flat tables has its limitations: indexing is quite complex and may take a long time to reindex data for Entity.  Also, changing the logic of the Index is a difficult issue that needs more time to research. If you have any ideas or solutions for this problem, feel free to comment under this article!!! 

Conclusion

We believe you have got so much helpful information about the Magento 2 EAV model in this article, haven’t you? Let’s share it with more and more developers to spread our knowledge, contributing to our skill improvement day by day!

BSS Commerce is one of the leading Magento extension providers and Magento 2 development services in the world. With experienced and certified Magento developers, we commit to bringing high-quality products and services to optimize your business effectively. Furthermore, we offer FREE Installation – FREE 1-year Support and FREE Lifetime Update for every Magento extension.

CONTACT NOW to let us know your problems. We are willing to support you every time.

Nguyen Tan Thanh

+ posts

• Do You Know How to Master Magento 2 Knockout JS? (Part 2)
• Unlock 11 Necessary Design Patterns for Every Magento 2 Developer
• Knockout JS 101: from Beginner to Advanced (Part 1)
• 4 Steps to Create Mass Action and Save Custom Attributes to Customer Grid