Magento 1 – Relationship Between qty in Stock Item and Stock Status Tables

inventorymagento-1stock

In Magento 1, Inventory information is stored in a Stock Item object. Each Stock Item object is associated with a product object. Magento persists Stock Item objects to the cataloginventory_stock_item table.

However, each Stock Item object has a related Stock Status object. Stock Status appears to be an (index|cache|de-normalized) table that stores a product's stock status as in integer constant, and allows you to associate multiple stock items and products with a different website_id. Magento persists stock status objects to the cataloginventory_stock_status. The class constants and table schema for this are below.

class Mage_CatalogInventory_Model_Stock_Status extends Mage_Core_Model_Abstract
{
    const STATUS_OUT_OF_STOCK       = 0;
    const STATUS_IN_STOCK           = 1;
}

mysql> describe cataloginventory_stock_status;
+--------------+----------------------+------+-----+---------+-------+
| Field        | Type                 | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+---------+-------+
| product_id   | int(10) unsigned     | NO   | PRI | NULL    |       |
| website_id   | smallint(5) unsigned | NO   | PRI | NULL    |       |
| stock_id     | smallint(5) unsigned | NO   | PRI | NULL    |       |
| qty          | decimal(12,4)        | NO   |     | 0.0000  |       |
| stock_status | smallint(5) unsigned | NO   |     | NULL    |       |
+--------------+----------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

However, for reasons that aren't entirely clear, there is also a qty column in this table.

What is the relationship between these two qty columns?

Which is the source of truth for qty and which is the indexed/cached value? Is the value in stock_status simply a cached version of what's in the stock item table? Or is there a way to configure Magento to manage stock on a website level? Or is the relationship something other than what I've described?

Best Answer

It looks to me like qty in cataloginventory_stock_item is the source of truth, and qty in cataloginventory_stock_status is the cached/indexed value. Rebuilding the Stock Status index appears to always copy the value from cataloginventory_stock_item into cataloginventory_stock_status. If there are multiple websites, there will also be a record for each product_id/website_id combo in cataloginventory_stock_status. This suggests the possibility of different stock levels per website, but as far as I can tell the qty value is always the same for each one.

I think this may be the beginnings of a multi-stock feature as suggested by Marius in this post: The purpose of the table cataloginventory_stock

Related Topic