2 Replies Latest reply on Oct 30, 2013 2:20 PM by RichNasser

    Recipe in a Recipe



      Recipe in a Recipe


      I am working on a database for food costs.  I have 3 tables:

      Menu Item

      All of the inventory items are the ingredients.  Ingredients come together to make a recipe.  However a recipe could be used in another recipe.

      Hamburger recipe: 4oz Hamburger
                               1   Bun
                               1   Condiment Pk


      Cheeseburger recipe:  1 Hamburger Recipe
                                   1 Slice cheese

      This is a very simplistic but realistic example.  On the TAB to build the recipe I have a portal to add ingredients and a portal to recipes.  The portal to ingredients works perfect.  But I cannot not get the portal to work back to itself on recipes.  I have TO of RECIPE and RECIPE2 related by the auto-assigned sequence number each recipe gets.  What am I missing for this?  I am sure something simple.

        • 1. Re: Recipe in a Recipe

          I'd put ingredients and foods produced from a recipe in the same table and use a many to many self join to link items to the food that they are used to produce. This is exactly like having an inventory that lists raw materials and then the various goods manufactured from those raw materials with a BOM (Bill of Materials) that lists the inventory items and quantiites needed to produce a given inventory item. Purchased items just don't have related recods in the BOM table. The relationships can link up records recursively for as many iterations as needed.


          Where Recipe and Ingredients are two occurrences of the same table. Recipe_Ingredient identifies one ingredient in each record with the quantity info needed to make one such item from the given recipe.

          Recipe::__pkItemID = Recipe_Ingredient::_fkRecipeItemID
          Ingredients::__pkItemID = Recipe_Ingredient::_fkIngredientItemID

          • 2. Re: Recipe in a Recipe

                 Trying to replicate what you're doing here... any chance you can still access this post?