Multi warehouse inventory management

Get Help Database
1

Hello,

How should I create the database for multi-warehouse inventory management?

For example:
1.) There are 10 products for 4 warehouses
2.) Products can be transferred between warehouses

Should I start with
1.) 1 collection
2.) 2 collections, 1 for warehouse & 1 for product

if 2.) what relationship should I select?

Any example I could learn from?

Thanks,
Cookie

2

Hi @Cookie,

I’d suggest having 2 collections: [Warehouses] and [Products]. Set a relationship between them, 1:many - one Product can belong only to one Warehouse, but a Warehouse can have many Products.

So when you create a new Product - you can select a Warehouse to store it. When you transfer the product to another warehouse - you update this relationship.

Best regards, Victor.

3

Thanks @Victor ,:hugs:

Just explored options last night. There’re cases such as:

  • Warehouse A have 2 apples
  • Warehouse B have 3 apples
  • Warehouse A transfer 1 apple to warehouse B
  • Reports updated for for all warehouse

How could I set up 1:many to do this?
It seem like I can’t link apples to A and B at the same time.

Thanks,
Cookie

4

Hi @Cookie,

Ok, this will require a different scheme. My previous advice is applicable to the case when each product can be uniquely identified (e.g. it’s a car, a box, etc.) and therefore has a separate record in the collection.

In your example you are using different type of products (apples), where there are no unique IDs for each product item (each apple), and as I see you’d like to store only the quantity of each product in each warehouse.

One of the solutions could be having 3 collections:
[Warehouses] - for the warehouses
[Products] - this collection will contain the names and descriptions of the product, but it is NOT linked to warehouses directly
[Inventory] - this is the main collection where you store the info about your inventory. This collection should have a relationship with Warehouses (each Inventory record can belong only to one Warehouse, but Warehouse can have multiple Inventories).
Also each Inventory should belong to one Product (and of course Product can have several Inventories).
And each Inventory record have a Quantity field, where you store the quantity.

With this approach, you will have a list of Inventory records for each Warehouse, and you can change the quantity in these records as you want.

Though such set up could be not very trivial, as there are some things which needs to be addressed (what happens if inventory goes to 0, how to get access to 2 “current inventory” records when you’re transferring it from one warehouse to another, etc.). Though as for me this seems to be achievable in Adalo.

Best regards, Victor.

1 Like