Forum OpenACS Q&A: Using math with CONNECT BY

I have a complex (and perhaps interesting) SQL question for you all:

Let's say I have an INGREDIENT table for recipes, that looks something like this:

INGREDIENT
id               primary key,
name             varchar(200),
carbohydrates_g  number

Ingredients can also be composed of several other ingredients. For example, let's say you have a pancake mix that is composed of flour, eggs, and salt.

MIX
master_ingredient    references INGREDIENT,
component_ingredient references INGREDIENT,
percent              number

Thus, an entry for this pancake mix would be in INGREDIENT:

id 5
name "pancake mix"
carbohydrates 0

The pancake mix has three components, so there are three entries in the MIX table:

master_ingredient      5  
component_ingredient   3 (let's say three is water)
percent               33 (let's say the mix is 33% water)

master_ingredient      5  
component_ingredient   2 (let's say two is flour)
percent               20 (let's say the mix is 20% flour)

master_ingredient      5  
component_ingredient   1 (let's say one is salt)
percent               47 (let's say the mix is 47% salt)

It's not terribly difficult to figure out what the total carbohydrate content for this pancake mix is, but what if a mix contains another mix? It becomes a recursive endeavor.

I could do this with Tcl, and just sum everything up, but I imagine a better solution might be found using SQL, and perhaps using CONNECT BY. I've never used it for summing things up in a tree before, however.

Sql for Smarties has a section that discusses this. In his example, you have parts for a machine. Each of the parts is composed of component parts, and the weight of the part is based on the sum of the weight of the components. He figures it out iteratively, by going through a table and saying component A's weight = sum of all child components. It doesn't really seem very ideal.

Has anybody encountered this issue before, and solved it satisfactorily?

with properly defined triggers there's no reason you should end up with bad data.  as long as your number of updates is low compared to reads you don't really have to worry how efficient the actual update computation is.

yes, updates performed by triggers will fire other (including "recursive") triggers just as any other update would.

since you're dealing with a tree structure (no cycles) this means your job is pretty simple.  doing something like

update table set weight = weight + (new.weight - old.weight) where id = new.parent_id

should cover you (allowing reparenting of nodes would complicate things some, but you could check for and handle that if you needed to).  top-level nodes should have parent_id = null so an update there will result in zero rows updated (by the trigger) and no subsequent triggers firing.  similarly for your ingredients/mix tables, your "top level" incredients will not exist as components in the mix table.

jade, use global temporary tables to temporarily store values and get around the mutating table problem.

I would start with three tables:

ingredients
 ingredient_id
 recipe_id references recipes default null
 name
 carb_count_per_measure
 measure_id

recipes
 recipe_id
 name
 general_instructions
 expected_yield
 measure_id

recipe_ingredients
 recipe_id ref recipes
 ingredient_id ref ingredients
 quantity
 measure_id
 add_order
 add_instructions

Although an ingredient may be composed of other ingredients, it still will need a recipe. It also isn't obvious that once a recipe is prepared, that all the carbs, fat, etc. that went into making the recipe make it through. For example, water may evaporate or drain away, salt could be washed off, so totalling up the nodes may not represent the final product.

A recipe can be used to calculate the cost of the final product, or a shopping list, but if an ingredient is created by using a recipe, that new ingredient should have it's own carb_count, etc.

Now the connect by problem moves to the calculation of the shopping list, but I believe you would be better served by moving this calculation into a procedure.

Here is an example: Pasta Inc. creates a dried pasta product. Their recipe will include a little water and semolina. Most of the water will evaporate during processing, some of the semolina will be lost during the manufacturing process (shrinkage). The final product will be an ingredient in their retail store's biggest seller: Cooked Pasta. The recipe for this product will consist of quite a lot of water and salt. Most of this will be lost, some of the carbs will dissolve in the salt water that is drained away. The final weight and carb count will depend on the length of cooking the pasta. Cooked pasta will be an ingredient to the final product: Pasta with your favorite sauce. Yumm.