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?