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.