sql server 2008 - Modeling A Food Recipes Database -

i'm trying design "recipe box" database , i'm having trouble getting right. have no idea if i'm on right track or not, here's have.

recipes(recipeid, etc.)
ingredient(ingredientid, etc.)
recipeingredient(recipeid, ingredientid, amount)
category(categoryid, name)
recipecategory(recipeid, categoryid, name, etc.)

so have couple of questions.

• how doing far? design okay know?
• how implement preparation steps? should create additional many-to-many implementation (something preparation(prepid, etc.) , recipeprep(recipeid, prepid)) or add directions in recipes table? ordered list in ui (webpage).

thank help.

some thoughts:

you might want use same table recipe , ingredient, type indicator column. reason recipes can contain sub-recipes. let's call combined table "item". recipeingredient table like

 recipeingredient (recipeid, itemid, amount).   

i'd expect table have sequencing column.

if want calculations these recipes (e.g., scaling, nutritional analysis, production planning) quantities need specify unit of measure. can explicitly (by having separate column uofm) or can use text field quantity , expect user enter values "1 cup", or "2 tbs". if take approach, you'll need make sure enter recognizable, , parse every time need use it. can become surprising complex, if want represent recipe yields in formalized manner.

assuming want 1:m recipe category, i'm still not sure why recipecategory table have name column. i'd think name comes category definition.

i agree dave it's unlikely you'd reuse preparation steps recipe recipe, , recipepreparationsteps table (or it) more appropriate.

however, recipes presented ingredients , instructions intermixed. eg.

intro text ingredients. prep instructions more ingredients baking instructions.

to accomodate that, need cleverly set sequencing values in recipeingredient , recipepreparation step tables can combine data both in proper order presentation. approach be, instead of these 2 tables, use "recipeline" table such each row can represent either instruction or ingredient. think may suggesting. purists frown on kind of table overloading, i'm not purist.

this topic happen know lot about, ask anything.