How can I identify when to create a new table to hold data that can be obtained from a query?

We have a payment table, and agents get commission on payments. Commission is based on a few different factors, such as how long it took to get the payment, so there is some calculations involved when figuring out the commission rate the agent gets, but nothing obscenely complex.

For example, it will probably never be more complex than this:

SELECT Payments.Amount * CASE 
    WHEN DateDiff(year, Client.Received, Payments.DatePaid) = 1 THEN Rates.Rate1
    WHEN DateDiff(year, Client.Received, Payments.DatePaid) = 2 THEN Rates.Rate2
    ELSE Rates.Rate3 END

Would it make sense to build a 2nd table to hold this data instead of querying for it anytime it's needed? Or should I just stick with run-time queries that pull the data whenever it's requested?

And more importantly, what are the factors to use when determining if a query should be run anytime the data is needed, or if the data should be stored in a separate table of it's own?

One issue not covered in the accepted answer is "do you need this value over time" and "will the formula possibly change".

For instance consider the commision example. If the commission is paid, the amount should be stored as that is a historical figure of what was actually paid. The way to calulate commisions could change next month (and frequently does) but that won't change what was actually paid which must be stored separately.

It is the same idea as storing the price the customer actually paid for a product (after a calculation of discounts etc.) rather than relying on a formula against a price table to do anything except the initial calculation because the product price next month might not be the same as what the price was when the cutomer made the order.

If you need a historical record of what the value was at a point in time, always store that value after using the formula for the inital calulation.

StackExchange Database Administrators Q#15859, answer score: 9