Inventory system table design

[coyote]

Am designing an Inventory Database System.
I have the following Tables
1.tblProducts
2.tblPurchases
3.tblPurchaseDetails (a join table with PurchaseID and ProductID as Key Fields)
4.tblIsues
5.tblIssueDetails (a join table with IssueID and ProductID as Key Fields)

On tblProducts there is a field QtyInStock which stores the current stock at any given time.

Once a product is purchased, on the purchase form as you save the record an update query is also run which updates the qtyinstock on the tblProducts. The same happens when you issue a product.

My problem comes at the end of the month when I want to calculate the opening stock of the month.
Is there a way I can store these quantities so that I have an analysed way of viewing the increament and decrease of the stocks.

Simply put like this

On the first day of the month the opening stock was X amount,then on the second day a purchase was made and x was increased to y amount.I want to view x and y which is not possible when I use the update query which updates x.

Cheers

 

[Thinh]

you need a transaction log that record all the activity of you inventory

TransactionID, Action, Date, Quantity, CurrentInventory
1, Inflow, 3/3/09, 100, 1000
2, outflow, 3/4/09, 100, 900
etc on

 

[RuralGuy]

Some would say the Quantity On Hand is a dynamic number.

 

[gemma-the-husky]

the best way is NOT to store a stock on hand, but to caclulate it by adding transactions (goods in /out/ adjustments)

therefore what you really need (imo) is one SINGLE table to include ALL these stock movements, with a flag of some sort to indicate the type of stock movement. (rather than separate tables for each type of movement)

If you also have a active/dead flag on these movements. you can manage a stock take/opening balance by "deading" all the transactions up to a given date, and replacing these with a single posting, representing the physical stock balance.

 

[neileg]

the best way is NOT to store a stock on hand, but to caclulate it by adding transactions (goods in /out/ adjustments)

therefore what you really need (imo) is one SINGLE table to include ALL these stock movements, with a flag of some sort to indicate the type of stock movement. (rather than separate tables for each type of movement)

If you also have a active/dead flag on these movements. you can manage a stock take/opening balance by "deading" all the transactions up to a given date, and replacing these with a single posting, representing the physical stock balance.

Couldn't have put it better myself.

 

[coyote]

Thanks Guys for your help, but am still in a bit of problem because what is happening here is that this system is used to manage a few branches also.

I have a main products table which tblProducts. This is the main store where once the products are bought are recorded. Then they are issued to branches which stocks different products but all are issued from the main store. What I have is a table called tblBranchProducts which stores all the products associated with a branch. So I am managing the main store and the branches at the same time.
At the end of the month I want to have the main store closing stock and also the branches closing stock.

 

[gemma-the-husky]

so you need to include a location field of some sort

then given a product, you will hold some stock in the central store, and some at the various branches. you will need to include the location as part of an transaction both in and out, in order to be able to evaluate the stock at a given location, as well as the overall stock

 

[coyote]

Sorry guys I was not clear. Here is the scenario.
I have this application where products are purchased and stored in the main store.Then they are issued to branches. I have eight branches and each branch stocks the same type of products. The sales are done from the branches and a product can be sold in retail or wholesale.A sale can also be paid in cash or as an Invoice.
What I have done is create a table tblBranchProducts which is a join table between tblProducts and tblBranches.

Attached is the backend of the application with the tables and relationships.
Any help greatly appreciated.

There is a front end to this db