From: Neil Tompkins
Date: October 8 2010 7:31pm
Subject: Re: Design advice
List-Archive: http://lists.mysql.com/mysql/223268
Message-Id: <0E8EFE33-52DA-433C-A630-20D6D52147CD@googlemail.com>
MIME-Version: 1.0 (iPod Mail 7E18)
Content-Type: text/plain;
	charset=us-ascii;
	format=flowed;
	delsp=yes
Content-Transfer-Encoding: 7bit

Hi Shawn

Thanks for your response.  In your experience do you think I should  
still retain the data used to generate the computed totals ? Or just  
compute the totals and disregard the data used ?

Regards
Neil

On 8 Oct 2010, at 19:46, "Shawn Green (MySQL)"  
<shawn.l.green@stripped> wrote:

> 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