I need to make a sale and inventory management system. I’m stuck on the purchasing price and stock for the product.
Let’s say you bought 10 t-shirt for $10 per t-shirt and selling them at $20 per t-shirt.
You sold 8 t-shirt quickly and decided to buy 10 more. Now, the price is $12 per t-shirt.
Now , you’ve total 12 t-shirt in stock, where 2 t-shirts purchasing price is $10 and 10 t-shirts purchasing price is $12.
How can I make a product table which will keep track of the purchasing price at different times for the product and also the total stock ?
Product table shouldn’t store the
Price at all. You need a separate table called something like
Purchases which has a row for every purchase made, the
PurchasedDate, and a foreign key field to reference the
Also, tables like
Sales are normally broken out further into two tables each. One table having header information and the other the line information. For example,
PurchaseOrders would store the header information like
PurchasedBy, maybe the
Company purchased from (if only one Company can be on one Order), where the
PurchaseLines table would be a row for every item purchased on that
PurchaseOrder, with the
Quantity (per item),
Price, and foreign key field to relate back to the
PurchaseOrders table. Similar structure for
PurchaseOrders are one-to-many
SalesOrders are one-to-many