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 ?

  • For any future questions you might have, It would likely help you to understand this (and us, too) if you try creating a minimal reproducible example using standard SQL, with some sample data, and what you want any potential output query results to look like.
    – Hannah Vernon
    Dec 17, 2020 at 21:57

1 Answer 1


Your 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 Price and PurchasedDate, and a foreign key field to reference the Product table.

Also, tables like Purchases and 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 PurchasedDate, 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 SalesOrders and SalesLines.

PurchaseOrders are one-to-many PurchaseLines. (SalesOrders are one-to-many SalesLines.)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.