Possible data inconsistency in "cataloginventory_stock_item"

[apiuser]

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.

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

Thanks.

[apiuser]

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

[apiuser]

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.