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
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.
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
Last edited by DelbertZZZ; 11-09-05 at 14:31.
Reason: addition for jpg
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?
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.
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.
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.
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.