I am attempting to design a "recipe box" database and I am getting trouble setting it up right. I've no clue if I am on course or otherwise, but this is what I've.

quality recipes(recipeID, etc.)
component(ingredientID, etc.)
recipeIngredient(recipeID, ingredientID, amount)
category(categoryID, title)
recipeCategory(recipeID, categoryID, title, etc.)

And So I have a few pre-determined questions.

  • How shall we be held doing to date? Is design okay from that which you are all aware?
  • Wouldso would I implement the preparation steps? Must I create one more many-to-many implementation (something similar to preparation(prepID, etc.) and recipePrep(recipeID, prepID)) or simply add the directions within the quality recipes table? I'd like this to become an purchased list within the UI (web page).

Appreciate your help.

Perhaps you have checked out the existing schemas available, for example this one at DatabaseAnswers?

Appears like an excellent start. A couple of ideas:

  1. Aren't seeing an excuse for a recpieCategory table. One-to-many between recipe and category must do fine.
  2. A PreparationSteps table should contain 1-n steps for every recipe. I would not attempt to reuse steps between quality recipes.

some ideas:

You might like to make use of the same table for Recipe and Component, having a type indicator column. This is because Quality recipes can contain sub-quality recipes. Let us call the combined table "Item". Your RecipeIngredient table would seem like

 RecipeIngredient (RecipeId, ItemId, Amount).  

I'd expect the table would in addition have a sequencing column.

If you wish to inflict information with one of these quality recipes (e.g., scaling, dietary analysis, production planning) your amounts will have to specify one of measure. It can be done clearly (by getting another column for uofm) or use a text area for quantity and expect the consumer to go in values like "1 cup", or "2 the best spinner's". For that approach, you will need to make certain that the things they enter is identifiable, and parse it each time you should utilize it. This could become surprising complex, especially if you wish to represent recipe yields inside a official manner.

Presuming you would like 1:M from recipe to category, I am still unsure why your RecipeCategory table might have a Title column. I'd believe that the title originates from the course definition.

To be sure with Dork it's unlikely that you would reuse preparation steps from recipe to recipe, and thus a RecipePreparationSteps table (or something like that enjoy it) could be appropriate.

However, quality recipes are frequently given elements and instructions intermixed. eg.

Intro text some elements. prep instructions more elements baking instructions.

To accomodate that, you have to cleverly set sequencing values within the RecipeIngredient and RecipePreparation step tables to ensure that you are able to mix data from in the correct order for presentation. Another approach could be, rather than both of these tables, make use of a "RecipeLine" table so that each row can represent either an instruction Or perhaps an component. I believe that might be that which you were recommending. Purists would do not allow this type of table over-loading, but I am not really a purist.

This can be a subject I occur to know a great deal about, so request anything.