Inventory configuration Design and DB structure
In the desirable state, MSI will bring own scoping for Inventory configuration, which would be mapped to Magento 2 scopes (Global, Websites, Stores & Views).
MSI has next scopes:
- Global
- Source Configuration (configuration of the particular warehouse or brick and mortar store)
- SourceItem Configuration (configuration of the product located on particular Source)
Currently, we identified just 2 Source level configurations: Notify the Quantity Below (Sets the quantity of Product per Soruce that triggers a Quantity Below notification), Backorders (specified on the level of Source and calculatable on the level of Stock based on all Source level configurations assigned to a Stock)
Along with Source Configuration, there is also Stock configuration, which affects Salability characteristics of a Product
- Global
- Stock Configuration
- StockItem Configuration
Global Inventory configurations accessible via (Stores -> Configuration -> Catalog -> Inventory) should contan just Global Stock configuration options:
Source level configuration options (Notify the Quantity Below and Backorders) should be added as an additional section to the Source editing page:
Stock level configuration options should be added as an additional section to the Stock editing page:
SourceItem configuration options (Notify the Quantity Below and Backorders) should be added to the grid of SourceItems on Product Editing page
StockItem configuration options which affect salability characteristics of a product are reachable via Advanced Inventory button on the Product Editing page
Taking into account that determining a value of particular option the system should make a series of fallbacks (upper level by scope SourceItem -> Source -> Global) until it retrieves the specified value, for example,
we need to determine backorders configuration option for particular SourceItem by SKU and Source Code, if the value is set - the system returns this value, if the value is absent in the storage, the system should proceed with fallback to Source level and then to Global one.
Read more about the design of Inventory Configuration Services here
We need to organize a storage of configuration options which would be effective for these kinds of fallbacks.
sku (nullable = true) | source_code (nullable = true) | stock_id (nullable = true) | config_option (string) | value (string)
Such structure helps to store both Source and Stock configuration on all levels into the same table. For example, to retrieve Source Item configuration options next SQL query should be executed:
select * from inventory_configuration where sku = %sku% and source_code = %source_code% and stock_id is NULL
To retrieve Source level configuration options we need to execute next query:
select * from inventory_configuration where source_code = %source_code% and sku is NULL and stock_id is NULL
We can even implement a fallback programmatically via pure SQL using LEFT JOIN by config_option between two queries above and applying IFNULL() to make a fallback to Source level if the option is not set on SourceItem level.
Here are you can see examples of how configurations on different scopes are stored:
- Storing backorders on the source item scope
SKU-1 | Source-A | null | backorders | 1
- Storing backorders on the source scope
null | Source-A | null | backorders | 1
- Storing can_manage_stock configuration option on the stock item scope
SKU-1 | null | Stock-1 | can_manage_stock | 0
- Storing can_manage_stock configuration option on the stock scope
null | null | Stock-1 | can_manage_stock | 0
- Such storage structure is very flexible and easily extensible as we can easily add new configration option(-s) if needed and store them in the same table without altering table and even not re-writing existing SQL queries.
-
We are loosing strong typing for storing config option values, as now the value of all config options would be represented as
string. But this is not a big issue taking into account that for each particular option we would have dedicated service class managing it, so all the validations and type checks could be done there. Thus, the business logic which will work with Inventory Configuration would rely on strict typed values. -
Currently we have 11 Inventory configuration options defined in Magento 2, each one will occupy 1 record in DB table if specified. So, potentially if all the configuration options are specified on SourceItem and StockItem scopes (the lowest ones) for given product - the system will create 11 records in the database table. Having configuration options specified on the lowest scopes for many products will lead to overwhelming the whole table and thus performance degradation. The poll in Twitter, and MSI Slack channel have been created to gather feebacks regarding how typical is to adjust Inventory Configurations options on the Product level