0

I have a table which is basically a "flat" list of all the purchase transactions done in the system - no aggregation of any sort.

I was wondering if there are any best practices or guidelines as to when should I store calculations in another column for future use and when should I do the calculations on the fly?

For example, if a user has 2 transactions purchasing NFLX and 3 transactions purchasing PANW, and I would like to know the total amount this user spent - if I didn't have another column I would need to calculate each stock separately since they have different prices in order to conclude the total sum. However, if I have another column for each row which calculates the total amount spent on the transaction (price * numOfShares) I could do a simple sum aggregation of all these columns for this user. But with that approach, am I "double storing" information I could extract without having to store it?

Thanks

2

Wow. Such a simple question with a complicated answer! The short answer is "it depends."

It depends on what your goals are. If processing time and resources don't matter, then it's whatever you want to do. When learning, as is the case here, it's best to learn and practice both methods.

Now, let's think about when something matters. If execution time is most important? Will the calculated sum be queried rarely or frequently? Is there plenty of space available or is it limited? Are there a few concurrent users or millions? All these factors come into play.

First, let's assume that there are millions of users, plenty of space and the calculation is accessed frequently. In this case you'd want to do the calculation once (when the row is created) and store the result in a column. That way, it can be directly accessed without being recalculated each query. This is the best practice for this scenario.

Now, let's assume the same conditions, except that the calculation is rarely used. In this case, it's probably best to calculate on the fly. Remember, millions of users making purchases. If the calculation is stored in a field/column, then every purchase consumes the time to make that calculation, but the value is rarely used. Let's say that it's used once for every 1000 purchases. That means that the calculation time to store it may be 1000 times more often than if it were calculated on the fly when needed. (You'd need to do a more accurate assessment of time used to calculate the value vs. how often it's used, but you get the idea.) Also, it would be consuming memory where it probably isn't important enough to do so.

So, you get the idea. You have to look at all of the needs and decide what's important and what's not to assess whether to calculate those values and store them or to do it on the fly.

This question is so common that the leading database products have provisions and tools specifically for this. Entire tables are often created to pre-calculate and store values based on common queried that are run on the database. Some more extreme, but fairly common examples are queries that take minutes to hours to run in their raw form, but can be precalculated and the results can be later queried within milliseconds on demand.

If you want to learn more, do a google search for "materialized query tables".

But, since you're just learning about this material, I'd suggest that you try to do both techniques to understand them. ;-)

If this answers your question, please click on the check mark to accept. Let's keep up on forum maintenance. ;-)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .