Design advice

--00148539414219b71f0491dafe8d
Content-Type: text/plain; charset=ISO-8859-1

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.

Thanks,
Neil

--00148539414219b71f0491dafe8d--

Fwd: Design advice

--001636eedee761edcf049207cca8
Content-Type: text/plain; charset=ISO-8859-1

Wonder if anyone can help me ?

---------- Forwarded message ----------
From: Tompkins Neil
Date: Tue, Oct 5, 2010 at 10:07 AM
Subject: Design advice
To: "[MySQL]"


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.

Thanks,
Neil

--001636eedee761edcf049207cca8--

Re: Design advice

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Design advice

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)"
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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Design advice

On 10/8/2010 3:31 PM, Neil Tompkins wrote:
> 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 ?
>

In my experience, the details matter. Also in my experience, as soon as
you designate some bit of data as "useless" it will somehow become
critical that you find it again.

You should probably keep that lowest-level detail data somewhere safe
even if you never plan to need it for direct statistics reporting after
you use it to generate the first level or two of time-based summary tables.

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org