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?

  • 2
    One key question is 'how often do people want to query this data?' Is it a report, or a heavily trafficked screen in the application? Mar 30 '12 at 12:43
  • @ConcernedOfTunbridgeWells In this case, it's a report that's run a few times a month, maybe more often if we let the agents run the report themselves to view their commission.
    – Rachel
    Mar 30 '12 at 12:47
  • Probably best off to build it into a reporting table on an overnight process, and the commission is 'as of last night'. If you have a close process where you need to close then report then you could provide a facility in the app to force a rebuild. Mar 30 '12 at 12:48
  • "AsOf" dates are fairly common with these sorts of operations in a financial context, in my experience. Thus, a table (as @ConcernedOfTunbridgeWells notes) with such an "AsOf" date should perfectly acceptable.
    – swasheck
    Mar 30 '12 at 14:56
  • Related post: dba.stackexchange.com/q/7592/2660 Apr 3 '12 at 6:46

If the query is run fairly infrequently (for example a report) then building the table on the fly is probably better1. If the query is run frequently and the temp table is required for performance then you potentially have a problem.

  • If the table is cheap to build, then do it as a temp table. As long as the database is fast enough you may get away with it. However you will need to keep an eye on performance.

  • If the table doesn't have to be totally up to date but will be the subject of relatively frequent reporting activity than a periodic rebuild is probably the best way to go.

  • If the table is expensive to build but needs to be up to date you may need to manage it as a denormalised structure, either maintained as an indexed view or through triggers. This is rather more complicated and places an additional burden on write operations.

    In more extreme cases (i.e. large data volumes) you may need a hybrid approach where historical data is queried from a denormalised structure optimised for performance and current data is queried from the live application.

    The most extreme cases of this can get you into low-latency data mart feeds and hybrid OLAP solutions, so this is by far the most complex in terms of how deep the rabbit hole can go. It's best avoided unless you have a genuine requirement.

In the case you describe above a periodic rebuild of a reporting table sounds appropriate. If you need to close in the middle of a day to run reports then you could provide a facility to force an update from the application. Otherwise run it on an overnight process and the agents can see their commission 'as at midnight on the previous working day.'

1 select into queries creating temp tables are quite fast on SQL Server because the insert operations are minimally logged.

So to summarize, you use the following factors to determine if you should have a new table for your data or not:

  • How often the data is needed
  • How expensive it is to get the data
  • How up-to-date the data needs to be
  • 1
    So basically the only two factors you use in determining if you need a permanent table for the data instead of querying for it when needed are how often the data is needed and how expensive the query is?
    – Rachel
    Mar 30 '12 at 13:10
  • 2
    @Rachel - Also, 'how up to date does the data need to be?' Mar 30 '12 at 13:14

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.

  • Thanks, that is definitely something to consider when making this sort of decision. This time, the value won't change because the commission rate is set once per agent and per client when the client is obtained, and the rate used is based off the date of the payment and the date we received the client, neither of which are values that change.
    – Rachel
    Mar 30 '12 at 15:02
  • @Rachel - Neither of which are values that you currently plan to change. Of course, if they do change you can always create a historical data table at that time, if you need it, as long as you don't forget about the issue.
    – psr
    Mar 30 '12 at 18:59

Probably not of interest if you're locked into a particular database, but MariaDB (MySQL-based work-alike) has something wonderful called "virtual columns" that can either be calculated on-the-fly or cached in actual storage, but automagically re-calculated as needed. I've missed this functionality since I left FileMaker Pro for the SQL world many years ago...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.