The Magento EAV Database Structure
Those of you that have ever tried to bring back data directly from the Magento database will know that it’s not straightforward at all. For example, you can’t go into the product table and bring back a product of your choice. This is due to to the EAV architecture, something that Magento has introduced to add extra security to how data is called and processed.
For those developers who have spent hours trawling through the Magento database for whatever reason, you’ll most likely have become very familiar with the large number of EAV tables that are contained within it. Until recently I actually had no idea what this stood for and indeed what the purpose of EAV was.. for those of you that have done object-oriented programming it will seem familiar, but actually the concept is completely different.
EAV, or Entity, Attribute, Value is a way of storing entity data that is very different in a way that makes them very similar. In normal OO circumstances if you’d have a product table, with many attributes such as price, colour, description etc, and these attributes would obviously have values such as £9.99, blue and ‘What an awesome price for a blue sportscar’.
Entity
The entity is a major factor that is dealt with in ever store. Created in eav_entity, an entity includes things such as:
- Products
- Categories
- Customers
- Orders
- Invoices
Attribute
Belonging to each entity is a set of attributes which make up the data for it. These attributes are all kept in eav_attribute and eav_entity_attribute for every type of entity. This means that you’ve got things such as ‘Customer Name’ and ‘Product Price’ sitting right next to each other in the same table. The entity_type_id is the reason why each attribute can be found so easily!
Value
I think you’ll be able to guess that the value is simply the raw data that is linked with each attribute. This type of data obviously is the main reason why databases become so huge – particularly if you have 10,000 products and 80,000 customers… but then you’ll be too rich to care!
The reason why EAV is used is due to the fact that it is much more flexible that normalized databases from the OO days. Entities and attributes can be added efficiently every time, which works well for Magento – an architecture which is ever-expanding (an ever more complicated). The main downside is the slowness that every Magento developer will be aware of – having so many adjoining tables makes the loading of data a slightly longer process.
My advice is to get used to the EAV architecture and the huge number of tables that are used for every single entity because it is here to stay. Get into phpMyAdmin or Sequel Pro and have a good look round at the architecture. In theory it’s quite difficult to get your head around, but actually it’s not so bad! Thanks for reading the Magento Blog at Ecommerce Web Design.
