php auto archive?

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.


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?
Who is Participating?
Shinesh PremrajanConnect With a Mentor Engineering ManagerCommented:
Add a new field in the INGREDIENT Table as yrid


Hence for every ingredient there will be yrid, for ex: mushrooms with the ingridientid as 9
mushroom will have yr id 12, in the table INGREDIENTYEAR, the row 12 will contain the data as

12, 2010 ,9, 20.00
18, 2011 ,9, 25.00

so you can add more years in the INGREDIENTYEAR table. only thing is there must be a unique key for
based on fields year and  ingridientid.

Hope this helps

jonofatAuthor Commented:
This is a good idea, but I don't know if it will solve my entire problem? Would the best thing then be to duplicate all the entries in the ingredients table and only the year changes? Then do a multiple update on the price to whatever you want it to be? Is that possible? For example, my ingredients table looks like:

IngredientID |  IngredientName  |  IngredientPrice  |  IngredientType  | IngredientYear
-------------- |  ------------------- |  ------------------  |  ------------------  | -----------------
      1                     Olive Oil               0.04726                   ml                      2010
      2                     Feta                     0.05271                   g                       2010

Is it possible to have a button and when I click on it, it would run a query that will add these items again but just change the year depending on the user input? So, if they user typed in 2011 and clicked submit, it would change the table to:

IngredientID |  IngredientName  |  IngredientPrice  |  IngredientType  | IngredientYear
-------------- |  ------------------- |  ------------------  |  ------------------  | -----------------
      1                     Olive Oil               0.04726                   ml                      2010
      2                     Feta                     0.05271                   g                       2010
      1                     Olive Oil               0.04726                   ml                      2011
      2                     Feta                     0.05271                   g                       2011

Although, it probably won't work because IngredientID is a primary key set to auto increment. Damn.

gizmolaConnect With a Mentor Commented:
Basically what you have is a normalized relational design, and what you've realized is that Ingredient price is not a core attribute of an ingredient.  It really should be normalized out, and further more, what I would suggest is that the price table contain a From/To Date range indicating how long the price is valid for.  It just begs the question of... do ingredients really have a set price for an entire year that only changes when there's a new year?  I'm dubious.

This gives you a way of updating pricing information whenever you want to, as well as going back in time and looking at price changes, if that's something of interest to you.  I'd suggest:

IngredientPriceID  - Int PK
IngredientID -FK to ingredient table.
FromDate - Date
ToDate - Date

Add a unique index on IngredientID, FromDate,ToDate

One application approach trick is to set your current active ToDate to always be a date far in the future.  I call this using a "perpetuity" date.  For a mysql Date column the largest allowable date is '9999-12-31', which makes a great perpetuity date.  When you need to enter a new ingredient price, you basically close out that date, by updateing the ToDate to be the day prior to your new date, and then add the new row.  

When you query this you will typically be doing a query related to CURDATE().  This article talks a bit about that:

When you join in your query from ingredient to ingredientPrice, you'll just include a WHERE CURDATE() BETWEEN FromDate AND ToDate and you'll always get the current price for that ingredient for the day, but you'll also be able to craft different queries that get you pricing for prior periods.

For even more flexibility, you might want to have a currency type to indicate the currency of your related Ingredient price as well.
jonofatAuthor Commented:
Helpful but not enough detail perhaps.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.