I am still hacking away at my little recipe program and am trying to find the best way to design the database that is going to hold all of my data (and not be dog slow when there are tons of rows). As part of this program, I would like to be able to calculate the nutritional information of a recipe "on the fly" by adding up the recipe's individual ingredients' nutrition info (which would be stored in an "ingredients" table).

I am still new to database design and have hit a wall with how to represent this data. If I were representing this in Java I would have a "Recipe" object that holds an array of "Ingredient" objects (along with other variables such as the recipe name, directions, etc). I am not sure what the best practices are for a.) representing an array (of unknown size) of values and b.) representing an abstract data type such as an "Ingredient."

I also want to be able to find recipes that contain a given ingredient(s), in case you want to use up food that is going bad, which is why I don't want to use a simple text variable for the ingredients.

Hopefully that is enough relevant information to get pointed on the right track

In your case, I'd make a many-to-many table. One recipe can have many ingredients, and one ingredient can appear in many recipies.

If we have a recipie table:


And a reference table of ingredients:



We might have this RecepieIngredients table. (Or, we might not; I don't cook, so what the heck do I know?)



In RECEPIE, the RecepieID is the primary key. Similarly, in the INGREDIENT table, the IngredientID is the PK.

In RECEPIEINGREDIENTS, the primary key has two columns: (RecepieID, IngredientID). The RecepieID there is a foreign key back to the RECEPIE table, and the IngredientID is a foreign key back to the INGREDIENT table.

I hope that helps, and that you can understand the examples--representing database tables in a forum is pretty cumbersome.

just so you know mike, you spelt recipe wrong ( I may have too)

Gosh, I did. Curiously, I can't edit the post.