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
teeraAsked:
Who is Participating?
 
sameer_goyalCommented:
so a very simple table design can be as below

Product_Inventory table

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

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
0
 
sameer_goyalCommented:
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
0
 
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
0
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.