Best way to handle following case?

[Martin Lang]

Hey,

I am working on a database that will be used for declaration on products (food and stuff).

Following tables are of interest

- a table containing different ingredients (IDIngredient, Name, values such as energy, fat, protein, carbohydrats, purchase price)
- a table containing different products (IDArticle, Name,...)
- a table containing recipies (IDArticle, IDIngredient, Amount)
- a table that gives the ingredientlist per IDArticle


My challenge is that some (not all) of the products that the database calculates values for, (such as cost to produce, fat/energy/protein/carbohydrats per 100 gram) are used as ingredients in other products.... so the IDArticle ends up with being an IDIngredient in another recipie so to speak.

I am not sure what to do and would appreciate a hint or advice on what to go for, before I dig myself into a too big hole. I can always feel my way onwards.

(Just to give an idea) I have made a form (please see attached picture) where the user can put together the recipie and other types of information such as allergens etc. The user should be able to look up all the necessary ingredients here, including those IDArticles/IDAingredients. Once the user selects an ingredient, necessary information is written into the recipie table (and other relevant tables).

(My thinking so far) I guess first step would be a "check-box" so that the database can be able to identify which articles the user should be able to use as an ingredient.

Maybe the best would be a macro that writes those articles into the ingredienttable ? How to avoid double entries? The name on the article could change (user error) between macro updates, pluss IDArticle and IDIngredient can't be the same for those products. Maybe a new column in article table with autonumber. For each new "checked" article, the column gives a number, which is either written into ingredient table if it is a new record, or used as lookup function if it already exists...?

In worst case, the user would have to manually enter the product as an ingredient in the ingredienttable , but the calculated values should be dynamic, including the ingredientlist. ... so it would be really nice if there is a solution to it.

Any help is very much appreciated.

Best,

Martin

[NeoPa]

Martin Lang: - a table containing recipies (IDArticle, IDIngredient, Amount)
- a table that gives the ingredientlist per IDArticle

Why is the latter necessary when the former covers this information?

I assume generally, that your ingredients are mixed together using recipe data to form a product (or article). However, some products are themselves used as ingredients in other recipes Is that all correct?

[Martin Lang]

Why is the latter necessary when the former covers this information?

I assume generally, that your ingredients are mixed together using recipe data to form a product (or article). However, some products are themselves used as ingredients in other recipes Is that all correct?

Well, the latter is necessary when printing reports etc. Some reports, the user would like to have the ingredients shown as Bread (Flour, Water, Salt, Yeast) whereas other places the user would like it as
Bread
Flour
Water
Salt
Yeast

Nico's solution in another post makes the two tables necessary :)

To answer your last question... correct :)

[NeoPa]

Surely the Recipes table gives you that facility too.

Anyway. If you have a situation where an item can be both an ingredient, as well as a produced article, then it seems to me you have a choice of two approaches :

1. Have a recursive table that handles both. Its design would need to incorporate the fields needed for both types. Generally speaking, this involves a different structure and approach to getting data from it.
2. Less normalised, but can be practical, duplicate the resultant items that are also required in the Ingredients table. The more of such items that exist, the less appropriate this approach.

I would choose the former approach myself, but I do appreciate from earlier feedback that inexperienced db designers can find getting their heads around this approach quite difficult.

I'll leave it to you to make your decision.

[Martin Lang]

Interesting idea with recursive tables. I see how it can be designed. However, there are a number of data I do not know how to handle then. Just to give an example.

ID, Name, Energy
1, Ingredient 1, 50
2, Ingredient 2, 30
3, Product 1, ?

"?" depends on the recipe. The recipe table would give the answer, and I guess I then would have to create a macro that writes that calculated data ( a weighted average of the ingredient's energy) into the "?" field.... ?

Hmmm... I wish I had asked this question before. If I do it the number 1 way, it means I have to redo alot of my work.

How many items before number 2 way becomes inappropriate approach? 100, 1000, 10 000 ? I guess a maximum products with double entries would be something like 100. Necessary update every month or so...at most. (I guess we are talking about optimization etc here)

Number two way would be the least time consuming I guess...

Thanks for showing interest :)

Martin

[nico5038]

Hmm, this is the "classic" Bill of Materials problem.
One of the first problems I had to tackle in my programmers days :-)
For the background check: http://en.wikipedia.org/wiki/Bill_of_materials

The BOM table doesn't need to be recursive, but the processing will have to be recursive.
My application used a Main table with finished products and linked to that:
1) Products
2) Intermediate
3) Costs
The Products are from the "basic" products table and define the product price.
The Intermediate(s) are linked to a Main product and the Cost is e.g. for the labor needed to create the product.

For calculating the tblMain product price you'll need to
recursively work through all linked tables and an Intermediate will require a "fresh" start, until you find a Main product with no intermediates.

The problem of this processing is the fact that you'll need to make sure that there are no "loops" created.
This will be the case when an intermediate points "back" in the chain. To secure this you'll need to verify that no "branch" in the complete "product tree" holds the same Main ID.

Nic;o)

[Martin Lang]

Hmm, this is the "classic" Bill of Materials problem.
One of the first problems I had to tackle in my programmers days :-)
For the background check: http://en.wikipedia.org/wiki/Bill_of_materials

The BOM table doesn't need to be recursive, but the processing will have to be recursive.
My application used a Main table with finished products and linked to that:
1) Products
2) Intermediate
3) Costs
The Products are from the "basic" products table and define the product price.
The Intermediate(s) are linked to a Main product and the Cost is e.g. for the labor needed to create the product.

For calculating the tblMain product price you'll need to
recursively work through all linked tables and an Intermediate will require a "fresh" start, until you find a Main product with no intermediates.

The problem of this processing is the fact that you'll need to make sure that there are no "loops" created.
This will be the case when an intermediate points "back" in the chain. To secure this you'll need to verify that no "branch" in the complete "product tree" holds the same Main ID.

Nic;o)

Okay, I understand the concept.

1.I am not sure if I am able to translate it into my case.

The intermediate products are not intended to be a part of the finnished product table, I only need to "combine" intermediates and ingredients. So, I should make a Main ingredient table linked to table "basic ingredients" and table "intermedia te ingredients"?

How do you suggest the processing to be done? By macros that are run by the user now and then when updates are needed for e.g. cost? Maybe the only solution?

2. What are the gains here...? compared to ...? Or is it just a practical solution to a problem? It looks like I end up with double records one way or the other?

[nico5038]

Best to describe first the way your Main products are constructed.
E.g.:
Loaf
- Flower
- Water
- Yeast
XmastLoaf
- Loaf (see previous)
- Spice
- Sugar

Next decide or costs are only those of the "basic" products, or that labor, gas/electricity, etc. is needed.

Using some practical cases will help to understand what's needed. I normally start with the needed reports to see what's the requirement and "optimize" the datastructure based on that.

There won't be "double records", as an intermediate (see my "Loaf" example) is recorded once as a "Main" product and "reused" when it's "part" of another "Main" product (See XmasLoaf)

Nic;o)

Nic;o)

[Martin Lang]

Best to describe first the way your Main products are constructed.
E.g.:
Loaf
- Flower
- Water
- Yeast
XmastLoaf
- Loaf (see previous)
- Spice
- Sugar

Next decide or costs are only those of the "basic" products, or that labor, gas/electricity, etc. is needed.

Using some practical cases will help to understand what's needed. I normally start with the needed reports to see what's the requirement and "optimize" the datastructure based on that.

There won't be "double records", as an intermediate (see my "Loaf" example) is recorded once as a "Main" product and "reused" when it's "part" of another "Main" product (See XmasLoaf)

Nic;o)

Nic;o)

I am a bit anxious to use up the goodwill of this forum to fully understand how I should translate it into my situation. Also, I kind of sense the solution means a lot of redesign in my database and I am a bit hesitant to do so (cost vs benefit). I guess we are talking about at most 100 intermediates that needs to be written into the ingredients table. I have a solution that I think will work, but with double records. I think I will go for that one, unless you recommend me not to. Two negative consequences I see for myself is
a) optimization, double records makes the database "heavier"
b) risk of error, double records raise the risk for wrong data

However, when I am done with all the functionality I have planned for the database I am planning to remake the whole thing. Starting from scratch. Then, I will come back to this concept and make the db properly :) At this stage, I am a bit of running out of time to finnish it and let them start using it.

I feel bad for not taking advantage of your kind suggestion, but I anyway appreciate your effort to assist me :)

[nico5038]

I guess you only have to deal with a "one level" intermediate situation. This is exactly the sample I gave about the Xmas loaf that's reusing the Loaf. When there would be a "SuperLoaf" based on the "Xmas loaf", than we have a so-called "Two level" intermediate situation.
The "heavy" solution I proposed will handle infinite levels of intermediates and that's indeed "heavy" when there's just one "sub level".

In your case (one level) it's sufficient to have a table with Two ID's:
1) MainID
2) ParentID
The "real Products" will have only a MainID filled and the intermediates will hold a ParentID with the MainID of the "Real Product" they belong to.
By making a unique index on both fields you can guard that there will be no duplicate records.

I guess you now have experienced why I start with an elaborate design of my datamodel before starting to code :-)

Success with your application !

Nic;o)

[Martin Lang]

Hehe, YES, I can easy understand why you do so :)

The unique index is a good idea. And you are right about the one level thing.

I think where I am failing to understand is where to place the intermediates, as I see two options (either tblingredients or tblfinalproduct ). The way I am thinking tells me that the most logic place to put intermediates is in the ingredient table, not in the final product table, as the ingredient table is the place where the database looks up the elements that makes the recipe.

The reason why is how the recipe is made, either of ingredients only, or a combination of intermediates and ingredients (never intermediates alone). The form I attached picks only the records that exists in the ingredient table. The way I am thinking, I believe that I need the intermediates to be part of the ingredient table somehow.

The way I understand your suggestion I would make the Main table like this:
IDUnique, IDFinal, IDIntermediate
1, 1 Cake bottom,
2, 2 Strawberryjam,
3, 3 Cake with strawberryjam, 1 Cake Bottom
4, 3 Cake with strawberryjam, 2 Strawberryjam

In this example, cake bottom and strawberryjam are made in the bakery. That makes me wonder where those recipes should be placed. In addition, the final cake also consist of whipped cream. Where should that be recorded?

Maybe you see where I am misunderstandin g.

Sorry for taking it so slowly.

[nico5038]

No need for being sorry, as it's always taking some time to grasp an idea :-)

Your table should look like:
IDFinal, IDIntermediate, UnitPrice, Unit
1 (Cake bottom), 2.05, 1
2 (Strawberryjam) , .22, 1
3 (Whipped cream), 3.04, 1
4 (Cake with strawberryjam), 1 (Cake Bottom), 0, 1
4 (Cake with strawberryjam), 2 (Strawberryjam) , 0, 40
4 (Cake with strawberryjam), 3 (Whipped cream), 0, 20

As you can have different quantities for different Final products, you can fill the Units needed in the "Final product" and the price in the "basic" product.
Joining the table to itself by the IDFinal and IDIntermediate, will allow the calculation of the UnitPrice of the Final product in a groupby query.

Remains the problem of changing UnitPrices....

Nic;o)

[Martin Lang]

No need for being sorry, as it's always taking some time to grasp an idea :-)

Your table should look like:
IDFinal, IDIntermediate, UnitPrice, Unit
1 (Cake bottom), 2.05, 1
2 (Strawberryjam) , .22, 1
3 (Whipped cream), 3.04, 1
4 (Cake with strawberryjam), 1 (Cake Bottom), 0, 1
4 (Cake with strawberryjam), 2 (Strawberryjam) , 0, 40
4 (Cake with strawberryjam), 3 (Whipped cream), 0, 20

As you can have different quantities for different Final products, you can fill the Units needed in the "Final product" and the price in the "basic" product.
Joining the table to itself by the IDFinal and IDIntermediate, will allow the calculation of the UnitPrice of the Final product in a groupby query.

Remains the problem of changing UnitPrices....

Nic;o)

Okay, so this table should contain both the "lowest level ingredients" and all the recipes, by including the field amount?

IDFinal, IDIntermediate, UnitPrice, Unit, Amount
1 (Cake bottom), 5 (Flour), 2.05, 1
1 (Cake bottom), 6 (Egg)
1 (Cake bottom), 7 (Sugar)
2 (Strawberryjam) , .22, 1
3 (Whipped cream), 3.04, 1
4 (Cake with strawberryjam), 1 (Cake Bottom), 0, 1
4 (Cake with strawberryjam), 2 (Strawberryjam) , 0, 40
4 (Cake with strawberryjam), 3 (Whipped cream), 0, 20
5 (Flour)
6 (Egg)
7 (Sugar)

[nico5038]

Basically you're now changing the table into a Two level one.
The Cake Bottom is "Level 1" (consisting from basic materials being Level 0) and the Cake with strawberryjam is a "Level 2" product.

Here you see the need for "recursive" programming as you'll need to "descend" two levels to calculate the final price of the "Cake with strawberryjam"

When you have an established "fixed" maximum of levels this "programmin g" can be dealt with in a query by JOINING the table multiple times to itself.

Do you need to be able to record different prices for different dates for one basic product ?

Nic;o)

[Martin Lang]

Basically you're now changing the table into a Two level one.
The Cake Bottom is "Level 1" (consisting from basic materials being Level 0) and the Cake with strawberryjam is a "Level 2" product.

Here you see the need for "recursive" programming as you'll need to "descend" two levels to calculate the final price of the "Cake with strawberryjam"

When you have an established "fixed" maximum of levels this "programmin g" can be dealt with in a query by JOINING the table multiple times to itself.

Do you need to be able to record different prices for different dates for one basic product ?

Nic;o)

No, I dont think so. I think I better understand it now Nico. Im not sure if I can put it this way in English, but I guess it needs to mature for me.

In the meantime, I have managed to create an alternative solution. I used the code you made for me in the other post, and it works well except that I am struggling to move to a specific record for updates. Should I post a new topic?

best,

Martin