When you first look at Magento database, it can be overwhelming because of its complexity. The Magento database is heavily dependent on EAV (Entity Attribute Value) model. While the EAV model makes it easier to expand the database, i.e. adding your own entities or attributes specific for your store, SQL codes can be very difficult to write.
Before you customize Magento database, make sure you read through EAV model and you understand Magento table structure thoroughly.
EAV Core Tables
All EAV tables are prefixed with “eav_”.
Table Sets (Table collections)
In Magento database, an entity can have several tables that share the same prefix. For example, the product entity has the catalog_product_entity table for its main data and several other tables prefixed with “catalog_product_” such as catalog_product_entity_int, catalog_product_entity_media_gallery, catalog_product_entity_text and so on.
To store the data more efficiently, product details are stored separately depending on their data types. When the value of the data is an integer type, it’s saved in the catalog_product_entity_int table, and when its type is an image, it’s saved in the catalog_product_entity_media_gallery table. The whole point is not saving big image data with small integer data in the same table.
Tables to Define Relationships
The catalog_category_product table, catalog_product_website or downloadable_link_purchased are examples of the tables that show relationships. The catalog_category_product table show which category includes which products and the downloadable_link_purchased table shows which order has which downloadable links.
By LED Downlights, November 4, 2009 @ 11:15 pm
Thanks for publishing this article, its quite helpful as im trying to manually import products into magento and am slightly confused by all the tables.
By Cobay, March 20, 2010 @ 8:50 am
gooo..posting…
have a nice day~~~