View Full Version : Table Structuring question

08-01-2011, 10:01 AM
Let's say I want to store recipes in my database and I also want to store the ingredients for each recipe as well. What's the best way to structure my tables? Should I have one table just for recipes and another table just for ingredients and just use a foreign key between the two? Or, should each new recipe get its own table for ingredients, that way there isn't one table with a list of thousands of ingredients.

Old Pedant
08-01-2011, 11:42 PM
Tough call.

Suppose you have 10,000 recipes. And ONE out of those 10,000 uses a particular ingredient.
Seems like a pain to have to put that ingredient into the Ingredients table, doesn't it?

On the other hand, maybe 25% of all recipes will have Flour as an ingredient. So clearly you would want to normalize for that ingredient.

Purists would say *always* normalize. I might argue that one entry in Ingredients table should be "OTHER" and then you have the actual ingredient in the RecipeIngredients table.

In other words:

Table: Recipes
title VARCHAR(...)
directions TEXT

Table: Ingredients
ingName VARCHAR( )

Table: RecipeIngredients
recipeid INT REFERENCES Recipes(recipeid)
ingid INT REFERENCES Ingredients(ingid)
qty ??? VARCHAR( ) ???
otherIngredient VARCHAR( )

So if an ingredient isn't in the ingredients table, maybe you put NULL inti ingid in RecipeIngredients table and then put the actual ingredient into otherIngredient.

qty is interesting: Is it a number? Along with a units-of-measurement? Or do you just put text (e.g., "1/2 tsp") in there?

I assume that your real DB is not for something as simple as recipes, so have fun interpreting the above in terms of your real problem.