Idea stock control for many branch

my company have a lot of branch please suggest how to control stock how can i search product what branch have this products

please suggest how to design database
Who is Participating?
so a very simple table design can be as below

Product_Inventory table

InventoryId (Primary Key)
ProductId (ForiegnKey To Products table)
BranchId  (ForiegnKey to branch master)

and so on

You can store inventory per branch for each product in the above table.

A simple query like the below will give you the total number of units present for a product at each branch

SELECT branchId, SUM(NoofUnitsInStock)
FROM Product_Inventory
WHERE productid = someProdId
Group By branchId

Hope it helps
do you want to centrally keep the stock data or want it to bind to branches?

Also, pls let me know a few attributes that you would like to store for product and branch. I can come up with an early design approach
teeraAuthor Commented:
all branch have there own server but it must update stock to mainserver to sever every day 2 time

and in every branch can search with main stock in case the product is not availa ble he will known may be it have a product in another branch
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.