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

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?

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?

May I suggest:

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.

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.

You may want to re-do your ERD and add Customer to the mix.

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.

You're right urquel, I'm a bit rusty

Excuse my ignorance, but what is PK-FK relationship between these 2 tables in your diagram?

Here let's try for the third time...

Composite keys could be used, but I tend to prefer using surrogate primary keys. There are exceptions where composites are necessary.

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.