Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Help with recipe database design

    Hello Everyone !

    I am trying to figure out how to design my recipe database.

    My Questions:

    1 - User specifies any serving portion and I will have to calculate the ingredients amout accordingly. Is the current setup okay to do this ?

    2 - User can choose imperial or metrics for the units. How is the best way to convert the ingredients amout accordingly? What table would I need for this ?

    3 - I want to let the user search recipes by ingredients they choose in the search area. Is current setup enough for this ?

    4 - User should be able to rate recipes and leave comments. IS the setup good enough?

    So far I have come up with the DB design in the attachment.
    database.bmp

    Hope that someone could point me to the right direction.

  2. #2
    Join Date
    Jun 2010
    Location
    Fort Lauderdale, FL
    Posts
    5
    1 - User specifies any serving portion and I will have to calculate the ingredients amout accordingly. Is the current setup okay to do this ?

    Yes.


    2 - User can choose imperial or metrics for the units. How is the best way to convert the ingredients amout accordingly? What table would I need for this ?

    I would specify for each ingredient which is the "natural" unit then have a conversion table letting me know how many "kilos" are in a "stone" :-) yes, I'm planning to catter for a large crowd ;-)


    3 - I want to let the user search recipes by ingredients they choose in the search area. Is current setup enough for this ?

    Yes.


    4 - User should be able to rate recipes and leave comments. IS the setup good enough?

    Yes.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Moved to Database Concepts & Design
    George
    Home | Blog

  4. #4
    Join Date
    Mar 2012
    Posts
    4
    Something like this:
    database.bmp

    1. I have added a unit_type to units table (determines if metric or imperial)

    2. In the conversion table I would use something like
    1 kg = 0.157473044 Stone

    Is that correct then ?

  5. #5
    Join Date
    Jun 2010
    Location
    Fort Lauderdale, FL
    Posts
    5
    Quote Originally Posted by ladooboy View Post
    Something like this:
    1. I have added a unit_type to units table (determines if metric or imperial)

    2. In the conversion table I would use something like
    1 kg = 0.157473044 Stone

    Is that correct then ?
    Yes but, I do not see a column on "Conversion" table to hold the 0.157473044 value. I assume the value for the first unit will be "1" by default then can be ommited but the conversion value has to be stored there.

  6. #6
    Join Date
    Mar 2012
    Posts
    4
    Hi PaulB_ !

    Yes, sorry. Of course there will be at least two more fields called from_unit_quantity and to_unit_quantity, which will contain the conversions.

    That's great. Thank you very much for your help .

  7. #7
    Join Date
    Mar 2012
    Posts
    4
    * Getting recipe steps *

    Code:
    SELECT * 
    FROM recipes
    LEFT JOIN recipe_steps ON recipe_steps.recipe_key = recipes.recipe_key;

    * Getting Recipe Ratings *

    Code:
    SELECT COUNT(*) AS rating_amount, SUM(rating_score) 
    FROM recipes
    LEFT JOIN ratings ON ratings.recipe_key = recipes.recipe_key;

    * Getting Recipes, Ingredients, Units and data for conversions

    Code:
    SELECT recipes.recipe_name,ingredients.ingred_name, recipe_ingredients.unit_quantity, units.unit_name,conversions.*,unti2.unit_name AS conv_unit_name
    FROM recipes
    LEFT JOIN recipe_ingredients ON recipe_ingredients.recipe_key = recipes.recipe_key
    LEFT JOIN ingredients ON ingredients.ingred_key = recipe_ingredients.ingred_key
    LEFT JOIN units ON units.unit_key = recipe_ingredients.unit_key
    LEFT JOIN conversions ON conversions.from_unit_key = units.unit_key
    LEFT JOIN units unti2 ON  unti2.unit_key = conversions.to_unit_key
    I would have to loop through the result and calculate: $converted_unit = (unit_quantity / from_unit_quantity) * to_unit_quantity
    $converted_unit would have the converted value.


    Is there a way to reduce the amount of these 3 queries into 1 or 2 ?
    I've tried different solutions, but I would always get duplicates because if the recipe has 2 images and 2 recipe steps it will return 4 lines with two duplicates.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    entity conversion could be refined or possibly dropped
    you don't need a conversion id
    but you do need a value for the conversion

    what I've done in the past is define a type unit of measurement
    include that in th eunit of measurement table
    and define a conversion factor in there
    implement by software that there must always be a base unit whose conversions factor is 1 , and that there must be one base unit for each of your measurement types

    bear in mind an ingredient may come in specific quantities or sizes

    the last recipie design I was involved with required around 15 tables
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •