Magento’s database layout and its EAV structure

Greetings fellow developers! Today I’ll try to explain the principles of EAV system and Magento’s implementation of it. This is a tutorial / explenation for beginners, so please be patient and even try to draw the logic on a paper if you don’t understand it. A small notice before we start. I won’t use specific SQL query examples since this will be a simplified example of principles of the EAV.

What is EAV?

EAV stands for Entity, Attribute and Value. And that’s where we begin with our story.

Entity is somewhat of a “data item”. In Magento, those are for example Categories, Products and Customers.

Attribute is again a “data item”, but to differ it from the Entity, in Magento, attributes are for example: Title, Description, Price, etc. for each Product entity.

And Value for simplicity of explanation represents “real value” that is attached to Entity’s Attribute

And now, how does it work? (a.k.a. The Fun Part)

Part 1 – saving the data

Again, for the sake of simplicity, let’s use a Product in Magento for example.

If you’ve ever added a new product to Magento, you know that you’ve had to add some title, description and price to it (there are more attributes but we are using 3 for this example). When you saved your product, you used a total of 3 tables to save that information (a few more, but those are irrelevant for our example). First, a new Entity was added to DB, second, In Attribute table, you saved attributes (title, description and price) attached to that product, and finally to Value table, you saved 3 rows, attached to that product’s attributes. Here’s a simplified schema for those 3 tables:

Let’s say that our inserted product got the ID value of 1, our attributes got IDs from 1-3 and values where assigned to attributes accordingly.

Part 2 – getting the data

This is the easy part to understand. Retrieving the data from any EAV system begins at Entity. First, you get the Product entity’s (in our example 1)  ID, then, you get it’s attributes and values accordingly. That’s about it regarding retrieving the data from EAV system.

The conclusion

Well, I hope you understood the above example, in Magento’s EAV. I say Magento’s because there can be (and there are) different variations regarding the EAV’s complexity, but the principle is same for all of them.

Why is EAV used?

It’s used because of scalability. You can add almost anything into DB without changing it’s structure. That would be the main pron of it.

But everything that has a pron, has a con too. 😀

Major problem with EAV systems is that each of them is much slower than a custom made solution because of SQL complexity. You need quite a few joins on DB just to retrieve one single Entity (or “data item”) in opposite to, let’s say, one select query on a custom made solution.

After this brief explanation. You should understand why Magento uses EAV system. It uses it because Magento is designed to be scalable, regardless of speed problems.

I’ve attached a PDF of Magento’s DB structure (v. 1.3), so that you can see the complexity (and simplicity) of it. And a wiki refference.

I hope this helped somebody, and that you now understand that once you comprehend EAV’s logic, it’s quite simple. 😀

P.S. Before me, two of my colleagues wrote  articles that are closely related to the EAV discussions. Tomislav Bilic firstly introduced Magento MySQL database diagram back in 2008, while last year Branko Ajzele opened a discussion on how to Escape from EAV the Magento way.