https://embed.notionlytics.com/wt/ZXlKd1lXZGxTV1FpT2lKaFlUZG1PR014WkRVd1ltWTBORGd5T1RVd05qZzBNekF4WTJJd1kyRTJPU0lzSW5kdmNtdHpjR0ZqWlZSeVlXTnJaWEpKWkNJNklrbGpiM1pzV2pOcU1VUnBRM1ZUUm1WNFRtdHlJbjA9

Magento implements the Entity-Attribute-Value (EAV) model. This means Magento is capable of managing any amount of entities. These entities may have any amount of assigned attributes.

The EAV model is an abstraction that allows extending Magento functionality by adding more entities or more attributes without writing too much boilerplate code.

What are Magento 2 entities?

As an eCommerce platform responsible for most processes of online retail business, Magento has to manage multiple entities. Here is their schema:

Each of these entities may have attributes that describe each instance of that entity. Products may have color, categories may have a description. Managing entity attributes is a very important process. Magento solves it by introducing the Entity-Attribute-Value (EAV) model.

How are attributes of entities managed?

This model stores data the following way:

• All entity types are stored in the eav_entity_type table. It declares identifiers of each entity type. Product, category, order entities are declared in this table.

• All attributes of all entities are stored in one table called eav_attribute. This table declares the identifiers of each attribute.

  <aside> ➡️ One attribute can only be mapped to one entity. This means the name of a product is a different attribute from the name of a category. The attribute code might match, but the identifier will not.

  </aside>

• The attribute groups are stored in the eav_entity_attribute table. It declares an identifier of attribute groups. Attribute groups are only used by product attributes in Magento.

• All attribute values mapped to each instance of each entity are stored in the <TYPE #N>_entity_<FORMAT> tables. You should look up the values of attributes of each entity (<TYPE>) here. Just make sure that you are aware of the data type (<FORMAT>) of that attribute.

  • What are possible data-type (<FORMAT>) suffixes?
  • What are possible entity (<TYPE>) prefixes?
  • What are examples of these tables?

And this is how the EAV model works! We have skipped over a few tables to avoid confusion.

• What tables are not included in this schema?

Why is entity information indexed?

As you may see, the information of each entity attribute is split into several tables. On a scale, with 100.000 entities and 400 attributes, this results in 100.000.000 entries in attribute tables. This is very hard to manage for any SQL engine. This is especially apparent when querying by entities by their attribute value.