php auto archive?

Posted on 2011-03-07
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 250 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 250 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:

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Backup Strategy 15 44
Help With Simple Database Design 7 50
Restricting the scope of variables to private in OOP 4 22
IP / 61 56
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

860 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