Solved
Recursive Stored Procedure - nested tables, best practice.
Posted on 2008-10-16
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.