[thelist] Database Design Question

[Luther, Ron]

Judah McAuley noted:


>>Carolyn Jewel wrote:
> It's rarely a good idea, imho, to store the results of a calculation. 
> If you need history (as in you need to know that the result of a 
> calculation was X on date:time Y, then it might make sense to store 
> the result in a table.
> 
> The problem with storing the result of the calculation is that you 
> must either risk showing stale data or perform the calculation again 
> anyway.
> 

>>In general, I like using Views for this. Grab the data that you need, 
>>perform the calculations that you need all in a nice presentable table

>>that is easy to query. 


Hi Judah || Carolyn!


Both good answers. I use both of these techniques and I can certainly 
think of a number of situations where each may be considered the 'best' 
approach.

However, (as a 3rd opinion), I don't think it's so rare to find that 
'storing' the calculation, for other situations, may, in fact, be the 
'best' approach.

Ex 1 - Let's say I have a detail table with 45M records and users want
to 
see summarized results. I get much better performance reporting
summarized 
results from a separate summary table created by a batch process running

through the details than I would with either a view or a summary on the
fly.

Ex 2 - Let's say I have a need to report the results of a very
complicated 
differencing between separate largish data feeds. Again, (for this
particular 
case), saving that difference to a physical file improves the
performance 
of my app.


I guess the real moral here is "It Depends" ... you have understand your

processing flow and select a technique appropriate for what you are
doing.


HTH,

RonL.