Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Nov 2005
    Posts
    5

    Tougher Twist To The Recipe Database

    Hi all,

    Background:
    I work for a company that preweighs ingredients for a mixing facility. The basic setup is relatively the same as the Recipe-RecipeIngredient-Ingredient database examples that I have seen floating around on this forum. However, in my situation it is sometimes necessary to combine or package certain ingredients together and isolate them from other ingredients in the recipe.

    For example, let's make a Chocolate and Peanut Butter chip oatmeal cookie.
    My db layout is the attachment titled db1.jpg

    RECIPE TABLE
    RecipeID = 1 RecipeName = ChocolatePBChipOatMlCookie

    INGREDIENT
    IngredientID = 1 IngredientName = ChocolateChips
    IngredientID = 2 IngredientName = PeanutButterChips
    IngredientID = 3 IngredientName = Oats
    IngredientID = 4 IngredientName = Sugar
    IngredientID = 5 IngredientName = Salt
    IngredientID = 6 IngredientName = Flour
    IngredientID = 7 IngredientName = Baking Soda

    Now the RecipeIngredient Table is nothing more than the ID's linking appropriately.

    Here is my problem...
    The ChocolateChips will need to be packaged in its own bag
    The PeanutButterChips will need to be packaged in its own bag
    The 2 individual bags of chips will be packaged inside a 3rd bag which contains the rest of the ingredients.

    Breakdown:

    Usually the recipe is assigned a certain bag and the preweigh can simply add the individual RecipeIngredient spec weights and then add the bag weight to determine an overall gross weight. This is easily done by just assigning a BagID from a BAG table to the Recipe table. However, No I need to figure out how to include 2 more bags for the isolated Chocolate chips and Peanut Butter chips.

    In this case, the preweigh has to accomodate for x amount of weight of chocolate chips and peanut butter chips + the weight of each bag. In addition, the main mix bag weight will have to considered.

    Main Cookie Mix Bag
    Oats, Sugar, Salt, Flour, Baking Soda, Chocolate Chips bag, Peanut Butter Chips bag
    Attached Thumbnails Attached Thumbnails db1.jpg  
    Last edited by DelbertZZZ; 11-09-05 at 14:31. Reason: addition for jpg

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    Consider using a recrusive relationship on BAG. (A bag can contain 0 to many bags) Shouldn't there be some relationship between bag and ingredient?
    Last edited by urquel; 11-09-05 at 14:42.

  3. #3
    Join Date
    Nov 2005
    Posts
    5
    Quote Originally Posted by urquel
    Consider using a recrusive relationship on BAG. (A bag can contain 0 to many bags) Shouldn't there be some relationship between bag and ingredient?
    Yes, I suppose there should be...However, until this isolation thing came up and all ingredients were placed into one bag, this relationship worked.

    Could you elaborate a little more on the recursive relationship idea you proposed?

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    Quote Originally Posted by DelbertZZZ
    Yes, I suppose there should be...However, until this isolation thing came up and all ingredients were placed into one bag, this relationship worked.

    Could you elaborate a little more on the recursive relationship idea you proposed?
    A recipe can have many ingredients. An ingredient can be in many recipes.
    Recipe>-<Ingredient (>-< is a many to many relationship, which you have resolved in your design with the Recipe Ingredient table)

    An ingredient goes in a bag. A bag of ingredients goes in a bag.
    A bag contains ingredients. A bag contains bags of ingredients.
    Bag-<Ingredients (-< is one to many relationship)
    Bag-<Bags of ingredients

    BTW: Where do you store the measure of ingredient and units of measure?
    Last edited by urquel; 11-09-05 at 15:11.

  5. #5
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    May I suggest:
    Attached Thumbnails Attached Thumbnails untitled.JPG  

  6. #6
    Join Date
    Aug 2004
    Posts
    330
    Certus. Your suggested design would allow you to put a recipe ingredient into multiple bags instead of putting multiple ingredients into a single bag. Perhaps you should consider making bag the parent of recipe ingredient or a many to many between recipe ingredient and bag.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    how about going recipe -> RecipeBags -> RecipeBagIngrediants with RecipeBagIngrediants referencing ingrediants? Unless you want to go nuts and be able to assign the same bag to multiple recipes. In that case, create a table just for Bags with a child table of BagIngrediates. Then use RecipeBags to hook up a completed bag to a recipe.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Nov 2005
    Posts
    5
    Thank you everyone for your help...

    I believe I should clarify some things so not to get off track.

    Although the example I depicted isolated Chocolate Chips from Peanut Butter Chips and put each in a separate bag...sometimes this may happen.

    If they make a chocolate chip cookie then they would use only one bag which would include the flour, sugar, salt, and chocolate chips.

    So all my ingredients need to be independent. Because chocolate chips may get put in its own bag and then put into another bag that has flour and sugar. But for another customer, chocolate chips may get put into the same bag as the sugar and flour.

  9. #9
    Join Date
    Aug 2004
    Posts
    330
    You may want to re-do your ERD and add Customer to the mix.

  10. #10
    Join Date
    Nov 2005
    Posts
    5
    Yeah,

    I have several more tables that are in this schema, but for simplicity I only made a diagram of the ones that were giving me some issues.

    Thanks to all of you I was able to put something together that appears to work...we'll see.

  11. #11
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    You're right urquel, I'm a bit rusty
    Attached Thumbnails Attached Thumbnails untitled.JPG  

  12. #12
    Join Date
    Nov 2005
    Posts
    5
    Excuse my ignorance, but what is PK-FK relationship between these 2 tables in your diagram?
    Attached Thumbnails Attached Thumbnails db2.jpg  

  13. #13
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Here let's try for the third time...
    Attached Thumbnails Attached Thumbnails untitled.JPG  

  14. #14
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Composite keys could be used, but I tend to prefer using surrogate primary keys. There are exceptions where composites are necessary.

  15. #15
    Join Date
    Aug 2004
    Posts
    330
    In this case, the surrogate keys may be needed since you will probably want to consider which foreign keys should be optional. Do you want to be able to have a bag with no ingredients (this bag would contain only bags of ingredients)? Do you want to be able to include ingredients with no bags? Also, shouldn't the recursive relationship be moved to the Recipe-bag entity since bags should only contain other bags within a recipe.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •