How to store information on Product stocks with variable number of warehouses

Question

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?

Answer 1

I think you've outlined all the possible solutions to storing data in Salesforce. Because of your requirements which include using standard reporting and automation, you can't consider all the solutions.

Big Objects: While it can easily handle the data volume, you can't have triggers or reporting.

External Objects: You can report on external objects, but you still can't have triggers.

Custom Fields: This does save you on data storage, but starts to add other complexity. For example, you would need to make sure your automation is watching all stock fields for changes. This might lead to having 20 record-triggered flows for each automation you want and then you need to add more if the number of warehouses grows.

Reporting could also become more complex. For example, if I want to see inventory of all my warehouses in a particular country or region, I'd need to know which of the 20 columns to bring into my report. And if I wanted to total across all those warehouses, I'd need to make some report-level formulas.

Junction Object: This is the most future-proof solution. I could see a future requirement come up where low stock might be defined by warehouse. If you have a junction object, you could just add a field to the junction object and reference that in a single record-triggered flow to create a task.

Pricebook Entries: One benefit of pricebook entries is they don't get counted for storage usage. I don't know if you'd have performance problems, but you could potentially create a pricebook for each warehouse and then use pricebook entries to store your inventory. One disadvantage here is that you can't have record-triggered automation on them, but I would think you could use scheduled jobs to process them and create low stock alerts.

Personally, I would go the junction object route. I think you'll find a lot of challenges with multiple fields.