The document discusses the entity-attribute-value (EAV) data model used in Magento, where attributes are stored in a separate table rather than columns. EAV provides flexibility but can result in inefficient queries; solutions include using a pivot table or Amazon SimpleDB which avoids complex queries and requires no database administration.

What's hot

Similar to Entity Attribute Value (Eav)

Recently uploaded

Entity Attribute Value (Eav)

• 1.
  Entity - Attribute- Value (EAV) Data Model in Magento @Pờ Đình Tâm
• 2.
  What is EAVEAV can be thought of as “vertical” modeling instead of “horizontal” modeling of columns in a database table. Instead of a table consisting of a number of columns, denoting attributes of a conceptual piece of data, the attributes are stored in one column of a separate table.
• 3.
  Traditional User Tabletable: user_entity
• 4.
  EAV Style Tables
• 5.
  Structure of anEAV table The entity: Objects are entities (in Magento. Entities are: product, customer, order,…) The attribute: object properties are attributes The value: The value of the attribute
• 6.
  Update SQL: UPDATE`eav_attribute` SET `attribute_id` = ?, `entity_type_id` = ?, `attribute_code` = ?, `attribute_model` = ?, `backend_model` = ?, `backend_type` = ?, `backend_table` = ?, `frontend_model` = ?, `frontend_input` = ?, `frontend_input_renderer` = ?, `frontend_label` = ?, `frontend_class` = ?, `source_model` = ?, `is_global` = ?, `is_visible` = ?, `is_required` = ?, `is_user_defined` = ?, `default_value` = ?, `is_searchable` = ?, `is_filterable` = ?, `is_comparable` = ?, `is_visible_on_front` = ?, `is_html_allowed_on_front` = ?, `is_unique` = ?, `is_used_for_price_rules` = ?, `is_filterable_in_search` = ?, `used_in_product_listing` = ?, `used_for_sort_by` = ?, `is_configurable` = ?, `apply_to` = ?, `position` = ?, `note` = ?, `is_visible_in_advanced_search` = ? WHERE (attribute_id='498') BIND: Array ( [0] => 498 [1] => 4 [2] => sp_test [3] => [4] => [5] => varchar [6] => [7] => [8] => text [9] => [10] => sp test [11] => [12] => [13] => 0 [14] => 1 [15] => 0 [16] => 1 [17] => [18] => 1 [19] => 0 [20] => 1 [21] => 0 [22] => 1 [23] => 0 [24] => 0 [25] => 0 [26] => 0 [27] => 0 [28] => 0 [29] => [30] => 0 [31] => [32] => 1 )
• 7.
  Delete SQL: DELETEFROM `eav_attribute` WHERE (attribute_id='498')
• 8.
  Read SQL:SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(3, 4) AND cat_index.category_id='2' WHERE ( e.entity_id in ( SELECT `t1`.`entity_id` FROM `catalog_product_entity_varchar` AS `t1` LEFT JOIN `catalog_product_entity_varchar` AS `t2` ON t1.entity_id = t2.entity_id AND t1.attribute_id = t2.attribute_id AND t2.store_id='1' WHERE (t1.store_id = 0) AND (t1.attribute_id = 498) AND (IFNULL(t2.value, t1.value) LIKE :attribute_498) AND ( t1.entity_id IN ( SELECT `t1`.`entity_id` FROM `catalog_product_entity_int` AS `t1` LEFT JOIN `catalog_product_entity_int` AS `t2` ON t1.entity_id = t2.entity_id AND t1.attribute_id = t2.attribute_id AND t2.store_id='1' WHERE (t1.store_id = 0) AND (t1.attribute_id = 497) AND (IFNULL(t2.value, t1.value) IN ('0', '1')) AND ( t1.entity_id IN ( SELECT `t1`.`entity_id` FROM `catalog_product_entity_int` AS `t1` LEFT JOIN `catalog_product_entity_int` AS `t2` ON t1.entity_id = t2.entity_id AND t1.attribute_id = t2.attribute_id AND t2.store_id='1' WHERE (t1.store_id = 0) AND (t1.attribute_id = 496) AND (IFNULL(t2.value, t1.value) IN ('0', '1')) ) ) ) ) ) ) BIND: Array ( [attribute_498] => %ten% )
• 9.
  Read(2)
• 10.
  Read(3) Relation database:SELECT * FROM Employee WHERE Name = ‘John’ AND Nationality = ‘English
• 11.
  Read(4) Self Join: SELECT t1.ID as 'ID' t1.Value AS ‘Name’, t2.Value AS ‘Nationality’ t3.Value AS Birthday FROM EAV_Data t1 LEFT JOIN EAV_Data t2 ON t1.ID = t2.ID LEFT JOIN EAV_Data t3 ON t1.ID = t3.ID WHERE t1.Attribute = ‘Name’ AND t1.Value = ‘John’ AND t2.Attribute = ‘Nationality’ AND t2.Value = ‘English’ AND t3.Attribute = ‘Birthday
• 12.
  Good Provides aflexible mechanism to record the attributes associated with any entity. This EAV design requires almost no consideration of the nature of the applicable hierarchical data and requires very little time to implement The database schema does not change when the model changes
• 13.
  Bad The EAVtable doesn't provide a mechanism to create relationships between entities of different sub-types. The EAV table does nothing to provide a grouping of related entity types. The EAV table uses a VARCHAR column for all attribute values regardless if Dates, timestamps, integers, numerics or booleans would be more appropriate Inefficient queries. Where you would execute a simple query returning 20 columns from a single table, you end up with 20 self-joins, one for each column
• 14.
  Solution Take alook at Magento Database Diagram
• 15.
  EAV table withPivot (3) Pivot SELECT * FROM ( SELECT ID , [100] AS Name , [101] AS Birthday , [102] AS Nationality FROM ( SELECT ID, EntityID, AttributeID, Value FROM EAV_Table ) p PIVOT ( MAX (Value) FOR AttributeID IN ([100], [101], [102]) ) AS pvt ) WHERE Name = 'John' AND Nationality = 'English'
• 16.
  When Recommend fromAmazon SimpleDB: Principally utilize index and query functions rather than more complex relational database functions Don’t want any administrative burden at all in managing their structured data Want a service that scales automatically up or down in response to demand, without user intervention Require the highest availability and can’t tolerate downtime for data backup or software maintenance
• 17.
  Thanks for yourattention