Hi all. Currently I have an ingredients table and a recipe table. First you would start by adding your ingredients ie: add ingredient name, price and type ie: ml, grams, etc.
Then after adding ingredients I will add a recipe which enters into the recipe table. I have a search function which allows you to search for ingredients for the recipe you want to add. So, at the moment if I search for mushrooms and I then click on mushrooms, there is a field which allows me to enter how many I need. I type in 4 and it calculates the price of 1 mushroom x the entered amount eg:4. Then I click on add ingredient and it adds that into another table.
The three tables I have are INGREDIENTS, RECIPES, RECIPEINGREDIENTS
When adding a recipe, just the name of the recipe, year of the recipe and number of portions it makes are entered into the RECIPES table. The individual ingredients, quantities and price of the ingredients are added into the RECIPEINGREDIENTS table. This all works great, the calculations are correct etc. and I am very happy with it. However, I need some suggestions. When this year is over, I need to put in new ingredient prices for 2012 which I want to automatically update the recipe prices so I don’t have to manually go and change all the recipe prices. But, this would then overwrite the current recipe prices for 2011. So, somehow I need to give the user the ability to click an “archive” button or something that will move the 2011 recipes to another table and then when they edit the ingredient prices for 2012 it will automatically adjust the recipe prices but they must still be able see how much it cost them to make the dish in 2011. Any suggestions? Maybe there is an easier way?