Product/Stock Multi-Inventory issue with NHibernate

Question

Hey there,

I'm working on an ERP application where multiple inventories need to be dynamically supported, ie. each depot/branch needs a separate stock value for each product. One approach - avoiding using static table columns - is to have a separate Stock table as follows:

[Product]
Code
...

[Branch]
Code
...

[Stock]
ProductCode
BranchCode
StockValue
...

This effectively becomes a many-to-many relationship separated by the Stock table. Immediately there appears to be pitfalls in this approach, for instance:

• If there are 5 branches (depots), and 50k product lines, then there will be 5*50k Stock lines. Is this excessive?
• Each time a Product is added, 5 new Stock lines need to be added - one for each Branch.
• Each time a Branch is added, 50k new Stock lines need to be added.

The main rationale behind this is to avoid using static columns (which may lead to modifying the mapping files as new Branches are added). So it is supposed to be more dynamic.

Has anyone worked with a similar concept in the past and may perhaps have a more efficient solution? Or if this appears to be a suitable solution, then what NHibernate association method may be most effective?

Thanks.

Answer 1

I would create a separate entity -- Warehouse and Stores are common names -- as a container for stock. Then create a many-to-many relationship between a Branch and the Warehouses they can access Stock from. This gives you the flexibility to have stock stored at the branch and another location (rented space) or allow multiple branches to pull stock from multiple warehouses. Robust ERP systems further classify stock locations into shelf and position, for example.

Unless every product is stocked at every branch then I don't see that your list of potential pitfalls are valid. Using the Warehouse model, Stock is a record in a many-to-many table containing the number of items in stock at a warehouse. If the record does not exist than there are none at that location.