Need help with recursive design

[frankos72]

I need help with the following recursive design.

The purpose of the database is to calculate food cost and nutritional information based on my ingredients and their costs and nutritional values.



The Problem, recipes(sub recipes) that are used in other recipes which is throwing me into a recursive situation I cannot figure out. For example, Beef broth is a receipe that is then used to make brown gravy. Then the brown gravy can be used on several different dinners. A recipe is not always a recipe in the way that you might think of in a kitchen. It can be in the instance of so brown gravy. But in the case of say, beef tips and potatoes dinner, it's actually a combination of sub recipes put together on a plate to get the total cost of the food on the plate.

Here is a diagram with 4 tables I have drawn up. In this attempt, the ingredients can come from either a recipe OR a grocery, but not both.




Perhaps there is a way to do this exact thing in Access and I just don't see it, or perhaps my database design is flawed and there is a better way. Either way I need help.

Thanks in advance,

Jev

[June7]

Check this http://office.microsoft.com/en-us/te...001018635.aspx

There is also a Nutrition template database. On the New database setup of Access, type recipe in the Search Office.com box.

I think you might just have a comment in recipe: 'see recipe for brown gravy'

[frankos72]

Thank you, that template is to basic. I've searched for templates and not found any that do what I'm trying to do.

I'd like to do it myself, but I just cannot get past this hurdle. If my needs were more flat, meaning I didn't have any sub recipes, I'd already be done with planning and into building the db.

[June7]

This is like manufacturing db, parts assemble into composites that become parts for an even bigger composition. Very complicated. Manufacturing is fairly common topic in forum. Might search for it.

Otherwise, the comment idea is the only thing I can offer.

[frankos72]

I think you might just have a comment in recipe: 'see recipe for brown gravy'

I'm not sure where you are talking about. Please elaborate.

I'll search for manufacturing.

[RuralGuy]

It is called "Bill of Materials" or BOM. As June7 said, this is not an easy situation to solve.

[June7]

Consider;

tblGroceries
GrocID

tblRecipes
RecipeID
Desc
CookingInstructions
Comment (example: Use recipe for brown gravy)

tblRecipeIng
RecipeID
GrocID

[frankos72]

Consider;

tblGroceries
GrocID

tblRecipes
RecipeID
Desc
CookingInstructions
Comment (example: Use recipe for brown gravy)

tblRecipeIng
RecipeID
GrocID

Yeah, okay, I get what you mean now by comment. This doesn't solve the problem. In my first example, I need to be able to import my current costs for groceries(IE Beef Bones, Celery, Salt, Pepper, etc) and then run a report that tells me my current costs for all my meals on my menus such as beef tips with brown gravy. I can do it in excel and I know it can be done in access, I'm just not sure of how to setup the tables and relationships.

[Dal Jeanis]

The good thing is, that you are NOT a manufacturing institution, and therefore your estimates of price and nutrition don't have any effect on the product itself.
So, the problem with recursion can be handled by stepping back a bit from the problem itself.

Suppose that

(1) A grocery is represented by a special case of a (sub)recipe that has only one input - the grocery.

(2) A subrecipe can take as inputs any number of other subrecipes.

(3) A subrecipe ***stores*** its current and immediate prior calculations of cost and nutrition.

(4) When recalculation occurs, each subrecipe uses the immediate prior stored values of its component
subrecipes to calculate its own new values.

(5) When recalculation occurs, it proceeds iteratively until all current and immediate prior calculations of cost and nutrition are the same for all subrecipes (within some variance parameters).

This way, you don't have recursion in the code, just in the process that calculates your values. Since the costs and nutritional values aren't particularly volatile, the number of iterations for the calcs to "settle" is likely to be roughly equal to the number of levels deep your recursion would have had to be. As a gross estimate, you could probably just run the recalc 5 times sequentially and not even check how much the values were changing.

You would do the same process in reverse to get the groceries required for any given set of menus.

(1) Set up a set of menus (recipes) in a temp table

(2) move all recipes that are groceries to a second temp table, then

(3) break down the remaining recipes in the first temp table into their components.

(4) repeat steps 2 and 3 until no recipes remain.

[frankos72]

The good thing is, that you are NOT a manufacturing institution, and therefore your estimates of price and nutrition don't have any effect on the product itself.
So, the problem with recursion can be handled by stepping back a bit from the problem itself.

Suppose that

(1) A grocery is represented by a special case of a (sub)recipe that has only one input - the grocery.

(2) A subrecipe can take as inputs any number of other subrecipes.

(3) A subrecipe ***stores*** its current and immediate prior calculations of cost and nutrition.

(4) When recalculation occurs, each subrecipe uses the immediate prior stored values of its component
subrecipes to calculate its own new values.

(5) When recalculation occurs, it proceeds iteratively until all current and immediate prior calculations of cost and nutrition are the same for all subrecipes (within some variance parameters).

This way, you don't have recursion in the code, just in the process that calculates your values. Since the costs and nutritional values aren't particularly volatile, the number of iterations for the calcs to "settle" is likely to be roughly equal to the number of levels deep your recursion would have had to be. As a gross estimate, you could probably just run the recalc 5 times sequentially and not even check how much the values were changing.

This is good. I was thinking the exact thing night before last. I actually I thought I could even count the number of levels in the db as I add items to the db so I would know how many times to recalculate. Now I just need to shore up the design. I think I have made a big step with a simple change to my diagram. I added another field to the Ingredients Table(Now called MasterIngredients) so that each entry can have a fk from groceries OR Recipes. There is the possibility that an Ingredient could end up with both, but since I'm the only one using the DB, I will make sure that doesn't happen.


I hope I'm on the right track.

[Dal Jeanis]

You're better off enforcing your rules by design. Try something like this -

tblRecipes
  Recipe_ID       PK
  Description     Text/Memo

tblIngrGroceries
  IngrGroc_ID     PK   
  Recipe_ID       FK to Recipes
  Grocery_ID      FK to Groceries
  AmountRequired  Number

tblIngrSubrecipes
  IngrSubRec_ID   PK   
  Recipe_ID       FK to Recipes
  SubRecipe_ID    FK to Recipes
  AmountRequired  Number

tblGroceries
  Grocery_ID      PK
  Description     Test/Memo

There is a loop from tblRecipes to itself, but that loop occurs in only one place, and is transparent to the database. You can't accidentally get two different answers by joining the same query in a different order, which is a major problem with your other designs.

For calculating the cost of all recipes, you first
(A) create tables to hold the grocery ingredient cost and subrecipe ingredient cost

 
tblIngrGrocCost
  PK              PK
  IngrGroc_ID     FK  
  IngrGrocCost    Currency (The cost for the amount of this grocery ingredient in this recipe)

tblIngrSubCost
  PK              PK   
  IngrSubRec_ID   FK   
  IngrSubCost     Currency (The cost for the amount of this subrecipe ingredient in this recipe)

tblRecipeCost
  Recipe_ID       FK   
  RecipeCost      Currency

(B) populate the grocery ingredient cost, and
(C) zero the subrecipe ingredient cost.

Then, you iteratively
(D) calculate recipe cost from ingredients, both groceries and subrecipes, and then
(E) calculate subrecipe cost from recipe cost.

[frankos72]

You're better off enforcing your rules by design. Try something like this -

tblRecipes
  Recipe_ID       PK
  Description     Text/Memo

tblIngrGroceries
  IngrGroc_ID     PK   
  Recipe_ID       FK to Recipes
  Grocery_ID      FK to Groceries
  AmountRequired  Number

tblIngrSubrecipes
  IngrSubRec_ID   PK   
  Recipe_ID       FK to Recipes
  SubRecipe_ID    FK to Recipes
  AmountRequired  Number

tblGroceries
  Grocery_ID      PK
  Description     Test/Memo

There is a loop from tblRecipes to itself, but that loop occurs in only one place, and is transparent to the database. You can't accidentally get two different answers by joining the same query in a different order, which is a major problem with your other designs.

For calculating the cost of all recipes, you first
(A) create tables to hold the grocery ingredient cost and subrecipe ingredient cost

 
tblIngrGrocCost
  PK              PK
  IngrGroc_ID     FK  
  IngrGrocCost    Currency (The cost for the amount of this grocery ingredient in this recipe)

tblIngrSubCost
  PK              PK   
  IngrSubRec_ID   FK   
  IngrSubCost     Currency (The cost for the amount of this subrecipe ingredient in this recipe)

tblRecipeCost
  Recipe_ID       FK   
  RecipeCost      Currency

(B) populate the grocery ingredient cost, and
(C) zero the subrecipe ingredient cost.

Then, you iteratively
(D) calculate recipe cost from ingredients, both groceries and subrecipes, and then
(E) calculate subrecipe cost from recipe cost.

I've drawn this on paper and I'm still having a hard time grasping it. I'll keep studying it and get back once my brain catches up! Thanks.

[Dal Jeanis]

It looks like this

                 +---------+       +---------+    
              ->>|IngGroc  |<<---->| groc    |     
+--------+   /   |         |       |         | 
|rec     |<--    +---------+       +---------+    
|        |<--    +---------+       +---------+
+--------+   \   |IngSub   |<<---->| rec(2)  |
              ->>|         |       |         | 
                 +---------+       +---------+

then there's a 1-to-1 relationship from rec to recCost, ingGroc to ingGrocCost, and ingSub to IngSubCost.