Idea stock control for many branch

Posted on 2012-09-16
Medium Priority
Last Modified: 2012-09-21
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
Question by:teera
  • 2

Expert Comment

ID: 38404611
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

Author Comment

ID: 38416735
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

Accepted Solution

sameer_goyal earned 2000 total points
ID: 38416771
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month15 days, 10 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question