How are store/website-specific (or non-global) products attributes stored/queried in the Magento mysql database?

Question

First of all, I know I should be using the model rather than working on the database directly. That being said, does anyone know exactly how Magento handles non-global product attributes?

I have 2 websites in core_website: Admin (website_id = 0) and Main Website (website_id =1). I also have two stores in core_store: Admin (store_id = 0) and Default Store View (store_id = 0). It seems that whether or not a product (or category?) attribute is global in scope is stored in catalog_eav_attribute.is_global. A value of 0 corresponds to a scope of "Store View," a value of 1 corresponds to "Global," and 2 corresponds to "Website." So far so good.

Now, if I wanted to get the value of a store-specific attribute like "name" (eav_attribute.attribute_id = 71; eav_attribute.backend_type = 'varchar'; catalog_eav_attribute.is_global = 0) for all my products, you would think I would do something like this:

SELECT *
FROM catalog_product_entity_varchar
WHERE attribute_id = 71
AND store_id = 1

But that returns nothing. All of the names are actually in rows with store_id = 0. As far as I can tell the only attributes in the database that are stored with store_id = 1 are 'url_key' and 'url_path'. So how does Magento store these values? And how does Magento retrieve them?

Are all values initially(or also) stored with store_id = 0 as a kind of default, until a different value than that one needs to be stored? When a store-specific value that is different from the admin value needs to be stored, does magento then create a new row with store_id = 1 (or whatever store_id it is)?

If that - or something like that - is the case, then how does Magento retrieve store-specific values? Does it check catalog_eav_attribute.is_global first for the attribute in question? If it is non-global, it could then first query with store_id = 1, and if that returns nothing, then query with the default store_id = 0?

I guess my main question is how does magento actually do it. Secondarily, why does magento do it this way instead of storing values with the actual store_id? Also, if I were to write a query, should I query both store_id = 0 and store_id = 1 and choose the right value based on whether or not the attribute is global and whether or not there is a value present for store_id = 1?

Answer 1

You seem to have it figured out. At least you have a good idea on how things are done.
To summarize and confirm your suspicions:
All the attribute values are stored in catalog_product_entity_* where * can be anyone of theses: decimal, int, varchar, text, datetime depending on the attribute type (backend_type).

There are also other tables that keep the data related to tier pricing and images but let's leave that for now.

Attribute table definition

Each of the tables have the following columns:

value_id       - just an increment id for the table
entity_type_id - the entity type id for the product (always the same)
attribute_id   - reference to the attribute
store_id       - reference to the store view
entity_id      - reference to the product
value          - actual value

There is a unique constraint on these columns entity_id,attribute_id,store_id. This means that for one product and one attribute you can have only one value for a store view.

Now the part where you are right.

store_id = 0 means that the value stored there is a default value.
If there is no value specified for a specific store view (store_id >= 1) then this value will be used.
If the attribute is set as global then the value for store_id = 0 will be used even if you have values for store_id = 1.

Examples

To get an idea of how the values are retrieved put this code in some file and run it (make sure the flat catalog is disabled - more on that later, and make sure you created an instance of the application first using Mage::app()):

Store view attribute

$collection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToFilter('name', 'some_name');
echo $collection->getSelect();

The code above means that I want to retrieve a list of products with the name some_name.
the sql query associated to the collection looks like this:

SELECT 
    `e`.*, 
    IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name` 
FROM 
   `catalog_product_entity` AS `e` 
    INNER JOIN 
         `catalog_product_entity_varchar` AS `at_name_default` 
               ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_name_default`.`attribute_id` = '96') AND 
                  `at_name_default`.`store_id` = 0 
    LEFT JOIN 
          `catalog_product_entity_varchar` AS `at_name` 
               ON (`at_name`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_name`.`attribute_id` = '96') AND 
                  (`at_name`.`store_id` = 1) 
WHERE 
    (IF(at_name.value_id > 0, at_name.value, at_name_default.value) = 'some_name')

Ugly huh?
Because the name attribute (id 96 in my case) is a store view scope attribute (is_global = 0) Magento joins twice with the table catalog_product_entity_varchar (the one that holds the name), once for the current store view and once for the detault store view (id = 0). adding a condition:

 IF(at_name.value_id > 0, at_name.value, at_name_default.value)

So if there is no value for the store id 1, use the default value.

Global attribtue

Now let's see what happens if we filter by a global attribute.

$collection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToFilter('weight', '1');
echo $collection->getSelect();

The sql printed looks like this:

SELECT 
    `e`.*, 
    `at_weight`.`value` AS `weight` 
FROM 
    `catalog_product_entity` AS `e` 
     INNER JOIN 
         `catalog_product_entity_decimal` AS `at_weight` 
          ON (`at_weight`.`entity_id` = `e`.`entity_id`) AND 
             (`at_weight`.`attribute_id` = '101') AND 
             (`at_weight`.`store_id` = 0) 
WHERE 
     (at_weight.value = '1')

So one single join with the table catalog_product_entity_decimal for the store id = 0.

Website attribute

If the scope of the attribute is website everything happens just like it does for the store view scope, because Magento creates a line in the attribute values table for each store view in the current website when saving the product.
If you want to try it use the attribute status in the examples above.

Flat Catalog
I promised earlier some explanations about "flat catalog".
For performance reasons Magneto introduced this feature (I don't remember the version).
Basically a cron runs (or you can run it by hand) and transforms the EAV approach for the products and categories into flat tables. One for each store view you have (except store id = 0).
This means one attribute will be transformed into one column in the new table. The new table is called catalog_product_flat_{store_view_id_here}.
This avoids the numerous left/inner joins when wanting the values for some attributes.
but again, for performance reasons, not all the attributes are added as columns in the flat tables (for products only. For categories all of them are added).
Only the attributes marked in the backend with Use in product listing are transformed into columns.
You can turn on/off this feature from System->Configuration->Catalog->Frontend->Use Flat Catalog Product (or Use Flat Catalog Category).

Even if turned on, the flat tables are used only in the frontend. The backend still uses the EAV approach.

Conclusion
My conclusion is that it is almost impossible to write your own queries to retrieve data directly from the DB. You should use the models and collections that magento provides. It saves a lot of mental health.

I hope I made things a little clearer for you.