I designed a database which initially has tables of TblProduct & TblSales. It works with user to input details of products( such as product name, cost and price) so it can be used in daily sales recorded in TblSales.
User is now requesting to have information of quantity of each product so it can be replenished before out of stocks and cost may be different from time to time depends on order user made to their suppliers.
for user to control the quantity I plan to design with real time quantity = initial quantity(to be input by user into TblProducts) + order quantity (from TblOrder) - sales quantity(From tblSales)
User is using first in first our method in selling the product so is there anyway to design to have the database recognizes and calculates same product with different unit cost like situation as below:
|Order Date||Product name||Cost||Order quantity|
|Sales No.||Sales date||Product name||Unit Price||Sales quantity||Unit Cost||Unit Profit|
How to design the database with situation above?
Thanks in advance.