Magento MySQL database diagram

If you worked with osCommerce, Zen Cart, CRE Loaded or any similar eCommerce platform before, you might find Magento database structure quite confusing when you see it for the first time. I advise you not to rush too much figuring out what is what by glancing through database. Try to spend first few hours getting familiar with some background. For purposes of flexibility, the Magento database heavily utilizes an Entity-Attribute-Value (EAV) data model. As is often the case, the cost of flexibility is complexity. Is there something in Magento that is simple from developers point of view?
Data manipulation in Magento is often more knowledge demanding than that typical use of traditional relational tables. Therefore, an understanding of EAV principles and how they have been modeled into Magento it is HIGHLY recommended before making changes to the Magento data or the Magento schema (Wikipedia: Entity-attribute-value_model). Varien has simplified the identification of EAV related tables with consistent naming conventions. Core EAV tables are prefixed with “EAV_”. Diagrams in this post contain a section labeled “EAV” which displays Magento’s core EAV tables and thier relationships to non-EAV tables.
Download Magento 1.1.6 MySQL database diagram (PDF)
Download Magento 1.3.2.4 MySQL database diagram (PDF)
For the later versions, visit http://www.magereverse.com/
Database diagrams and documents found in this post are intended to mirror the database schema as defined by Varien. Table relationships depicted in the diagrams represent only those relationships explicitly defined as Foreign Keys in the Magento database. Additional informal/undiagrammed table relationships may also exist, so when modifying the schema or directly manipulating data it is important to identify and evaluate possible changes to these tables as well (and the tables they relate to, and the tables they relate to…).
The author of Database Diagram is Gordon Goodwin, IT Consultant. You can see his info in the PDF.
PS. This topic was discussed on two more following articles from my colleagues. Mladen Lotar expanded this intro and provided more details at his Magento’s database layout and it’s EAV structure article at 2010, while in 2009. Branko Ajzele opened a discussion of how to Escape from EAV the Magento way.
31 comments
I am actually the Magento Schema Diagram author. Was thinking of updating it, so I went looking for my original and ended up here. I originally posted the diagrams to the Magento Forum & Wiki – no idea how they found their way here, but thanks for the comments.
Does anyone have an idea where to find Magento 1.8. database diagram? Would like to update this post.
I like the way Magento works, I currently have an OSCommerce, and in the process to migrate. Thanks for this post as it was one of the first ones I read about the database because it is confusing however it will make things so much cleaner than OSC.
Extremely awesome …. Very good job.
I think this will be very helpful to learn magento.
Looking at the database schema, I can guess that Magento is a very comprehensive application. But on the other hand, all those overhead of having flexible attributes will cause the app to slow down significantly.
I heard a lot of good things about Magento. Coming from Zen Cart, which is a bit slow shopping cart, I am curious to know how fast does Magento run?
Hi,
Beautiful diagram.
Ho knows what tool is used for generate this diagram ?
What graphic modelin tool ?
Thanks
Hi,
Thanks a lot.
Its very useful …
Thiyagarajan
Hello Tomislav,
You may appreciate a new tool available online :
MAGEREVERSE – online Database Diagram Tool dedicated to Magento eCommerce : http://www.magereverse.com/
Selim
you are a Genious, thanks a lot.
Deo
Thank you so much! This is exactly what we needed to make our store operations more manageable. I feel like my life is about to get a whole lot easier.
Hi Kelly,
Sadly, it will not. 😉 There are very rare occasions where we would recommend communication with database directly outside Magentos collections. Magento tends to change database structure quite often with each new version, so we would suggest you not to build modules that directly manipulate existing tables. You never know what will change in the next version.
Cheers!
Thank you. Your effort deserves a comment.
Wow, thanks alot, this is going to save me hours, literally
Great, cant imagine how helpful it has proved to be to me. Thanks so much
Feels like looking at a circuit diagram 😀 Considering how much wire an entitiy must traverse, I’m suprised to see that Magento can even run 🙂 Sorry for moaning over the slowness before seeing this 😛
Goddamn, this is awesome
Thank you very much
Hey Mera,
Did they really help? I don’t know how could they 🙂
Before Magento, database diagram was always the first item we investigated when we started to learn some platform. However, this is totally different. We rarely look into database diagram while working in Magento.
Cheers!
You are an amazing person! What an effort. You saved my day and my store.. i have been juggling with all teh tables so far, without knowing what is meant for what. thanks a ton!
For update version (Database Diagram Files for Magento v1.3.2.3 & 1.3.2.4 [2009-10-03]) of the database diagram, you can visit http://www.magentocommerce.com/boards/viewthread/7359/
OMG… thx so much for the diagram!!!!
hahahahaha.. long live the relational database!
what a mess..
Very useful indeed, thx a bunch!
This is a life saver. I’m sort of disappointed Varien didn’t supply this along with a class diagram themselves. By the way I’m still looking for a comprehensive class diagram.
We all do Sam 🙂
Hi,
Gr8 work n thanks a lot I was looking for exactly same functionality
Hey, friend!
Thanks for publishing this, and congrats to Gordon. Astonishing work.
I am afraid I cannot print it in an A4 though *sigh*
thanks gordon , and you for publishing
Hi guys,
Sorry for misunderstanding. Let me clarify something. I’m not the author. The Author is Gordon Goodwin, IT Consultant. You can see his info in the PDF.
YES!!!. Very nice. Very helpful. Thanks again.
can’t believe someone actually done this! this will surely speed up my progress.
thanks a lot!
I can’t believe no one has take the trouble to thank you for this effort.
This is exactly what I have been looking for.
Many thanks,
Harry Pierson