...

View Full Version : Table Structuring question



wojo1086
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
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( )

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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum