magento2: Database Schema: Incorrect Unique Indexes

Hi,

Regarding unique key on (product_id,website_id) in cataloginventory_stock_item table it’s legacy, and in the scope of Multi Location Inventory track has already been fixed, but it’s still not in mainline. Correct index is (product_id,stock_id) as you mentioned in your post. Having (product_id,website_id) index you will stuck with conflicts implementing Multi Location Inventory (MLI).

Knowledge about Website_Id are already eliminated from CatalogInventory data interfaces both from https://github.com/magento/magento2/blob/develop/app/code/Magento/CatalogInventory/Api/Data/StockInterface.php and https://github.com/magento/magento2/blob/develop/app/code/Magento/CatalogInventory/Api/Data/StockItemInterface.php

You can still have the same stock_id associated to multiple website_ids or vice versa

It’s true, so in the scope of MLI we will deliver Link interface which describe the relationship between Source (MLI interface which describes physical Stock extending basic Stock interface from CatalogInventory module) and Website.

<?php
/**
 * Copyright © 2016 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */
namespace Magento\MultipleLocationInventory\Api\Data;
/**
 * @api
 */
interface SourceWebsiteLinkInterface extends \Magento\Framework\Api\ExtensibleDataInterface
{
    /**
     * @return int
     */
    public function getSourceId();
    /**
     * @param int $sourceId
     * @return $this
     */
    public function setSourceId($sourceId);
    /**
     * Get website id
     *
     * @return int
     */
    public function getWebsiteId();
    /**
     * Set website id
     *
     * @param int $websiteId
     * @return $this
     */
    public function setWebsiteId($websiteId);
    /**
     * Retrieve existing extension attributes object.
     *
     * @return \Magento\MultipleLocationInventory\Api\Data\SourceWebsiteLinkExtensionInterface|null
     */
    public function getExtensionAttributes();
    /**
     * Set extension attributes object.
     *
     * @param \Magento\MultipleLocationInventory\Api\Data\SourceWebsiteLinkExtensionInterface $extensionAttributes
     * @return $this
     */
    public function setExtensionAttributes(
        \Magento\MultipleLocationInventory\Api\Data\SourceWebsiteLinkExtensionInterface $extensionAttributes
    );
}

There is also Repository interface to manage this entity.

All the links belonging to Source (Stock) would be available through the Source DTO

<?php
/**
 * Copyright © 2016 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */
namespace Magento\MultipleLocationInventory\Api\Data;
use Magento\MultipleLocationInventory\Model\Source;
/**
 * @api
 */
interface SourceInterface extends \Magento\Framework\Api\ExtensibleDataInterface
{
...

    /**
     * @return \Magento\MultipleLocationInventory\Api\Data\SourceWebsiteLinkInterface[]
     */
    public function getWebsiteLinks()

    /**
     * @param \Magento\MultipleLocationInventory\Api\Data\SourceWebsiteLinkInterface[]
     * @return $this
     */
    public function setWebsiteLinks(array $websiteLinks)
}

Hi @SchumacherFM. Thank you for your report. The issue has been fixed in magento/magento2#27399 by @serhiyzhovnir in 2.4-develop branch Related commit(s):

• 9c5528ff66f77990dd05370c8ddade8e522a4d0e
• e4280560511769ad3145e6f3880c6ae3a3007d5f
• 9735426c8d2f127b4c9bc373dccc3051fe21c9c8
• c83fbc6f4b505ff2d9ded16b1bce11ccc761d535
• 8e4a197b11cde6e4b3d60d3fff34e70c0fd28682

The fix will be available with the upcoming 2.4.0 release.

Hi @korostii We are not able to backport all fixed issues in 2.2.0 release to 2.1 and 2.0 releases. We will backport the most critical issues based on PO decision or the most up voted issues from public GitHub. Also, if we will get a pull request from Magento Community with the backport, we will accept it and deliver with upcoming patch releases.

@SchumacherFM fixed in develop, not yet backported to 2.0 and 2.1.

Still tracking internally as MAGETWO-33564 for mainline fix