Hi,
I would appreciate any help in regards to what I need to do.
I'm working on a database for my friend cafe, it has to keep a record of the following:
- Orders
- Purchase Orders
- Inventory
- Recipes Control (Add New Product/Drink means add new recipe for it)
- The cost price for each recipe determined by the quantity of materials used in each drink based on the recipe
- Cash register (Spent/Earned)
My problem is that I do not know how to track the inventory of purchased materials like (Milk, chocolate,..) and deduct from that the consumed materials based on the products/drinks sold in orders
I Figured that I need a separate tables for ( Orders/Purchase Orders/Materials Registry to hold all the info about the materials used in the recipes/Recipes tbl so I can add a new recipe or change it and create or add new drink/product in the menu/Invetory tbl to hold the quantity add to it materials purchased and deducting form it materials used based on the drinks sold)
I hope u got the basic idea please help me with how to make the inventory table hold all the quantities of the materials purchased (-) all the quantities consumed based on the recipe
Example of recipe: Milkshake: 180ml Milk + 200gm Ice Cream + 60ml chocolate syrup each component of this recipe has its own cost price I need to make it calculate the cost based on the prices of the materials and volumes purchased and the recipe itself.....please help me.