08-01-2011, 11: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.
08-02-2011, 12:42 AM
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:
recipeid INT PRIMARY KEY
ingid INT PRIMARY KEY
ingName VARCHAR( )
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.