List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:October 8 2010 6:46pm
Subject:Re: Design advice
View as plain text  
Hi Neil,

On 10/5/2010 5:07 AM, Tompkins Neil wrote:
> Hi
>
> I have a number of tables of which I use to compute totals.  For example I
> have
>
> table : players_master
> rec_id
> players_name
> teams_id
> rating
>
> I can easily compute totals for the field rating.  However, at the end of a
> set period within my application, the values in the rating field are
> changed. As a result my computed totals would then be incorrect.
>
> Is the best way to overcome this problem to either compute the total and
> store as a total value (which wouldn't change in the future), or to store
> the rating values in a different table altogether and compute when required.
>   If you need table information please let me know and I can send this.
>

Many databases designed for rapid, time-based reporting do exactly as 
you propose: build a table just to hold the aggregate of a time-interval 
of values.

Here's a rough example.

Let's say that you run a web site and you want to track your traffic 
levels.  Every second you may have thousands of hits, every hour 
hundreds of thousands of hits, and by the end of the week you may have 
hundreds of millions of individual data points to report on. To compute 
monthly stats, you are looking at a huge volume (billions) of data 
points unless you start aggregating.

Lets say you build tables like: stats_hour, stats_day, stats_week, and 
stats_month.

Every hour, you would take the last hour's worth of traffic and condense 
those values into the stats_hour table. At the end of the day, you take 
the previous 24 entries from stats_hour and compute a stats_day entry. 
Each level up aggregates the data from the level below.

Does that give you an idea about how other people may have solved a 
similar problem?

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
Thread
Design adviceTompkins Neil5 Oct
  • Fwd: Design adviceTompkins Neil7 Oct
  • Re: Design adviceMySQL)8 Oct
    • Re: Design adviceNeil Tompkins8 Oct
      • Re: Design adviceMySQL)10 Oct