In my recent Salesforce project, we came across tough problem storing product stocks coming from integration with ERP API. Initially there were only 10 warehouses and thus there was 10 fields on Product2 object,one for each warehouse. Although, client requested to prepare Salesforce for variable number of warehouses up to approximately 20 (meaning today there may be 10 but in a month 1 location can be closed and 2 new opened, making it 11). Client also wants to report on stocks and create tasks if conditions are met (E.g. stocks are low need to place order at manufacturer)
Initial idea to solve this was create custom object Inventory__c of master-detail relation to product. However there is 450k products in the database.
450k Products x 20 Warehouses x roughly 2KB = 18 GB
That much additional storage is unnaceptable.
Other ideas was to use Big Objects or External Objects but these does not suit requirements (reporting mostly) and requires additional licenses as well.
Our final idea is to create number of fields on Product2 (Stocks_WH_1__c, Stocks_WH_2__c, Stocks_WH_3__c...) to make sure 20 + some unexpected warehouses will fit, and use only as many as there will be warehouses returned from the API.
This does not look like optimal solution, however seems to meet requirements and avoides additional licenses.
My question is:
Is ther any better(under this conditions) solution for such large number of records?