Recursive Stored Procedure - nested tables, best practice.

I am currently defining a Calculation system, it will be used to calculate production cost for different products, the system will be running on SQLEXPRESS.

The idea is that i will have some ingredients.
These ingredients are then added to a calculation (defining how much of an ingredient and so on)

This of cause is very easy to do, my problem however is figuring our the best way to allow calculations to include other calculations which in turn can hold other calculations and so on.

When an ingredients price is changed the calculations using this ingredient should be recalculated, this however could result in calculations using these calculations also to be recalculated.

A very important issue here is that the calculations are calculated in the correct order (calculations holding other calculations should not be calculated before all included calculations are calculated).

My integration idea is to keep the price in the tbl_calculation in a nullable decimal field.
When an calculation or ingredient is changed i want to change all calculation prices which are affected by this change to null.
HOW SHOULD THIS BE DONE ? (recursively in a stored procedure until no more records are affected ?)

Then to calculate them again i am thinking the best idea would be to calculate all calculations which do not hold calculations with a non-calculated price (meaning price set to null)-
HOW SHOULD THIS BE DONE ? (recursively in a stored procedure until no more records are affected ?)


MY IDEA OF A TABLE STRUCTORE;
tbl_ingridient - holds ingridients
tbl_calculation - holds calculations
tbl_calculation_ingridient - holds information of which ingridients are included in the calculations.+ amount of ingridient. (this however is a simply multiplication)
tbl_calculation_calculation - holds information of which calculation are included in the calculations. + amount of calculation (this however is a simply multiplication)

As you can see i have some ideas, however i would like to do this as correctly as possible.

Secondly if my idea is good i would like to an example of how to do a stored procedure which recursively runs until no more records are affected.
mSchmidtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
Can you show us sample data so we understand your scenario better?

I thought it was ingredients that contained other ingredients -- not the calculations that were fundamentally nested.  Am I right?

Is it that:
Product A consist of:
 25% Compound Z
 75% Compound Y ... which consists of
     90% sub-compound alpha
      5% sub-compound beta
     5% sub-compound gamma

If that's the case your table structure is not quite right -- but again, an example would help.
0
mSchmidtAuthor Commented:
Sorry must not have specified the scenario quite enough.

A Calculation may consist of a number of ingredients and/or a number of calculations.

Example
Ingredients: IngrA, IngrB, IngrC
Calculations: Calc1,Calc2,Calc3,Calc4

Calc1
  - 2,5 x IngrA
  - 3,0 x IngrB

Calc2
  - 2,5 x IngrA
  - 3,0 x IngrB
  - 2,2 x Calc1

Calc3
  - 2,5 x IngrC
  - 1,5 x Calc2
  - 1,2 x Calc1

Calc4
  - 2,5 x Calc3
  - 10,0 x Calc2

As i hope you can see from this example the calculations can become quite entangled with each other.
I would of cause make some procedures to make sure no deadlocks appear.

Please reply if you need additional information.
0
Daniel WilsonCommented:
OK ... those things where one table links to EITHER TableA OR TableB are unpleasant ... but sometimes they happen.

Here's what I see:

tbl_Ingredient -- as you said, store everyting about the ingredient.
tbl_Calculation
  CalcID
  Name
  Maybe something else ... but I don't see much here
tbl_CalcDetail
  DetailID
  CalcID -- FK to tbl_Calculation
  Component_IngredientID -- NULLable -- populate if this calculation item multiplies an ingredient
  Component_CalcID -- NULLable -- populate if this calculation item multiplies another calculation
  Multiplier -- the number by which to multiply

If you like the table structure, I'll try to write a procedure using a recursive CTE to solve this.  

Are you using SQL Server 2005?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mSchmidtAuthor Commented:
okay as i see it you join these two tables into one table.

tbl_calculation_ingridient - holds information of which ingridients are included in the calculations.+ amount of ingridient. (this however is a simply multiplication)
tbl_calculation_calculation - holds information of which calculation are included in the calculations. + amount of calculation (this however is a simply multiplication)

This does of cause remove an extra table but it would also result in a lot of null values which you will then propably try to join on to.
Does this fact with alot of null values not make the database perform worse than it would with two tables where no null values would appear. ? (here you would ofcause need to union a result dependent on two queries)

However as i see it your table structure would be able to handle my problem, but as i said i would like to do it as correctly as possible.
0
ZberteocCommented:
The only way i see this being done is by using computed columns with functions to return the value of a calculation. You'll have to keep a clean and accurate record of all your functions.

Simply put, any product will have it's own functions for the calculations. You have to be careful because a computed column cannot be used in another computed column.
0
Daniel WilsonCommented:
In some ways your 4th table does clarify things ... within the Calculation, is the sequence of the details important?  If so, putting those details in 2 tables gets tricky again ... if not, your idea is probably better.

As for computing the thing ... I can't see computed columns doing it.  The computations must reference different rows ... even different tables.

I'm still seeing a recursive CTE ... which brings us back to -- is this in SQL Server 2005?  CTE's are new for 2005.
0
mSchmidtAuthor Commented:
Hi

Well if it isnt a performance issue to join tables on nullable values then i actually like your idea better (there is quite some extra information in the tables and currently i need to update two tables every time) and as you say ordering is actually important.

Iam using SQLEXPRESS which is 2005.
0
ZberteocCommented:
Product A consist of:
25% Compound Z
75% Compound Y ... which consists of
    90% sub-compound alpha
     5% sub-compound beta
    5% sub-compound gamma

What exactly this means? If i have 1000 units of product A then

A:
25% * 1000 of Z
75% * 1000 of Y
(
   75%(90%) * 1000 of alpha
   75%(5%) * 1000 of beta
   75%(5%) * 1000 of gamma
)

if yes then you need something like:

product table:
===========================
Products
ProdID        identity
Deescription  varchar(100)



composition table:

ProductComposition
===================================================================
ProdCompID  identity
ProdID      int (fk to Products)
SubProdID   (fk to products for the sub elements/products/compounds)
Proportion  float or decimal (18,5)

so you will have:

Products
ProdID / Description
1    A
...
100  Z
...
120  Y
...
150  alpha
151  beta
152  gamma

ProductCompozition
ProdCompID / ProdID / SubProdID / Proportion
1    1      100     0.25
2    1      120     0.75
2    120    152     0.90
3    120    152     0.05
4    120    153     0.05


and yes, in orser to calculate the final composition for product A for instance you will need a recursive query against ProductComposition becasuse product A points to Z, Y and Y points to alpha, beta, gamma

0
ZberteocCommented:
Don't worry about the NULL values, they are not an issue.
0
mSchmidtAuthor Commented:
Hi

I have read up on using CTE and i can see where you are going with this.
However in my situation where i have 2000 or more calculations which i would like to show in a list where all there sums are shown, this seems quite ineffecient.

With a recursive CTE you would propably end up calculating many of calculation many times because they are included in quite some calculations.
The hiarchy could even be 10-20 steps long.

It seems to me that it would be a way more efficient to only update the prices if a ingredient or calculation which is used in a specific calculation is changed.
This does ofcause give me some redundant data, however a calculation would only be calculated once each time.
If i could create a Storedprocedure which first set all the prices of the changed calculations to NULL (or have a bit indicating is should be updated)

Afterwards i would the calculate then calculate the calculation from the lowest level and on each recursion only updating calculations which does not include calculations which are not calculated.


PSEUDO CODE -

INGREDIENTCHANGED(ID) {
    SET PRICE TO NULL ON ALL CALCULATIONS THAT HOLD THIS INGREDIENT.
    WHILE (RESULTS AFFECTED > 0)
        SET PRICE TO NULL ON ALL CALCULATIONS THAT HOLD CALCULATIONS WHERE PRICE IS NULL
    RECALCULATE_CALCULATIONS
}
CALCULATIONCHANGED(ID) {
    SET PRICE TO NULL ON ALL CALCULATIONS THAT HOLD THIS CALCULATION.
    WHILE (RESULTS AFFECTED > 0)
        SET PRICE TO NULL ON ALL CALCULATIONS THAT HOLD CALCULATIONS WHERE PRICE IS NULL
    RECALCULATE_CALCULATIONS
}
RECALCULATE_CALCULATIONS {
     WHILE (RESULTS AFFECTED > 0)
          UPDATE PRICE ON ALL CALCULATIONS WHERE PRICE IS NULL AND THAT HOLD CALCULATIONS WHERE PRICE IS NOT NULL
}

I think this pseudo code is as effective as it can get, because it only calculates what is really necesary and the fact that it calculates them recursively makes sure that the prices are updated in the right order meaning only once for each change.

Is this a very bad idea ?
0
Daniel WilsonCommented:
Caching the values there would improve efficiency ... at the possible expense of accuracy.  It's awefully easy for cached values to get out-of-synch with reality.
0
ZberteocCommented:
I don't think you're listening and I don't really understand why you are obsessed with NULL. It is not NULL that will help you here but a good and simple design. I don't really understand that pesudocode and  where you're aiming at.

Sorry.
0
mSchmidtAuthor Commented:
DanielWilson
True however there is only two interfaces which will allow users to change these, so making sure to run the stored procedure that recalculates them each time something is changed would work, correct ?

How would you write a stored procedure that could execute something like my PSEUDO code.
or have i not described it well enough ?


Zberteoc.
I think your approach is almost the same as Daniel is suggesting however
Calc1 could be used in 1000 other calculations, Calc1 could consist of 20 other calculations which in turn consists of others.
If i use CTE it will execute the entire Calc1 tree for each calculation which holds it.

If I calculate the calculation using the idea i described in my PSEUDO CODE i would only need to calculate them once.
The idea of my PSEUDO Code is to store the cost of a calculation, making sure that they are calculated in turn.
Which means that calculation using other calculations simply use the stored cost (meaning calculations holding other calculations dont need to iterate through ingredients/calculations used by those calculations they simply need to make use of the stored cost.)

I hope this describes the idea a little better
0
mSchmidtAuthor Commented:
Daniel, is it possible to create this as a stored Procedure ?
0
Daniel WilsonCommented:
Can you provide a script of the tables as you'll actually create them?  This will be sufficiently complex that I want to get it right the first time!
0
Daniel WilsonCommented:
I'm assuming that the Calculation table will include an Amount field, and that is what we will be putting the cached figure into.

Is that what you had in mind?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mSchmidtAuthor Commented:
I solved it, using several Stored procedures.

one of them are shown here, . thanks for all your help though.
ALTER PROCEDURE dbo.clearCalc
AS
      /* SET NOCOUNT ON */
      update tbl_calculation set calculation_cost_price=null where not SOMETHING
      IF @@ROWCOUNT > 0
            exec clearCalc
      ELSE
            RETURN 0
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.