cancel
Showing results for 
Search instead for 
Did you mean: 

Possible data inconsistency in "cataloginventory_stock_item"

0 Kudos

Possible data inconsistency in "cataloginventory_stock_item"

Feature request from flancer64, posted on GitHub Jun 07, 2016

Hello,

I discovered DB structure and found that there is extra field named "website_id" in "cataloginventory_stock_item".

Each stock item is related to "cataloginventory_stock" (foreign key: stock_id). Stock record in "cataloginventory_stock" has field "website_id" is related to "store_website". So, each record in "cataloginventory_stock_item" is related to record in "cataloginventory_stock" and is related to record in "store_website".

Field "website_id" in "cataloginventory_stock_item" (without references/foreign key to "store_website") can contain any value - not only different from the "website_id" in related "cataloginventory_stock" record, but really any smallint(5) UNSIGNED value. mage2_stock_item_website_id

I suppose, field "cataloginventory_stock_item.website_id" is extra field and should be removed.

Thanks.

2 Comments
Not applicable

Comment from cpartica, posted on GitHub Jun 21, 2016

The question is if we could have an item into a inventory in a website that's different from it's parent website, probably not I'll clear that out with our team and get back to you Thank you for your posting

Not applicable

Comment from flancer64, posted on GitHub Jun 21, 2016

Hello, @cpartica .

"cataloginventory_stock_item" is a "data" table (contains raw data), not a "flat" table (contains "indexed" data - from other tables, like a "customer_grid_flat" or "sales_order_grid"). There is other table - "cataloginventory_stock_status". There is one only new field ("stock_status") in this table that is not from "cataloginventory_stock_item". So, "cataloginventory_stock_status" is not a pure "flat" table but mostly - is. IMHO, "stock_status" field should be moved into "cataloginventory_stock_item" table - this is attribute of the StockItem entity, not standalone entity.

This is SQL for "cataloginventory_stock_status":

CREATE TABLE cataloginventory_stock_status (
  product_id int(10) UNSIGNED NOT NULL COMMENT 'Product Id',
  website_id smallint(5) UNSIGNED NOT NULL COMMENT 'Website Id',
  stock_id smallint(5) UNSIGNED NOT NULL COMMENT 'Stock Id',
  qty decimal(12, 4) NOT NULL DEFAULT 0.0000 COMMENT 'Qty',
  stock_status smallint(5) UNSIGNED NOT NULL COMMENT 'Stock Status',
  PRIMARY KEY (product_id, website_id, stock_id),
  INDEX CATALOGINVENTORY_STOCK_STATUS_STOCK_ID (stock_id),
  INDEX CATALOGINVENTORY_STOCK_STATUS_WEBSITE_ID (website_id)
)

(product_id, website_id, stock_id) - is a primary key, we have 2 indexes and no foreign keys. This is OK for "flat" tables (used for searches mostly).

This is SQL for "cataloginventory_stock_item":

CREATE TABLE cataloginventory_stock_item (
  item_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Item Id',
  product_id int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Product Id',
  stock_id smallint(5) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Stock Id',
  ...
  website_id smallint(5) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is Divided into Multiple Boxes for Shipping',
  PRIMARY KEY (item_id),
  UNIQUE INDEX CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID (product_id, stock_id),
  INDEX CATALOGINVENTORY_STOCK_ITEM_STOCK_ID (stock_id),
  INDEX CATALOGINVENTORY_STOCK_ITEM_WEBSITE_ID (website_id),
  CONSTRAINT CATINV_STOCK_ITEM_PRD_ID_CAT_PRD_ENTT_ENTT_ID FOREIGN KEY (product_id)
  REFERENCES mage2.catalog_product_entity (entity_id) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT CATINV_STOCK_ITEM_STOCK_ID_CATINV_STOCK_STOCK_ID FOREIGN KEY (stock_id)
  REFERENCES mage2.cataloginventory_stock (stock_id) ON DELETE CASCADE ON UPDATE RESTRICT
)

We have simple primary key, (product_id, stock_id) is a unique key, we have 2 indexes and 2 foreign keys. This is OK for "data" tables (constraints should provide consistency for stored data).

So, if "cataloginventory_stock_item" is a "data" table and stores data consistently and "cataloginventory_stock_status" is a "flat" table and is used like a view for the grids/selects/... we should remove "cataloginventory_stock_item.website_id" and use "cataloginventory_stock.website_id" instead when indexing data. Each row from "cataloginventory_stock_status" identified by PK (product_id, website_id, stock_id), each row in "cataloginventory_stock_item" has unique pair (product_id, stock_id), each stock has its own website_id (see "cataloginventory_stock.website_id"). "website_id" is an extra field in "cataloginventory_stock_status" PK too cause "website_id" is hardly bound with "stock_id" in "cataloginventory_stock" table.

IMHO "webisteid" should be removed from all "cataloginvenotry..." tables. Please, see \Magento\CatalogInventory\Model\Configuration::getDefaultScopeId There is "scope_id" in the code, not "website_id" (see https://github.com/magento/magento2/pull/5078/files ) and "scope_id" (in the code) == "stock_id" (in the DB).

This is my own point of view.

Thanks.