Magento 2 EAV Model
Why 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 we use this structure to add a new attribute for the product, we have to change much database and code (add more columns or create a new table to save attributes).
Furthermore, there are unnecessary attributes we 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.
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.
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).
The Magento 2 EAV 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 we want to add more attributes, we just need to put these attributes to a table, including all declared attributes in advance. For example, we 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 we desire to add attribute data for each store, we 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.
Benefits Of Magento 2 EAV Model
EAV model helps to resolve a lot of issues related to designing databases for websites, especially when we want to manage, modify or create new attributes for entities:
- Easily create new attributes and manage adjustment. If we desire to add attributes for the entity, there is no need to modify table structures, but the only thing we 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.
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.
- 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 we enable Use Magento 2 Flat Catalog Product config at Store ⇒ Configuration ⇒Catalog ⇒Catalog ⇒Storefront).
In this table, attribute data is updated/inserted when we run index (via cron or cli) or save the product, depending on the Product Flat Data config in Index Management. Hence, we just need to implement queries to get data via the catalog_product_flat table. And the query is now much more straightforward.
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.