Help with designing sales and inventory management system

Question

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 ?

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.)