Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: Table Structuring question
08-01-2011, 10:01 AM #1
- Join Date
- Mar 2010
- Orlando, FL
- Thanked 8 Times in 8 Posts
Table Structuring question
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-01-2011, 11:42 PM #2
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:
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.Code:Table: Recipes recipeid INT PRIMARY KEY title VARCHAR(...) directions TEXT Table: Ingredients ingid INT PRIMARY KEY ingName VARCHAR( ) Table: RecipeIngredients recipeid INT REFERENCES Recipes(recipeid) ingid INT REFERENCES Ingredients(ingid) qty ??? VARCHAR( ) ??? otherIngredient VARCHAR( )
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.