I am trying to design a database for a Stock and Sale System and I have the following issue.
Suppose there is an item in stock that costs $ 100 (product cost, not the selling price), but there are only 3 pieces to sell, then the customer decides to buy another batch of the same product, but this time the lot comes to a different price, $ 76 for example.
The purchase cost is very important, because on it the percentage of profit is calculated, so if the cost is different, the selling price will be different too.
I wonder how to make the seller sell each piece to its corresponding price.
I am a novice in designing databases and I have the following tables:
PRODUCT -> Record all products, whether or not available in stock.
- product_id (PK)
PURCHASE -> Record all purchase of new products to sell.
SALE -> Record each sale operation
- sale_id (PK)
SALE_ITEM -> Records which product was sold in a sell operation.
The SALE_ITEM table is related to the PRODUCT table through an attribute product_id.
So, should I relate the sale of each product with the identifier of the table PURCHASE instead of the identifier in PRODUCT table?
Or, should I force the client to sell the old lot before selling the new ones?
How you would handle this situation?
Thanks, and sorry for my english, I am not a native.