Hello

Here again I ask for your help to build this database. I've traveled the forum but I did not find my happiness .... : whistle:

Here I want to create a database to manage recipes and be able to calculate my production costs.

I tried several "formulas" but my big problem is the sub-recipes.

In fact, a recipe is a set of ingredients and the same recipe can also become an ingredient.

I tried to copy the recipe with all necessary information (name, price per kilo, ...) in my ingredient table, what works. But in case of modification (the recipe itself or tariffs ingredients) of this recipe, the changes are not taken into account which is very annoying and if you have changed everything by hand ... : drop:

So it is obvious that my structure is not at all suitable or I've missed something ...

Thank you for all the help you could give me ....
excuse me for my english, I'm french ...

You might want to look into thread: http://fmforums.com/forum/topic/89496-my-recipe-database-needs-help/ Towards the end you'll find an example database that should get you started.

 

In fact, a recipe is a set of ingredients and the same recipe can also become an ingredient.

 

Are you sure about this? I've never read a recipe where it says "put 200 grams of recipe A into the mixer" … Can you give a real-life example?

To keep the database simple you can add a field to the ingredient table that references a recipe, and auto enter other fields into the ingredient from the referenced recipe, such as the total weight, cost, etc from a relationship IngredRecipe > Recipe.  This would give you one level of nesting within your recipe.  Such as a Béchamel sauce recipe referenced within a pasta with sauce recipe. 

 

To list all ingredients from sub recipes in a top level recipe would be a lot of work and take you to another level of database design, that being an exploded bill of materials.  

 

This is where you would have multiple levels of recipes within a top level recipe, such as Pizza containing pizza sauce, within that a recipe for tomato sauce, then sausage, within the sausage could be a recipe for spice blend, pizza skin, within that a recipe for dough, and within that a recipe for a starter, etc.