Help with recipe database design

ladooboy · #1 (**permalink**) 03-18-12, 14:55

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.

PaulB_ · #2 (**permalink**) 03-18-12, 19:49

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.

gvee · #3 (**permalink**) 03-18-12, 20:31

Moved to Database Concepts & Design

ladooboy · #4 (**permalink**) 03-19-12, 18:12

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 ?

PaulB_ · #5 (**permalink**) 03-19-12, 18:17

Quote:

Originally Posted by ladooboy

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.

ladooboy · #6 (**permalink**) 03-20-12, 05:48

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

.

ladooboy · #7 (**permalink**) 03-21-12, 11:39

* 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.

healdem · #8 (**permalink**) 03-21-12, 13:36

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

Internet Services

Web Development

Digital Marketing

Consumer Tech