php auto archive?

Posted on 2011-03-07
Medium Priority
Last Modified: 2012-05-11
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?
Question by:jonofat
  • 2
LVL 17

Accepted Solution

Shinesh Premrajan earned 750 total points
ID: 35066945
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


Author Comment

ID: 35068136
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.


Assisted Solution

gizmola earned 750 total points
ID: 35074051
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:  http://www.gizmola.com/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html

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.

Author Closing Comment

ID: 35221706
Helpful but not enough detail perhaps.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month16 days, 20 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question