Magento Database Structure: EAV

The Entity, Attribute and Value (EAV) database architecture is at first, extremely difficult to grasp. Combine this with the lack of documentation on EAV and you find that most people don't truly appreciate just how good EAV is and more importantly, how well suited it is to Magento. In this article I will attempt to shed some light on EAV in the hopes of helping you understand how it works, why it works and how this knowledge can benefit you as a Magento developer. To better understand this article, I recommend opening up a development Magento database using a tool such as phpMyAdmin.

What is EAV?

EAV stands for Entity, Attribute and Value. Let's look at each part of that and try to understand them a little better.

Entity

The entity represents Magento data items such as products, categories, customers and orders. Each entity (product, category etc) will have it's own entity record in the database.

Attribute

The attributes represent data items that belong to an entity. For example, the product entity has attributes such as name, price, status and many more.

Value

The value is the simplest to understand as it is simply a value linked to an attribute.

To better understand this, let us consider the product entity. Each product entity will have a series of attributes, one being the attribute name. Each product will then have a value for the attribute name (and all other attributes). This might not be clear yet but keep reading!

How does EAV work?

Before Magento, databases seemed a lot simpler. If you were designing an eCommerce application, you had one table that contained all of your product information, another that contained your category information and maybe another table that linked these two together. This is simple enough and easy to understand, where as Magento has almost 40 tables for the products and category's alone! To understand why, let us use the product table as an example.

Rather than store all product information in one table, Magento splits this information up into sub tables. The top table in this hierarchy is catalog_product_entity. If you take a look at this table in phpMyAdmin, you will see that it includes simple base information for a product and does not appear to include any useful information other than the SKU! Fortunately, using this table it is possible to build a full product record from the attribute and value tables.

To start building a full product record, you will need to start joining attributes to the product entity table. Before you do this, take a look at the table called eav_attribute. eav_attribute is the main attribute store for Magento and is used to store attributes for all different entities (product, customer, order, category etc). Open this table in phpMyAdmin and click browse. Notice that there are hundreds of different attributes, some even with the same name? At first this confused me because I wasn't sure how Magento could differentiate between the the two different attributes called name. How did Magento know which one was for the product and which one was for a category? As is usually the case with Magento, a small bit of research led me to the an extremely simple answer: entity_type_id! Each entity (product, category, customer etc) is given an entity_type_id. To find this out, go back to catalog_product_entity and look for the entity_type_id field. The value for every record in that table should be 4, as this has been designated as the entity_type_id for products. If you were to look in catalog_category_entity you should see a different entity_type_id. Using this value and the attribute code, it is possible to load the attributes for a product, or any entity.

Consider the following queries


# Load all product attributes
   SELECT attribute_code FROM eav_attribute 
   WHERE entity_type_id = 4;

# Load a single product attributes
   SELECT attribute_code FROM eav_attribute 
   WHERE entity_type_id = 4 AND attribute_code = 'name';

Now that you can get attributes and entities, it is time to start getting values. Values are separated across several different tables for reasons that I will go into shortly. For now though, just take a look at all tables that begin with catalog_product_entity. The way the values are split depends upon their type. For example, all prices and other decimal attributes are stored in catalog_product_entity_decimal where as all short text strings are stored in catalog_product_varchar. To figure out which table each attribute is stored in, Magento uses the column backend_type in the table eav_attribute. If you run the following query you should be able to find out the backend type for the product attribute 'name'.

   SELECT attribute_code, backend_type FROM eav_attribute 
   WHERE entity_type_id = 4 AND attribute_code = 'name';

Hopefully the above query returned the backend_type varchar, which is the correct type for name and all other short text strings. Based on what was said above, we can determine that the value for the name attribute will be stored in catalog_product_entity_varchar. What do you think will be produced by the following query? Have a think and then copy it into phpMyAdmin and see whether you're right.

   SELECT e.entity_id AS product_id, var.value AS product_name
   FROM catalog_product_entity e, eav_attribute eav, catalog_product_entity_varchar var
   WHERE 
      e.entity_type_id = eav.entity_type_id 
      AND eav.attribute_code = 'name' 
      AND eav.attribute_id = var.attribute_id
      AND var.entity_id = e.entity_id

The above code lists out the name and id for every product in your database. If you got that correct then congratulations, you are well on your way to understanding the EAV architecture. If you didn't get that, keep going and then give this another read afterwards. If you're still confused, post your questions in the comments or email me and I'll try to make things clearer. If you did understand that, can you see how using a simple PHP loop, you could cycle through all product attributes and retrieve each value, creating a full product model?

If you're running a multi-store Magento, here is how to adapt the above code to only include products from a certain store.

   SELECT e.entity_id AS product_id, var.value AS product_name
   FROM catalog_product_entity e, eav_attribute eav, catalog_product_entity_varchar var
   WHERE 
      e.entity_type_id = eav.entity_type_id 
      AND eav.attribute_code = 'name' 
      AND eav.attribute_id = var.attribute_id
      AND var.entity_id = e.entity_id
      AND var.store_id = 0

Why is EAV Used?

EAV is used because it much more scalable than the usual normalised database structure. Developers can add attributes to any entity (product, category, customer, order etc) without modifying the core database structure. When a custom attribute is added, no logic must be added to force Magento to save this attribute because it is all already built into the model; as long as the data is set and the attribute has been created, the model will be saved!

What are the down sides to EAV?

A major downside to EAV is it's speed. With entity data being so fragmented, creating a whole entity record requires a lot of expensive table joins. Fortunately, the team at Varien have implemented an excellent cache system, allowing developers to cache information that doesn't often change.

Another problem with EAV is it's learning curve, meaning a lot of junior developers give up before they can truly see the simplicity of it. While there is no quick fix for this, hopefully this article will help people start to over come this problem.

Conclusion

Entity, Attribute, Value is a great database structure and has been a key part to the success of Magento and therefore it is important for developers to understand how it works. There are also many applications for this knowledge and I'm confident if you work in Magento for long enough that you will come across some!

I intend to write a follow up article to this sometime soon, depending on how this one goes down. In the meantime, I wrote an article on updating prices globally in Magento that uses some techniques from this article. It might be a good idea to look at that to practise what you have learned here.

If you have any questions please let me know or post in the comments.

46 thoughts on “Magento Database Structure: EAV”

  • hellokeykey

    Could you write a tutorial to show us how to retrieve some date from EAV.

    best wish.

    Reply
  • nico

    thanks for this article, it's very good

    Reply
  • daisukebe

    I appreciate this helpful entry. This kindly article would be good for the beginners learning EAV :)

    Reply
  • israel

    thanks!!! that's all i need to understand this kind of db structure!

    Reply
  • Davin

    Very useful and brief,Thx

    Reply
  • sai

    thankyou for this article, i got an idea how the EAV tables work. Thanks alot.:)

    Reply
  • Sean

    The "Joinitis" really kicks in when you are trying to pull a dataset out of Magento to view more than 3000 products. It's the "death by a thousand cuts" on speed.

    Reply
  • Ovidiu

    Hi,

    Thank you for this info, very useful, I just finished a script that enters tznds of attributtes and sets in the db directly and was very hard, I finally figured it out by watching what tables change.

    Best Wishes,
    Ovidiu

    Reply
  • PankajK

    Hi,

    Thanks for the very useful article for understanding EAV structure of Magento, This is real helpful
    Thanks again!!!

    Reply
  • phpfarmer

    Thank you very much for your nice article :) I really like it, Can you please post a article on how product insertion works on magento?

    thanks again!

    Reply
    • BT

      @phpfarmer

      I am working on this at the minute and have written a small class to help with this. Once it is finished, I will write an article about my findings

      Reply
  • webrup

    Thanks ! That was a good explanation of it.

    Reply
  • Behrooz

    Thanx for the post. I can understand the entity, attributes and values and how there fit together. I took a look at the magento database structure and you get eav_entity_store table which is the followed by the eav_entity_type. What i want to know is what happens on the eav_entity_store?

    Reply
  • magento tutorial

    it is more flexible when you to design a database

    Reply
  • Karto

    That's a wonderful article. I tried it and it works like magic. Now, i want to get these values.
    name| product_link | description | image_link | model |price | category | sku |
    Can you please help me with the query? Thanks

    Reply
  • John

    EAV is nice for a lot of things but once the data gets extremely large and you're requirements for the data you want pull out increase, EAV falls apart w/o additions or changes.

    For example say I have thousands (or millions) of products and I want to find those that have 5 certain attribute values? You have to find the intersection of those values across potentially millions of "groups" of values. The dataset gets increasingly larger as you continue joining or using INTERSECT. That is if you have INTERSECT (MySQL doesn't). So it completely falls apart. You MUST have additional meta data or you must flatten the table into another more suitable for querying in an RDBMS. EAV should be used w/ care.

    Reply
    • BT

      Hi John,

      You're definitely right here, but as you point out, creating flat tables to increase lookup times can help dramatically. Magento does this for products and categories and other types of data that is a high chance a lot of sites with have large quantities of.

      Reply
  • Vipin

    wow。。thx for your article,it help me to learn magento well. thx very much! and now i want to design a product's erp for magneto.

    Reply
  • Abhijeet jadhav

    Very Useful information.

    Reply
  • Alvin

    Excellent tips indeed.. I think would be helpful for beginners.

    Reply
  • FMC

    I think this the best site for learning magento tutorial.

    Reply
  • Indika

    Very useful article. Thanks

    Reply
  • Beren

    Thanks for a great article. Can you please post the SQL for selecting more than one value type? Say name and base image URL?

    I'm guessing subselects will have to be used..?

    Reply
  • gagan

    Thanks it really helps !
    I have just started learing magento ... Please keep posting so that it helps junior developers llike me .. can u please make a tute on event driven system in magento . how all this works ..

    Reply
  • Rajendra Prasad
    Rajendra Prasad January 6, 2012 at 6:53 am

    Thanx very use full article

    Reply
  • sirajhussain

    Very helpful article, Thanks a lot.

    Reply
  • Frodo

    Thank you for the sharing this helpful information!

    I would like to know if anyone knows where magento stores the data from "Default Qty" field of the Gruped Product's associated products?

    Reply
  • Anant

    Can you please tell me how to insert product using sql queries .?
    Directly interacting with the database..

    Reply
  • Pranay

    Thanks, You wrote this article in very simple way.
    That's Gr8...

    Reply
  • Magento Development

    Excellent article, i obviously adore this page, continue on it.

    Reply
  • Neelam

    Fantastic article and easy to understand. Thanks.

    Reply
  • Ruiwant

    Thanks for your great post on Magento EAV System! I got so much from this tutorial! Thank you!!!

    Reply
  • Senthil

    its very nice and simple to understand about EAV in magento. thank you so much...

    Reply
  • Imranul Hoque
    Imranul Hoque May 27, 2012 at 12:18 pm

    Thank you so much. A very good place to start learning Magento database.

    Reply
  • Akshay Kumar Vishnoi

    Really helpful..Thanks
    But I need more details to understand magento DB tables with example. Can some one provide me link of tutorial on this topic (as I am beginner in Magento).

    Reply
  • Amit Kumar

    great tutorial !!!!!!

    how can i derive all the product (Only SKU) from 1 store in a multiple store configuration.

    I need to join catalog_product_entity and _________________ which table ?

    Reply
  • Jon Acker

    I find it much easier to read queries when the are written with the mysql USING keyword, so that it's clear what's joined onto what using what.
    Here's the query above rewritten with USING:

    SELECT e.entity_id AS product_id, var.value AS product_name
    FROM catalog_product_entity e
    JOIN eav_attribute eav USING(entity_type_id)
    JOIN catalog_product_entity_varchar var USING(entity_id, attribute_id)
    WHERE eav.attribute_code = 'name'

    Reply
  • sixe

    BOOOM! Thanks a lot maaan!

    Reply
  • Alastair

    A million thank-yous! I have been looking for ages for a simple introduction to the structure of the Magento database. I'm sure there's a proper manual out there somewhere but I haven't come across it. Your simple explanation of the eav table and how it relates to the products was enough to get me where I need to be.

    Reply
  • Cecil

    This I call demystifying magento Db. Thank you. I was completely in the dark until reading this block.
    Thank you again. Its brought it all together really nice.

    Reply
  • jaipal

    I have get good knowledge of database structure of magento of your article.

    Thank You

    Every one

    Reply
  • Someone

    I could not disagree with above article more. EAV is not great, is is totally useless. It was invented for people who cannot grasp the basic concept of normalizing a database.

    It does totally NOT suit magento and it is NOT part of magento's succes, if anything, it's the main reason why Magento is NOT as succesfull as it could be.

    I am glad to hear that EAV will be eradicated in Magento 2.0.

    The idea behind EAV is in itself ok. Most people who claim to know why EAV was invented, don't. EAV is there for datasets with what's called sparse data. Normally one wants to prevent double data in a database, and empty fields.

    In older e-commerce solutions one often got something like a product table. Then a developer or customer wanted a new attribute, and simply add a column to a table.

    Suppose you have a large set of products with a lot of attributes, and not all the same, this will result in a products table with A LOT of columns, and most of them being empty for most products, since no product uses all attributes (for instance). This is called sparse data.

    This is exactly the situation that EAV was originally ment to cope with.

    However, in reality, the sparse data or possibly growth of the product table, is really a minor issue for those who know how to develop and have a sense of what normalising a database entails. By either creating tables for sparse attributes (containing the values), or indeed just adding columns to a table, this 'problem' is way less complicated than the current EAV model is, or ever will be.

    Besides that, the performance of EAV downright sucks. It's the main reason why magento is not the logical choice for big stores with lots of products, EAV will totally kill and destroy the performance.

    Luckily, the magento devteam also started to understand it. They first tried to hack their way out of it using flat tables, which basically are the tables with extra columns I described earlier, but it appears for 2.0 they will finanally bite the bullet and remove the EAV crap all together.

    It was a nice try, but failed miserably. EAV is NOT great. It's crap. At least for MAgento, it is.

    Reply
  • Marc

    Thanks fo the tutorial, it helps explain the EAV structure nicely.

    How would I change this query to select 2 or more attributes?

    Reply
  • Rick Buczynski
    Rick Buczynski July 2, 2013 at 2:35 pm

    First, thanks for writing an article that's still useful years later. Second, though the "Someone" poster above might be right about EAV flaws, the fact is that for a developer like me who doesn't necessarily know any better, and is simply being tasked to deal with Magento, this post taught me exactly what I needed to know.

    Reply
Leave a Reply
Post your comment

FishPig Ltd