Magento uses for products (categories, customers and customer addresses) the EAV paradigm.
This means that instead of having a single table and one column corresponds to one property, you have 3 tables (actually more, but I will get to that later).
- one for the entity where you keep the id and things that don't depend on the environment (craation time for example).
- one table that contains the attributes (color, size).
- one table that contains the values for entities relative to an attribute. (entity id, color id, color value).
Magento has more that one of these last tables. It has one for each attribute type. (datetime, varchar, text, ...).
The main table is
catalog_product_entity, the attribute table is
eav_attribute and the tables that hold the values for entities and attributes are
catalog_product_entity_* where * is
int, decimal, datetime, varchar, text.
Using this approach, magento introduces the store id in the
So for one entity and one attribute you can have more that one value.
The default values have the value for column
store_id zero and the values for specific stores have the column
store_id the actual id of the store.
When retrieving the values for frontend for a specific id, magento checks if there is a record for each attribute for that specific store id. If there is, it uses that, if no it checks for the value with store id zero.
But... even if the EAV paradigm is really extensible, it does not perform well. So for this magento introduced the flat tables.
* is the store id.
But these tables hold only a part of the product attribute values and you should not use them for writing.
Anything you write in there will be deleted the next time you rebuild the indexes.
I advice against writing directly in the database to clone products. It can easily lead to chaos.
You should use the internal API to clone products. See how the duplicate functionality works in the backend.