Hi Jörg/Andrei/ChuckR,
Great start on a job that is IMO much needed if NOP is going to compete successfully as an enterprise level EComm system.
I am hoping you guys won't mind if I make a few suggestions in the first instance around the database structure/changes that I think could be added to the existing work to help deliver full "Stock/Inventory Control" capabilities to NOP.
Appreciative I've covered quite a bit in this post I'm also very aware that due to language & terminology differences some of the things I've highlighted here might not make immediate sense, rather than just dismissing something because of the differences & in the interest of us going forward as a team working to deliver this functionality to NOP could we use this as a starting point for a common understanding of both the Domain Model & terminology that we will need to get our heads around to be successful.
An example of the terminology differences would be ChuckR's comments on "shelf" while from the nature of Chucks comment it appears to me that what Chuck calls a "shelf" I call a Store or Warehouse, in this example for me the Store or Warehouse is the upper level of where stock is stored and below that level stores & warehouses have shelves or bays where particular products are stored or displayed in a store or warehouse.
As I said we need to get these sorts of differences sorted.
Now as for suggestions;
I'll start with the "ProductInventory" Tbl you added Jörg, btw you hit the nail on the head with this table and getting this right is the key to having a great underlying inventory control system.
What you appear not be aware of is (highlighted in ChuckR's post) that in about V2.8 or V2.9 NOP made a fairly significant change to it's product table structure to allow for stock control to be implemented in the future, unfortunately the job was not completed so there is some hangover stuff that maybe we can also clean up with this.
This is the "ProductInventory" Tbl as it currently stands
[Id]
,[ProductId]
,[WarehouseId]
,[StockQuantity]
,[ReservedQuantity]
,[PlannedQuantity]
,[MinStockQuantity]
,[MaxStockQuantity]
,[ReorderStockQuantity]
With the changes a couple of versions ago the primary key identifier for a product changed from the "Id" field on the "Product" Tbl to a combination of the "ProductId" and "AttributesXml" fields on the "ProductVariantAttributeCombination" Tbl it means that the Product Primary Key for the "ProductInventory" Tbl will need to be changed to;
[Id]
,[ProductId]
,[AttributesXml]
,[WarehouseId]
,[StockQuantity]
,[ReservedQuantity]
,[PlannedQuantity]
,[MinStockQuantity]
,[MaxStockQuantity]
,[ReorderStockQuantity]
While some might argue that "Id" field from the "ProductVariantAttributeCombination" Tbl would be a better Identifier for the product (what happens if the "AttributesXml" field changes) I do see some merit in the use of the combination of the "ProductId" and "AttributesXml" fields being used but we would need to include change logging for it to be managed correctly.
Once this has been sorted we will need to flow the changes around what a product PK is into the OrdersLine tbl as it still only uses the ProductID field from the product tbl.
In any event I'm open for discussion on the preferred Product Pk.
The next suggestion I'd make would be to have ALL stock on hand quantities for all locations (Stores & Warehouses) stored in the 1 table I.E. the "ProductInventory" Tbl. For this to happen could I suggest 1 of 2 approaches;
1. First would include adding a "LocationTypeId" field to the "ProductInventory" Tbl, and changing the "WarehouseId" field on the "ProductInventory" Tbl to "LocationId". The "LocationTypeId" would identify the row as being Quantity's on hand for a Store or a Warehouse and the "LocationId" on the "ProductInventory" Tbl would then be the Id field from either the Store or the Warehouse Tbl's.
2. Second option, Merge Store & Warehouse Tbl's into a "Location" Tbl that includes all Store Tbl fields and the addition of a "LocationTypeId" field then in the "ProductInventory" Tbl change the "WarehouseId" field to "LocationId"
I would advocate for the second option as the way forward primarily as going into the enterprise world NOP is going to need strong location management capabilities and making this change as part of fixing stock control makes sense now.
This is how the "ProductInventory" Tbl will look if you guys were to adopt the second option.
[Id]
,[ProductId]
,[AttributesXml]
,[LocationId]
,[StockQuantity]
,[ReservedQuantity]
,[PlannedQuantity]
,[MinStockQuantity]
,[MaxStockQuantity]
,[ReorderStockQuantity]
A future addition to NOP would include the ability to map the relationships between stores and warehouses (also see Chucks comments on this in his post). but I'll explain this further once the primary work has been done.
The next suggestion I have is pre-emptive to some changes I think would be highly beneficial to the sales transaction capabilities of NOP, fyi Layaway/Lay-by (Aust & NZ) is a much wanted feature that products like Magento have ignored.
[Id]
,[ProductId]
,[AttributesXml]
,[LocationId]
,[StockQuantity]
,[OrderReservedQuantity]
,[LayawayReservedQuantity]
,[OtherReservedQuantity]
,[PlannedQuantity]
,[MinStockQuantity]
,[MaxStockQuantity]
,[ReorderStockQuantity]
To accommodate the possible changes to the sales transaction capabilities you'll note that I have added the [OrderReservedQuantity], [LayawayReservedQuantity] and [OtherReservedQuantity] fields. As we get more into this discussion I'll better explain why I thing we should provide for this functionality.
The final thing that I'd suggest we look at at this time would be the inclusion of some Cost/RRP info at the product inventory level, wether that be in the "ProductInventory" tbl or in its own "ProductInventoryCost" tbl is neither here nor there but that said my preference would be for it to be held in the "ProductInventory" tbl, on that basis this is how I envisage the "ProductInventory" tbl would look if all the changes I propose were accepted by the group.
[Id]
,[ProductId]
,[AttributesXml]
,[LocationId]
,[StockQuantity]
,[OrderReservedQuantity]
,[LayawayReservedQuantity]
,[OtherReservedQuantity]
,[PlannedQuantity]
,[MinStockQuantity]
,[MaxStockQuantity]
,[ReorderStockQuantity]
,[CostFOB]
,[avgCostEx]
,[avgFreightCostEx]
,[RRPEx]
,[LastCostEx]
,[LastFreightCostEx]
,[PriceBookCostEx]
,[PriceBookFreightCostEx]
,[PricebookRRP]
,[VendorRebateId]
With respect the average & last costs these would initially be sucked up from an existing ERP or POS system in the longer term these numbers could be captured through NOP's own purchasing structure.
As for the inclusion of PriceBook costs, RRP and vendor rebate id this is info that is particularly needed for franchise/enterprise retail I've included it here as including it now allows us to nail the domain requirements.
Again with these suggested changes as the discussion develops I'll better explain everything.
In closing while I'm far from a gun coder (my strength is more as a domain specialist) I do alright so please accept this as an offer to get my hands dirty doing what coding work I can about these changes.
Cheers
Gaz
