Magento employs a comprehensive Entity-Attribute-Value (EAV) database model to store merchant data, providing robust support for future customizations. With the EAV model, Magento enables the creation of new attributes for entities such as products, customers, sales orders, invoices, and more, all without requiring any modifications to the database schema. In this article, we will delve into the details of the EAV model, explore how it functions, and examine how Magento leverages it to achieve superior performance and scalability.
What is EAV Model ?
In a traditional relational database model, entity data are stored in a single table with separate columns for each attribute. Consider the following example:
In the product table, there are three separate columns for the name, description, and price.
Contrary to this traditional approach, the Entity-Attribute-Value (EAV) model stores entities, attributes, and attribute values in separate tables. Let’s examine the product entity used in Magento and its attributes (name, description, and price). The following tables are used to store product data with these attributes:
- catalog_product_entity: This is the entity table, storing only the entity_id and product type.
- eav_attribute: This table stores all attributes used for various entities such as products, customers, orders, etc.
- Attribute values tables – These tables store the actual attribute values considering the data types.
- catalog_product_entity_varchar – name
- catalog_product_entity_decimal – price
- catalog_product_entity_text – description
Magento utilizes several other such tables to store product data according to their data types.
Advantages of EAV Model
- Flexibility – It is easy to create new attribute without doing any modification to the existing schema
- Scalability – Handle large number of attributes without significant performace degration
Disadvantages of EAV Model
- Complexity – Querying can be complex and less efficient compared to non-eav relational model.
- Performance – Since complex queries involves in data retrival, there can be performance issues.
How Magento utilizes EAV model and enhance performance.
Data dictionaries are more complex in EAV model and hence, there will be significant effect on performance of data retrival. Magento uses EAV model for data storing and utilize it’s robust indexing process to improve performance.
How Magento uses indexing for performance improvement and scalability will be discussed in a seperate article.