as Magento has almost 40 tables for the products and category’s alone! To understand why, let us use the product table as an example.
Rather than store all product information in one table, Magento splits this information up into sub tables. The top table in this hierarchy is catalog_product_entity. If you take a look at this table in phpMyAdmin, you will see that it includes simple base information for a product and does not appear to include any useful information other than the SKU! Fortunately, using this table it is possible to build a full product record from the attribute and value tables.
To start building a full product record, you will need to start joining attributes to the product entity table. Before you do this, take a look at the table called eav_attribute. eav_attribute is the main attribute store for Magento and is used to store attributes for all different entities (product, customer, order, category etc).
Open this table in phpMyAdmin and click browse. Notice that there are hundreds of different attributes, some even with the same name? At first this confused me because I wasn’t sure how Magento could differentiate between the two different attributes called name. How did Magento know which one was for the product and which one was for a category? As is usually the case with Magento, a small bit of research led me to the extremely simple answer: entity_type_id! Each entity (product, category, customer etc) is given an entity_type_id. To find this out, go back to catalog_product_entity and look for the entity_type_id field. The value for every record in that table should be 4, as this has been designated as the entity_type_id for products. If you were to look in catalog_category_entity you should see a different entity_type_id. Using this value and the attribute code, it is possible to load the attributes for a product, or any entity.
Now that you can get attributes and entities, it is time to start getting values. Values are separated across several different tables for reasons that I will go into shortly. For now though, just take a look at all tables that begin with catalog_product_entity. The way the values are split depends upon their type. For example, all prices and other decimal attributes are stored in catalog_product_entity_decimal where as all short text strings are stored in catalog_product_varchar. To figure out which table each attribute is stored in, Magento uses the column backend_type in the table eav_attribute. If you run the following query you should be able to find out the backend type for the product attribute ‘name’
Hopefully the above query returned the backend_type varchar, which is the correct type for name and all other short text strings. Based on what was said above, we can determine that the value for the name attribute will be stored in catalog_product_entity_varchar
Rather than store all product information in one table, Magento splits this information up into sub tables. The top table in this hierarchy is catalog_product_entity. If you take a look at this table in phpMyAdmin, you will see that it includes simple base information for a product and does not appear to include any useful information other than the SKU! Fortunately, using this table it is possible to build a full product record from the attribute and value tables.
To start building a full product record, you will need to start joining attributes to the product entity table. Before you do this, take a look at the table called eav_attribute. eav_attribute is the main attribute store for Magento and is used to store attributes for all different entities (product, customer, order, category etc).
Open this table in phpMyAdmin and click browse. Notice that there are hundreds of different attributes, some even with the same name? At first this confused me because I wasn’t sure how Magento could differentiate between the two different attributes called name. How did Magento know which one was for the product and which one was for a category? As is usually the case with Magento, a small bit of research led me to the extremely simple answer: entity_type_id! Each entity (product, category, customer etc) is given an entity_type_id. To find this out, go back to catalog_product_entity and look for the entity_type_id field. The value for every record in that table should be 4, as this has been designated as the entity_type_id for products. If you were to look in catalog_category_entity you should see a different entity_type_id. Using this value and the attribute code, it is possible to load the attributes for a product, or any entity.
Now that you can get attributes and entities, it is time to start getting values. Values are separated across several different tables for reasons that I will go into shortly. For now though, just take a look at all tables that begin with catalog_product_entity. The way the values are split depends upon their type. For example, all prices and other decimal attributes are stored in catalog_product_entity_decimal where as all short text strings are stored in catalog_product_varchar. To figure out which table each attribute is stored in, Magento uses the column backend_type in the table eav_attribute. If you run the following query you should be able to find out the backend type for the product attribute ‘name’
Hopefully the above query returned the backend_type varchar, which is the correct type for name and all other short text strings. Based on what was said above, we can determine that the value for the name attribute will be stored in catalog_product_entity_varchar
No comments:
Post a Comment