Obj design: store or not to store in db

esteban404 posted on Wednesday, April 08, 2009

I've got a project that does some comparison between current and previous data values to get a range for the data points. This is then charted. The charts are in-process but can also be brought up as history.

Should I store the comparisons in the database or create them manually (LINQ)? The problem I see storing them in db is that a sample can be deleted and all of the ranges would need to be recalculated. OTOH I'm typically only charting the current run.

Anyone have a similar construct or advice?

Thanks,

_E

dlambert replied on Wednesday, April 08, 2009

My "default" behavior would be to normalize & re-compute everything, and then modify that position when I've got a good reason.

Examples of good reasons to store pre-computed results:

Performance. If computing values takes a long time, and you know you're going to be hurt by re-computing values, then you can consider storing computed values. In this case, you'd want to think of the pre-computed values as sort of a persisted cache, and give some thought to state changes that might make you want to re-compute your "cache".
Historical tracking. If it's important (say, for legal reasons) that you can demonstrate exact values for computed fields, it's worth storing the as-computed values. Otherwise, you may find that when you go back to look at historical data, you re-compute fields for display and produce different output than you did when you ran originally. This can happen if you change algorithms, lookup values, or other inputs since you originally recorded data.

Give some thought to usage scenarios. When you talk about "history", are you talking minutes, days, months, years? Given that you run a graph right now, and then you come back and look at it in a year, is it important that you see exactly the same graph, or would you expect to see the graph recalculated with the newest available input data?

You'll have to chase down the details based on your business scenario, I think, but that's the sort of thought process I'd apply.

esteban404 replied on Wednesday, April 08, 2009

Thanks for the reply, that's along the lines I was thinking. My decision has been to not store the values since they are computed as the process is happening. History for this app is any data related directly on the program being analyzed and ordered by test date. If at some point I do store it, I can add a trigger to the database on delete actions to recalc the values and cascade it through.

They also want a predetermined number of results no more than 30 data points, so I think letting the client do the calculation will be OK. Some info came to me finally as I was working on it, so I'll see if it works well enough.