| Monte on May 11, 2010 at 4:37:56 AM (# 3) Bod:
Thanks! Sorry it's been so long since I have been able to reply.
The A, B, C, and D are calculated values, but they are calculated based on input from the user.
So something like:
A = 2 + 2
in my initial post would probably really resemble
A = X + Y
Where X and Y are user input values.
I'm thinking that I'll go with your "user supplied" answer, and store the values (not the sub-values, "A", for instance, but the actual user input), and the result.
And so if I had:
A = X + Y
B = E - F
C = G * H
D = I / J
Result = A + B + C + D
The only values I would store in the DB are X, Y, E, F, G, H, I, J, and Result.
ja928 on May 11, 2010 at 5:49:30 AM (# 4)I'm with bod, but I'd want to ask about the relative horespower required for the calculations, too. If the users' machines are adequate for the task, compute it at the client. If they are rather outdated, you may be able to get a better response time processing at the server. Always minimize the roundtrips to the DB.
bod1467 on May 12, 2010 at 8:21:40 AM (# 5)I don't think these calcs are being done at the client, rather at the server machine that does the backend stuff and database interface. (i.e. the web server).
For something like this you can't rely on the client machine allowing scripting to run. ;-)
Terry Young on Nov 15, 2010 at 2:17:12 PM (# 6)
This message has been edited.I actually see two questions combined into one: a database design question, and a performance/scalability question.
Purely from a database design perspective, I would actually tend to store the most raw values rather than calculated ones, at least for starters. (Of course, this really depends on actual needs and requirements.)
An example illustrates this. Take an invoicing system for example. You may have Unit Prices, Quantities, a Sub Total, a discount of the sub total, and a Grand Total. In this case you can already see two layers of formulas.
In time, you might want to enhance this system so that for each Unit Price * Quantity you would also have a Discount specific for that item only. You might take it further to support concepts such as, say, Buy 2 and Get 1 for free. My point is I generally tend to at least keep the atomic elements.
Yes, the sad truth (if not the whole truth) in this industry is we often need to anticipate changes and enhancements. Things change as soon as better ideas come up. But here's another idea: To store both.
And I have seen this. An example to illustrate would be monthly/annual reports within an Accounting system. I have seen designs where it provides functions to re-calculate monthly/annual totals (and in relation, to freeze months/years of data simply because, say, they are already audited).
Now imagine you now need to display a report of monthly totals that occurred four years ago. Surely, it seems to make more sense if you would use the other table that has already pre-calculated them, instead of calculating them once again in runtime right from the beginning of the account, which started from, say, ten years ago.
So do also consider the possibility of actually storing both the raw and calculated values, usually in separate tables. You then have the flexibility to choose which approach to use according to the actual task.
The second perspective is a performance one. I would simply benchmark them and find out. There is no definite answers to this until you actually do a benchmark yourself, specific to the situation. Plotting the cases and performances onto a chart almost always produces a curve. You never know there may be situations where client-side/server-side calculations might actually be faster than a calculation done directly on the database layer until you do it in a large scale, or at least simulated.
A good example (not a perfect one) of what I mean by this is doing an intensive loop of string concatenation in classic ASP. Without knowing the nature of how memory is handled in the case of string concatenation in classic ASP, all you have is really a theory until you actually do it (or find out).
This is why benchmarking exists. So my second answer is to actually do the benchmarks and find out.
I guess that's four cents from me.
The art of computer engineering is beyond just design or logic, but how to strike a balance. The art of engineering is to choose which way to skin the cat.
Data and logic is one beast. Performance and scalability is another. They are not to be approached with the same methodology, and the latter can't be done best by only relying on theories.
Monte on Nov 16, 2010 at 6:12:55 AM (# 7)Terry:
That sounds like a good approach. I could store the initial values, then the sub-calculated values, then the result.
Good idea.
Terry Young on Nov 17, 2010 at 5:22:03 AM (# 8)
This message has been edited.It's a two-way sword if you think of it.
It's more work, obviously. And more points of failure/bugs.
And if you allow changes of initial values, you have to provide re-calculation features. Then another balance needed to strike: when exactly should the re-calculation happen.
If you choose not to re-calculate upon changing/updating initial values, you'd flag certain sub-calculated values as 'dirty'.
Then your app could provide functions for users to manually invoke recalculations at will, or have reports automatically recalculate if it detects dirty sub-calculated values.
Choice is yours (or your users')
computer repair on Nov 3, 2011 at 8:12:43 AM (# 9)Very good information - I needed this to solve a problem of my own. Good effort.
Thanks Computer Repair
|
General Question -- Calculations in DB versus app code...