Magento EAV structure & role of eav_attribute’s backend_type field

Introduction:

Magento database is based on EAV(Entity Attribute Value) architecture. And it uses this architecture, especially for categories, products, customers & customer addresses.
Before Magento version 1.4.x it used EAV structure for orders as well but thereafter order’s EAV structure has been converted to a flat structure.

From the definition of EAV:

Entity(E):

Entity actually refers to the data item.
In Magento:

  • catalog_category_entity
  • catalog_product_entity
  • customer_entity
  • customer_address_entity

are the entity tables for categories, products, customers & customer addresses respectively.

Attribute(A):

Attribute refers to the different attributes of an Entity.
In Magento, attributes related to an entity are stored in a single table: eav_attribute but differentiated by field: entity_type_id.

We can easily find all the attributes related to an entity using the following SQL query(For Example product):

SELECT 
  attribute_code,
  attribute_id,
  backend_type 
FROM
  eav_attribute 
WHERE entity_type_id = 
  (SELECT 
    entity_type_id 
  FROM
    eav_entity_type 
  WHERE entity_type_code = 'catalog_product')

Which gives the following results:

+----------------------------+--------------+--------------+
| attribute_code             | attribute_id | backend_type |
+----------------------------+--------------+--------------+
| activation_information     |          496 | text         |
| color                      |          272 | int          |
| color_code                 |          950 | varchar      |
| computer_manufacturers     |          510 | int          |
| contrast_ratio             |          875 | int          |
| cost                       |          100 | decimal      |
| country_orgin              |          507 | text         |
| cpu_speed                  |          877 | int          |
| created_at                 |          930 | static       |
| custom_design              |          571 | varchar      |
| custom_design_from         |          572 | datetime     |
| custom_design_to           |          573 | datetime     |
| custom_layout_update       |          531 | text         |
| description                |           97 | text         |
| dimension                  |          494 | text         |
| enable_googlecheckout      |          903 | int          |
| finish                     |          509 | text         |
| gallery                    |          271 | varchar      |
| gender                     |          501 | int          |
| gift_message_available     |          562 | varchar      |
| harddrive_speed            |          878 | varchar      |
| hardrive                   |          499 | text         |
| has_options                |          838 | static       |
| image                      |          106 | varchar      |
| image_label                |          879 | varchar      |
| in_depth                   |          492 | text         |
| is_imported                |          949 | int          |
| is_recurring               |          933 | int          |
| links_exist                |          947 | int          |
| links_purchased_separately |          904 | int          |
| links_title                |          906 | varchar      |
| manufacturer               |          102 | int          |
| max_resolution             |          873 | varchar      |
| media_gallery              |          703 | varchar      |
| megapixels                 |          513 | int          |
| memory                     |          498 | text         |
| meta_description           |          105 | varchar      |
| meta_keyword               |          104 | text         |
| meta_title                 |          103 | varchar      |
| minimal_price              |          503 | decimal      |
| model                      |          495 | text         |
| name                       |           96 | varchar      |
| news_from_date             |          704 | datetime     |
| news_to_date               |          705 | datetime     |
| old_id                     |          110 | int          |
| options_container          |          836 | varchar      |
| package_id                 |          951 | int          |
| page_layout                |          929 | varchar      |
| price                      |           99 | decimal      |
| price_type                 |          859 | int          |
| price_view                 |          862 | int          |
| processor                  |          497 | text         |
| ram_size                   |          874 | varchar      |
| recurring_profile          |          934 | text         |
| required_options           |          837 | static       |
| response_time              |          876 | varchar      |
| room                       |          508 | int          |
| samples_title              |          905 | varchar      |
| screensize                 |          500 | text         |
| shape                      |          476 | text         |
| shipment_type              |          863 | int          |
| shipping_qty               |          952 | int          |
| shirt_size                 |          525 | int          |
| shoe_size                  |          502 | int          |
| shoe_type                  |          107 | int          |
| short_description          |          506 | text         |
| sku                        |           98 | static       |
| sku_type                   |          860 | int          |
| small_image                |          109 | varchar      |
| small_image_label          |          880 | varchar      |
| special_from_date          |          568 | datetime     |
| special_price              |          567 | decimal      |
| special_to_date            |          569 | datetime     |
| status                     |          273 | int          |
| tax_class_id               |          274 | int          |
| thumbnail                  |          493 | varchar      |
| thumbnail_label            |          881 | varchar      |
| tier_price                 |          270 | decimal      |
| updated_at                 |          931 | static       |
| url_key                    |          481 | varchar      |
| url_path                   |          570 | varchar      |
| visibility                 |          526 | int          |
| weight                     |          101 | decimal      |
| weight_type                |          861 | int          |
+----------------------------+--------------+--------------+

Similarly, you can find attributes for other entities just by using the following values for entity_type_code:

  • catalog_category
  • customer
  • customer_address

for categories, customers & customer address respectively.

Note: You can get the related values for entity_type_code from table: eav_entity_type.

Value(V):

Value refers to the actual value of the attribute of the entity.
In Magento attribute values of an entity (for example: product) are stored in catalog_product_entity_{backend_type} tables.
Where {backend_type} refers to the value of field: backend_type (except value ‘static’) of table: eav_attribute.

Following SQL is used to find all the backend types related to a product entity:

SELECT DISTINCT 
  backend_type 
FROM
  eav_attribute 
WHERE entity_type_id = 
  (SELECT 
    entity_type_id 
  FROM
    eav_entity_type 
  WHERE entity_type_code = 'catalog_product')

Which results:

+--------------+
| backend_type |
+--------------+
| text         |
| int          |
| varchar      |
| decimal      |
| static       |
| datetime     |
+--------------+

So tables:

  • catalog_product_entity_text
  • catalog_product_entity_int
  • catalog_product_entity_varchar
  • catalog_product_entity_decimal
  • catalog_product_entity_datetime

are used for storing values for an attribute of related backend_type.

Note: You must be wondering about backend_type = static. For an attribute with the static backend_type, values are stored as a column directly in the entity table. For example, sku is an attribute of backend_type = static and its values are stored in the entity table itself: catalog_product_entity under field: sku.

That’s all about the definition of EAV relating to Magento.
Hopefully, now you are able to differentiate which attribute values go where (in which table) based on the backend_type.

Thanks for reading & Happy E-Commerce!!