473,320 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Best way to handle following case?

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
Attached Images
File Type: jpg Userform.jpg (13.1 KB, 212 views)
Jul 7 '10 #1

✓ answered by 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)

24 1836
NeoPa
32,556 Expert Mod 16PB
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?
Jul 7 '10 #2
@NeoPa
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 :)
Jul 7 '10 #3
NeoPa
32,556 Expert Mod 16PB
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.
Jul 7 '10 #4
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
Jul 7 '10 #5
nico5038
3,080 Expert 2GB
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)
Jul 7 '10 #6
@nico5038
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 "intermediate 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?
Jul 7 '10 #7
nico5038
3,080 Expert 2GB
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)
Jul 7 '10 #8
@nico5038
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 :)
Jul 8 '10 #9
nico5038
3,080 Expert 2GB
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)
Jul 8 '10 #10
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 misunderstanding.

Sorry for taking it so slowly.
Jul 8 '10 #11
nico5038
3,080 Expert 2GB
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)
Jul 8 '10 #12
@nico5038
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)
Jul 8 '10 #13
nico5038
3,080 Expert 2GB
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 "programming" 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)
Jul 8 '10 #14
@nico5038
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
Jul 8 '10 #15
Ill post it in a new post. A couple of errors I can't figure out. Thank you for helping me out on the above issue. It extends my mind :)

Martin
Jul 8 '10 #16
nico5038
3,080 Expert 2GB
The "move to a specific record for updates" is visible in my function where the rsI2 is set within the rsI loop.
The rsI2 recordset uses the ID from rsI to filter just the rows for the current main loop for rsI.

Getting the idea ?

Nic;o)
Jul 8 '10 #17
Yes :) The place where you have Set rsI2 = ?

But what about
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere As String
  2. strWhere = "[Artikkelnr] ='" & rsI!IDRåvare & "'"
  3. rsO.FindFirst strWhere
  4.  
I get an errorcode 3464 Data type mismatch....
Jul 8 '10 #18
I managed to solve it with this code

Expand|Select|Wrap|Line Numbers
  1. Dim strWhere As String
  2. strWhere = "[råvare].[Artikkelnr] =" & rsI!IDRåvare & ""
  3. rsO.FindFirst strWhere
  4. rsO.Edit
  5.  
Thanks for sharing. I love that code you made. So easy. I also now understand the SQL string better.

But hey, I am facing the "same" memo challenge as last time.

This time, I am reading the memo field directly from the table. (No query as last time.)

The code looks like this
Expand|Select|Wrap|Line Numbers
  1. Function fncOverføreHFtilråvare()
  2.  
  3. Dim rsI As DAO.Recordset
  4. Dim rsI2 As DAO.Recordset
  5. Dim rsI3 As DAO.Recordset
  6. Dim rsI4 As DAO.Recordset
  7. Dim rsO As DAO.Recordset
  8.  
  9. ' init table
  10. CurrentDb.Execute ("Delete * from tblIngrediensliste")
  11.  
  12. ' Select the unique IDArtikkel
  13. Set rsI = CurrentDb.OpenRecordset("Select IDArtikkel, Hfnavn, IDråvare from Halvfabrikat")
  14. Set rsO = CurrentDb.OpenRecordset("Select * From råvare")
  15.  
  16. ' Loop to add one row for each IDArtikkel
  17. While Not rsI.EOF
  18.     Set rsI2 = CurrentDb.OpenRecordset("Select Ingrediensliste From tblIngredienslisteHF WHERE IDartikkel = " & rsI!IDArtikkel & "")
  19.     Set rsI3 = CurrentDb.OpenRecordset("Select [sum av kj1], [sum av kcal1], [sum av fett1], [sum av protein1], [sum av karbohydrat1] FROM qryNæringsinnholdHalvfabrikataPåArtikkelnivå WHERE IDHalvfabrikat = " & rsI!IDArtikkel & "")
  20.     Set rsI4 = CurrentDb.OpenRecordset("Select totalenhetskost From qryTotalkostPerEnhetHF WHERE IDartikkel = " & rsI!IDArtikkel & "")
  21.     If IsNull(rsI!IDRåvare) = True Then
  22.         rsO.AddNew
  23.         rsO!Råvarenavn = rsI![Hfnavn]
  24.         rs0!Råvaredata = rsI2!Ingrediensliste
  25.         rsO!Kalkulasjonspris = rsI4!totalenhetskost
  26.         rsO!Kj = rsI3![sum av kj1]
  27.         rsO!kcal = rsI3![sum av kcal1]
  28.         rsO!Protein = rsI3![sum av protein1]
  29.         rsO!karbohydrat = rsI3![sum av karbohydrat1]
  30.         rsO!Fett = rsI3![sum av fett1]
  31.         rsO.Update
  32.         rsO.MoveLast
  33.         IDRåvare = rsO!Artikkelnr
  34.         rsI.FindFirst IDArtikkel = rsI!IDArtikkel
  35.         rsI.Edit
  36.         rsI!IDRåvare = IDRåvare
  37.         rsI.Update
  38.     Else
  39.         Dim strWhere As String
  40.         strWhere = "[råvare].[Artikkelnr] =" & rsI!IDRåvare & ""
  41.         rsO.FindFirst strWhere
  42.         rsO.Edit
  43.             rsO!Råvarenavn = rsI![Hfnavn]
  44.             rs0!Råvaredata = rsI2!Ingrediensliste
  45.             rsO!Kalkulasjonspris = rsI4!totalenhetskost
  46.             rsO!Kj = rsI3![sum av kj1]
  47.             rsO!kcal = rsI3![sum av kcal1]
  48.             rsO!Protein = rsI3![sum av protein1]
  49.             rsO!karbohydrat = rsI3![sum av karbohydrat1]
  50.             rsO!Fett = rsI3![sum av fett1]
  51.             rsO.Update
  52.     End If
  53.  
  54.     rsI.MoveNext
  55. Wend
  56.  
  57.  
  58. End Function
There are two places where the error occur (before and after "else", and both are the line

Expand|Select|Wrap|Line Numbers
  1.            rs0!Råvaredata = rsI2!Ingrediensliste
  2.  
Any idea? I get the error code 424 "object required"...

(This code btw is my fix to the BOM issue... :)

Martin
Jul 8 '10 #19
nico5038
3,080 Expert 2GB
You forgot the mandatory single quotes to surround your text field IDArtikkel.

Use this:

Expand|Select|Wrap|Line Numbers
  1.     Set rsI2 = CurrentDb.OpenRecordset("Select Ingrediensliste From tblIngredienslisteHF WHERE IDartikkel = '" & rsI!IDArtikkel & "'")
  2.     Set rsI3 = CurrentDb.OpenRecordset("Select [sum av kj1], [sum av kcal1], [sum av fett1], [sum av protein1], [sum av karbohydrat1] FROM qryNæringsinnholdHalvfabrikataPåArtikkelnivå WHERE IDHalvfabrikat = '" & rsI!IDArtikkel & "'")
  3.     Set rsI4 = CurrentDb.OpenRecordset("Select totalenhetskost From qryTotalkostPerEnhetHF WHERE IDartikkel = '" & rsI!IDArtikkel & "'")
  4.  
Nic;o)
Jul 8 '10 #20
@nico5038
Noop, it is not there the error arise. It works without the single qoutes, because in this case I deal with a numeric value. It happens right on that line I extracted. When I click debug, VB marks that line yellow.

Confusing....

When I move the cursor over the rsI2!ingrediensliste, a light yellow box pops up displaying the correct information.

When I move the cursor over the rs0!råvaredata, a light yellow box pops up displaying rs0!råvaredata =<object required>
Jul 8 '10 #21
hehe, maybe because I had entered zero 0, instead of letter O.

Problem fixed.

Thank you anyway Nico :)
Jul 8 '10 #22
nico5038
3,080 Expert 2GB
Glad it's solved, I also overlooked the 0 / O problem <LOL>

Nic;o)
Jul 8 '10 #23
NeoPa
32,556 Expert Mod 16PB
That's bizarre!

I wondered where the rs0 came in when I read that code. I just didn't make the link to what I'd read (rsO) much earlier in Nico's code. Doh!!

Otherwise, you guys had better stop posting so fast. You're making it hard to keep up with just reading all the updates :D
Jul 8 '10 #24
Hehe, I know. I am so thankful that Nico has been able to help me. It is a huge relief. Now, I know for sure that mission will be acomplished, something I didn't know before I started. Eagerness to learn and brilliant forums as this one, and the sky is the limit :)
Jul 9 '10 #25

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Seb... | last post by:
Hi, I m looking for an algorithm for an optimization. I know there are a lot of existing algoritm. Here is my problem : I have serveral ring to do, defined by external and internal...
3
by: Ing. Branislav Gerzo | last post by:
Hello all, user have in browser address bar this URL: http://localhost/store/CDfilter.asp?catid=3&lngid=3&osid=7&ordertype=desc&orderby=release_date&PerPage=50&PageNum=2 I have on page filter...
19
by: Johnny Google | last post by:
Here is an example of the type of data from a file I will have: Apple,4322,3435,4653,6543,4652 Banana,6934,5423,6753,6531 Carrot,3454,4534,3434,1111,9120,5453 Cheese,4411,5522,6622,6641 The...
1
by: Dan11 | last post by:
here's C sharp code: Start code... private void searchBtn_Click(object sender, System.EventArgs e) { //For sqlCommand1 is a search by Wkst ID (employeeID on pubs) params if...
9
by: lli | last post by:
Hi Guys, I am a new cgi programmer. Now I need to design a web application: 1. first, user login by their username and password in a form (login form). When users click submit button it connect...
3
by: chris | last post by:
hello, I can't seem to make this work: VS2005 I have a simple program that uses a backgroundworker control to execute a long process (webservice call) if that webservice call fails, i want to...
8
by: dmoore | last post by:
Hi folks, I've seen the following issue come up in multiple posts to this mailing list: I have a python program that spawns a child process with popen or popen2 or popen3 or popen2.popen2...
0
by: Ronald S. Cook | last post by:
How would you recommend passing errors back from your proc to your business tier to the client (assuming you have full control of all layers)? Take InsertUser for instance. The proc will...
1
by: Don Li | last post by:
Hi, Env: MS SQL Server 2000 DB Info (sorry no DDL nor sample data): tblA has 147249 rows -- clustered index on pk (one key of datatype(int)) and has two clumns, both are being used in joins;...
5
by: Ben | last post by:
Hi! I have a trigger created for Customer table. My front-end is access. What is the best approach to handle a trigger result when adding a new customer record? Below is the trigger script:...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.