Magento Table Structure
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_?.
* eav_entity_type: table of all entitiesIt contains information about entity codes, entity models, entity tables and more.
Example entities: customer, order, catalog_category, catalog_product, invoice, shipment, and so on.
Each entity has a corresponding data table prefixed with “_entity?, i.e. customer_entity, sales_order_entity, catalog_category_entity, and so on.
* eav_attribute: table of all attributes
It defines all necessary attributes for each entity. For example, a customer has first name, last name, email address and so on. Customer is defined as an entity in the eav_entity_type table and customer’s attributes such as first name, last name or email are defined in the eav_attribute table. Hundreds of attributes are defined by default with Magento installation.
* eav_attribute_option, eav_attribute_option_value
These two tables are used to define options for each attribute. For example, the manufacturer attribute can have “Toshiba?, “Dell?, or “HP? for its options. These option values are stored in the eav_attribute_option_value table and the relationship of each option and the attribute is stored in the eav_attribute_option table. An option can also have multiple option values when it’s used for multiple stores.
* eav_attribute_set
This table is used to define different attribute sets for an entity. For example, a cell phone has different options from a camera. Both cell phone and camera are products (entities) that have different option sets (attribute sets).
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.
Thanks,
– Bijal Bhavsar 🙂
Magecracker 