The columns in a table normally tell us what is inside of them.  So, if you are looking at a flat table that contains product data, a column called ‘name’ contains the name of each product.  A column called ‘description’ contains the descriptions.  In Magento, if you are looking for product data, there is no column called ‘name’ or ’description’.  Magento does not use flat tables to store product data but instead uses the EAV model.

The table catalog_product_entity_varchar is one of the tables that contains product data.  The column called value contains the product’s image location, name, page layout, and many more types of data. The picture below illustrates how in the EAV different types of data are stored in one column.

In this picture, the attribute_id tells us the type of data stored in each row.

(Normally, the catalog_product_entity_varchar table does not contain a column called attribute_code.  I added this column so that you can see what each attribute_id actually is).

By the way, please notice that the entity_id is 382.  This means that all the values in the above picture correspond to ONLY one product.

So, now you have the gist of what the EAV is.  But, there are so many more details that need to be filled in and the best way to convey these is through a real example of how the EAV is used in Magento. Let’s take a look at how the product data is stored…

The Basics of the EAV

The main table that contains product data is catalog_product_entity. Every row in the main table represents a product. So, the number of rows in this table is the number of products in your Magento installation.   The technical name for this table is NOT the main table; it is the entity table.  Every row in the entity table is called an entity!

EAV standards for Entity-attribute-value. Now you know that E stands for entity!

If we look at the table catalog_product_entity, we see that it only has a few columns.  All of the columns in this table are shown in the picture below except for updated_at and created_at (these are not shown because they are not as important to us at this time).  But, where is all the other product data? Where is the product’s name, description, special price, etc?  There are no columns for these!

The product’s name, description, special price, etc, are all stored in other tables that are linked to the catalog_product_entity table.   The picture below shows where all the other columns are stored.  The blue text to the right indicates the name of the table where the information is stored in.

In the EAV, the different fields like description, name, meta_description, and special_price are all called attributes.  Now, you know what the A stands for in EAV!

The tables in the blue text shown in the picture above I will refer to as attribute value tables. Every row in any of the attribute value tables refers to one product in the ‘entity_table’.

What if every single product attribute was stored in the catalog_product_entity table and there were no attribute value tables? If your test environment is using Magento’s sample data that comes with 1.9.1.1, then the catalog_product_entity table would need to have over 110 columns. Yikes!

How do You Determine the Name of the Entity Table?

Before digging into the EAV further, let’s cover a more basic question. What data in Magento uses the EAV?

Magento uses the EAV to store customer, customer address, category, and product data.  Customer, customer address, category, and product are all called entity types.

So, above, we were told that the table called catalog_product_entity is the entity table is for product data.  How do we know this? The table called eav_entity_type shown below tells us this.

picture: eav_entity_type

In the eav_entity_type table, the entity_table column for the last row shown above is ‘catalog/product’, which is an abbreviation for the entity table.  The picture below illustrates that this abbreviation maps to the catalog_product_entity table.  If you would like a full explanation on how to map an abbreviation to a table name please read this tutorial.

picture: mapping ‘catalog/product’ to the catalog_product_entity table.

The entity_type_code shown in the picture above called ‘picture: eav_entity_type’

tells us what type of data is contained within each entity type.  The data that is contained in each entity type can be determined by just looking at the entity_type_code.  For example, the entity type that has the entity_type_code ‘customer’ holds the customer data.  The entity type that has the entity_type_code ‘catalog_category’ holds the category data. 

How to Determine What The Attribute Value Tables are?

So, how do we know what the entity attribute tables are for the catalog_product entity?  Please search your database for all tables starting with catalog_product_entity.  These tables are shown below. 

The catalog_product entity has many more tables associated with it than the typical entity type.  If catalog_product were a typical entity type it would just have these tables:

• catalog_product_entity
• catalog_product_entity_datetime
• catalog_product_entity_decimal
• catalog_product_entity_int
• catalog_product_entity_text
• catalog_product_entity_varchar

What table is each attribute stored in?

In the picture below, we see that the attributes called description, name, meta_description, and special_price are being set.  These attributes are stored in one of the tables in the bulleted list right above this paragraph. So, which table is each of these attributes stored in? In the picture below, the comments in blue on the right tell us the name of the table that each of these is stored in. 

How was this determined?  The table name can be determined by looking in the eav_attribute table’s column called backend_type.  

Picture: eav_attribute table backend_types

So, we just need to take the name of the entity table, add an underscore, and then add the backend type.

name of entity table + ‘_’ + backend_type = tablename

So, both name and meta_description have a backend type of varchar.  Therefore, if we follow the above formula the name of the table is: catalog_product_entity_varchar.

The catalog_product_entity_varchar table is shown below….

Picture:  catalog_product_entity_varchar table

So, each row in the above picture corresponds to a record in the entity table.  The record it matches with in the master table is the one that has the same entity_id.

How EAV Entities Store Columns as Rows

As we know, columns do NOT exist for description, meta_description, name, and special_price in the above table.  Rather, columns are actually stored as rows. The attribute_id tells us what the name of the ‘column’ is.

• The fourth row in the picture above has an attribute id of 71, which means that this row contains the product’s name in the value column.
• The second row in the picture above has an attribute id of 87, which means that this row contains the product’s thumbnail in the value column.
• The last row in the picture above has an attribute id of 97, which means that this row contains the product’s url_key in the value column.

So, how do we know that 71 is the product’s name, that 87 is the thumbnail, or 97 is the url_key?  The table eav_attribute provides us with this information as shown below!  The name, url_key, and thumbnail are all called attributes.

Picture: eav_attribute table, simple

In the picture below, you do not need to look up the name of a column by its attribute id.  The name of the column is shown inside the red rectangle.

Picture 6: catalog_product_entity_varchar with column names

The name of a column that is stored as a row is called an attribute. (all the attributes that exist are listed in the eav_attribute table)

When many people think of a product, the sku is how many people would uniquely identify it. In Magento, a product’s entity_id is its primary key. The picture below is EXACTLY the same as the above table EXCEPT the sku appears instead of the entity_id to make it easier to understand the following about the catalog_product_entity_varchar table:

• One row represents one product
• Each row contains ONLY one piece of data about that product such as the name, or the description, or the url_key.  The attribute_id tells you what the data is.

The picture above shows the name, thumbnail, and url_key each three different times.  What if we took the above EAV table and converted it into a flat table?     

It would look like the table below.  The table in the picture above and below are EXACTLY the same!

A Step-By-Step Example of How to Find Where Informaiton is Stored in the EAV

A great way to reinforce the skills and ideas in the above sections is to perform a practice exercise that will allow us to apply these to a common real life situation.

In this exercise, our goal is to find the description in the database for the product with an sku of  ‘hdb006’. From the above section, we know that entity_table for the product entity type is catalog_product entity, which has an entity_type_id of 4. Please click here if you wish to see the full explanation mentioned above.

The catalog_product_entity table contains a column called ‘sku’.  Let’s run the following query to determine what the product’s entity_id is:

SELECT entity_id FROM catalog_product_entity WHERE sku = ‘hdb006’

And, the record that this query returns has an entity_id of 382. This is the value of the primary key.   We will need later. 

The description is not one of the columns in the entity table.  Therefore, it must be in one of the attribute value tables.  Let’s find the table that contains the description. 

Okay, the eav_attribute table lists all of the attributes that exist in your Magento installation and it contains extremely important details about each one.  For each attribute, it tells us its label, input type, attribute code, the backend type, and other very useful information.  It will also tell us what attribute value table the description attribute is stored in.

So, let’s find the description attribute in the eav_attribute table. How do we find it?  If we log into the Magento admin, and visit any product page, we will see that the label is ‘Description’ which is shown below.

So, we can find the description attribute in this table by running the query SELECT * from eav_attribute WHERE frontend_label = ‘Description’.  And, it returns two results.

We’ll we’re looking for one attribute, not two, why is it that two description attribute’s exist? There is only one difference between these two attributes; the entity_type_id is different for each.

Please notice, one of these attributes has an entity_type_id of 3 and the other 4.  Earlier, we spoke about how the eav_entity_type table tells us the type of data Magento stores using the EAV such as customer data, customer address data, product data ,etc.  That table has a column called entity_type_id. 

In the eav_entity_type table, the entity_type_id for the catalog_product_entity is 4 and the category’s entity_type_id is 3.  We are looking for the product’s description in this example therefore the row we want is the one with the attribute_id of 72. 

Please note, the above picture tells us that the attribute that has the attribute_id 72 has a backend_type of text.

The backend type of ‘text’ tells us what the child entity table is.  All we need to do is take the name of the entity_table is use this equation:

main entity table + underscore + backend_type = tablename

‘catalog_product_entity’ + ‘_’ + ‘text’ = catalog_product_entity_text

This means the product’s attribute called description is stored in the catalog_product_entity_text table.

Okay, great, so let’s open up the catalog_product_entity_text table and look for the description.  Let’s run the query and remember attribute id of 72 refers to the description attribute.

SELECT *

FROM catalog_product_entity_text

WHERE attribute_id =72

This query lists the description for every single product in your Magento installation.  We only want the description for sku ‘hdb006’, which has an entity_id of 382.  Now, let’s revise our query slightly.

SELECT *

FROM catalog_product_entity_text

WHERE attribute_id =72 and entity_id = 382

This query produces the result below and the value is this product’s description.  Hooray, you have done it, great job finding the value of the description attribute!