I'm trying to make a database for a construction depot (there where are stored materials for construction). I need to know all the materials stored (products in), all the products sold and all the products that had left. I'm thinking to do like this:
Products--> Deliver <--Depot (many to many) - here I see all the products in.
Depot--> Sell <--Products (many to many) - here I see what I have sold.
And what is left: To make a difference between "Deliver" and "Sell".
Is there something correct here? Have you another idea? Thank you for your help.
Another way to handle an inventory tracking system is to approach it like a double-entry accounting system. This would have you combine your Deliver and Sell tables into an inventory Movement table. Each movement would consist of two records, one with a positive quantity and one with a negative quantity.
The advantage of treating your inventory system this way is that it is easy to use an aggregate query to get the inventory position (stock on hand) of any product at any depot at a point in time.
EDIT: Table Structures...
PRODUCT
-ID (PK)
-Description
-...
DEPOT
-ID (PK)
-Name
-Location
-...
MOVEMENT
-ID (PK)
-DateTime
-ProductID (FK)
-DepotID (FK)
-Quantity /* Positive=Increase, Negative=Decrease */
-OffsettingMovementID (FK) /* Points to the other half of the entry */