Best method to keep totals

--00163630f0e31b7f50048f388d17
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I'm developing a system whereby a manager gets assigned points based a
certain number of factors which are saved alongside a football result.
However, I also want to keep a total for each manager. My question is it
best to just have a query that uses SUM to total the managers points gained
for each fixture participated in ? Or should I be looking to have
a separate field which is the calculated total for each manager ?

Thanks in advance for any advice.

Cheers
Neil

--00163630f0e31b7f50048f388d17--

RE: Best method to keep totals

IMNSHO, never store dynamic data in a field unless you absolutely have to.

There are going to be exceptions, but unless a manager is going to participate
in hundreds of thousands of fixtures I don't think this is one of them.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com

>-----Original Message-----
>From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
>Sent: Wednesday, September 01, 2010 4:29 PM
>To: [MySQL]
>Subject: Best method to keep totals
>
>Hi,
>
>I'm developing a system whereby a manager gets assigned points based a
>certain number of factors which are saved alongside a football result.
> However, I also want to keep a total for each manager. My question is it
>best to just have a query that uses SUM to total the managers points gained
>for each fixture participated in ? Or should I be looking to have
>a separate field which is the calculated total for each manager ?
>
>Thanks in advance for any advice.
>
>Cheers
>Neil




--
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: Best method to keep totals

--0015175cb71c8147c6048f394212
Content-Type: text/plain; charset=ISO-8859-1

Thanks for your quick response. So like I thought, I'll just calculate the
totals on the fly and like you mentioned the manager is going to participate
in 50 games per season, with a season being twice per year.

Thanks for the help.

Neil

On Wed, Sep 1, 2010 at 10:06 PM, Jerry Schwartz wrote:

> IMNSHO, never store dynamic data in a field unless you absolutely have to.
>
> There are going to be exceptions, but unless a manager is going to
> participate
> in hundreds of thousands of fixtures I don't think this is one of them.
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
> E-mail: jerry@gii.co.jp
> Web site: www.the-infoshop.com
>
> >-----Original Message-----
> >From: Tompkins Neil [mailto:neil.tompkins@googlemail.com]
> >Sent: Wednesday, September 01, 2010 4:29 PM
> >To: [MySQL]
> >Subject: Best method to keep totals
> >
> >Hi,
> >
> >I'm developing a system whereby a manager gets assigned points based a
> >certain number of factors which are saved alongside a football result.
> > However, I also want to keep a total for each manager. My question is it
> >best to just have a query that uses SUM to total the managers points
> gained
> >for each fixture participated in ? Or should I be looking to have
> >a separate field which is the calculated total for each manager ?
> >
> >Thanks in advance for any advice.
> >
> >Cheers
> >Neil
>
>
>
>

--0015175cb71c8147c6048f394212--

RE: Best method to keep totals

> From: "Jerry Schwartz"
>=20
> IMNSHO, never store dynamic data in a field unless you absolutely have =
to.

I agree, and yet, it's so darned handy if it's a calculation you need =
quite often.

In FileMaker Pro (hold the "boos," please :-) you can have calculated =
fields -- a "pseudo field" that holds references to other fields in an =
equation.

To do something similar in MySQL, I've used views (which is somewhat =
clumsy), but is there a better way? Or is this necessarily part of =
domain knowledge programming in your interface language?

----------------

:::: Jan Steinman, EcoReality Co-op ::::


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

RE: Best method to keep totals

>-----Original Message-----
>From: Jan Steinman [mailto:Jan@Bytesmiths.com]
>Sent: Thursday, September 02, 2010 1:52 PM
>To: mysql@lists.mysql.com
>Subject: RE: Best method to keep totals
>
>> From: "Jerry Schwartz"
>>
>> IMNSHO, never store dynamic data in a field unless you absolutely have to.
>
>I agree, and yet, it's so darned handy if it's a calculation you need quite
>often.
>
[JS] You run the risk of introducing inconsistencies.

>In FileMaker Pro (hold the "boos," please :-) you can have calculated
>fields --
>a "pseudo field" that holds references to other fields in an equation.
>
[JS] I've used similar products. I suspect that the calculations are done on
demand just as they would be with MySQL.

>To do something similar in MySQL, I've used views (which is somewhat clumsy),
>but is there a better way? Or is this necessarily part of domain knowledge
>programming in your interface language?
>
[JS] Wouldn't the appropriate place for the calculation be in whatever queries
retrieve the data, or in a store procedure that they share? That's basically
the same as a calculated field.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com



>----------------
>
>:::: Jan Steinman, EcoReality Co-op ::::
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp





--
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: Best method to keep totals

On 2 Sep 10, at 13:58, Jerry Schwartz wrote:

>> From: Jan Steinman [mailto:Jan@Bytesmiths.com]
>>=20
>>> From: "Jerry Schwartz"
>>>=20
>>> IMNSHO, never store dynamic data in a field unless you absolutely =
have to.

....

>> To do something similar in MySQL, I've used views (which is somewhat =
clumsy),
>> but is there a better way?
>>=20
> [JS] Wouldn't the appropriate place for the calculation be...=20
> in a store procedure that they share? That's basically=20
> the same as a calculated field.

I've never played with stored procedures. Can you point me to a =
tutorial? Or should I just Google for it?

----------------

:::: Jan Steinman, EcoReality Co-op ::::


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

Re: Best method to keep totals

On 01/09/2010 10:29 p, Tompkins Neil wrote:
> Hi,
>
> I'm developing a system whereby a manager gets assigned points based a
> certain number of factors which are saved alongside a football result.
> However, I also want to keep a total for each manager. My question is it
> best to just have a query that uses SUM to total the managers points gained
> for each fixture participated in ? Or should I be looking to have
> a separate field which is the calculated total for each manager ?
>
> Thanks in advance for any advice.
>
> Cheers
> Neil
>
Neil, its generally not a good idea to store totals. I also had that
problem when i thought that as data grows it will slow down the server
to a halt. We have a mobile financial system with a table called trns
that stores all the credits and debits per account. each time a customer
wants his balance we sum up all the debits and credits (debits being
negative) and reply with the answer. We have close to 1 million million
people and each person does around 2 transactions per month, so the trn
table grows 2 million records every month we are in our 3rd month. and
select sum(t_amount) from trn where t_acno = xxxx works like a charm! of
course t_acno is indexed and the server is optimized.

In addition we sum before a debit to see if the balance is suffecient,
before a transfer, blah blah blah ...

So it's all good.

--
Jangita | +256 76 91 8383 | Y! & MSN: jangita@yahoo.com
Skype: jangita | GTalk: jangita.nyagudi@gmail.com

--
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: Best method to keep totals

--0016e6d6441c389b46048f5ca5de
Content-Type: text/plain; charset=ISO-8859-1

While I agree with the general take on this subject ("Never store totals
without a good reason" and "where there is duplication there is the
opportunity for skew"), I must say that there are exceptions. A couple of
years ago I worked on an inherited database in which the operant principle
was "sum don't store"; the problem was that many of the rows summed dated
back a year or two or more, and as an accountant friend of mine loved to
say, "A paid transaction is history; an unpaid transaction is fiction." In
other words, repeatedly summing amounts dating from last year or the year(s)
before is a waste of time and energy. An approach much superior in
performance is to store History in one summary table and Current in the
actual transaction table. Then all no sums or other calculations are
required for the History portion of the final calculation or presentation or
whatever it is. You grab and sum this (fiscal) year's rows, and then look up
the numbers for previous year(s). When you're dealing with say a million
rows per year, this change can dramatically improve performance.

Just my $0.02".

Arthur

--0016e6d6441c389b46048f5ca5de--

Re: Best method to keep totals

On 03/09/2010 16:32, Arthur Fuller wrote:
> While I agree with the general take on this subject ("Never store totals
> without a good reason" and "where there is duplication there is the
> opportunity for skew"), I must say that there are exceptions. A couple of
> years ago I worked on an inherited database in which the operant principle
> was "sum don't store"; the problem was that many of the rows summed dated
> back a year or two or more, and as an accountant friend of mine loved to
> say, "A paid transaction is history; an unpaid transaction is fiction."

The other exception is also where financial data is being stored. If you
have, say, a database containing sales order records, then as well as
storing the individual values of each item in each order, you also need
to store the total value of the order, the total price charged to the
customer and the total paid by the customer. These three should, of
course, be not only identical to each other but also to the sum of the
individual items, so there is not only duplication but the potential for
skew. But that, of course, is precisely *why* you store them, as any
discrepancy indicates an error which needs to be investigated.

Mark
--
http://mark.goodge.co.uk

--
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: Best method to keep totals

--00032555694ae03b6b048f7072dc
Content-Type: text/plain; charset=ISO-8859-1

100% agreed.

Arthur

The other exception is also where financial data is being stored. If you
> have, say, a database containing sales order records, then as well as
> storing the individual values of each item in each order, you also need to
> store the total value of the order, the total price charged to the customer
> and the total paid by the customer. These three should, of course, be not
> only identical to each other but also to the sum of the individual items, so
> there is not only duplication but the potential for skew. But that, of
> course, is precisely *why* you store them, as any discrepancy indicates an
> error which needs to be investigated.
>
> Mark
>

--00032555694ae03b6b048f7072dc--

Re: Best method to keep totals

Thanks for all the useful information. I'm going to ensure the
relevant fields are indexed and our db is optimised.



On 4 Sep 2010, at 16:10, Arthur Fuller wrote:

> 100% agreed.
>
> Arthur
>
> The other exception is also where financial data is being stored. If
> you
>> have, say, a database containing sales order records, then as well as
>> storing the individual values of each item in each order, you also
>> need to
>> store the total value of the order, the total price charged to the
>> customer
>> and the total paid by the customer. These three should, of course,
>> be not
>> only identical to each other but also to the sum of the individual
>> items, so
>> there is not only duplication but the potential for skew. But that,
>> of
>> course, is precisely *why* you store them, as any discrepancy
>> indicates an
>> error which needs to be investigated.
>>
>> Mark
>>

--
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: Best method to keep totals

--0015175cdbfc1766c6048f93a73c
Content-Type: text/plain; charset=ISO-8859-1

Just another quick question - following my initial question regarding the
best method to keep totals, I wondered if I should adopt a table to record
team league standings like based on each result like

team_id
home_win
home_draw
home_loss
home_goals
home_conceded
away_win
away_draw
away_loss
away_goals
away_conceded

Or should I try and display the league standings, based on the
fixtures/results table which contains fields like

match_id
seasons_id
week_number
home_team_id
away_team_id
home_goals
away_goals

Cheers
Neil


On Sun, Sep 5, 2010 at 7:32 AM, Neil Tompkins
wrote:

> Thanks for all the useful information. I'm going to ensure the relevant
> fields are indexed and our db is optimised.
>
>
>
>
> On 4 Sep 2010, at 16:10, Arthur Fuller wrote:
>
> 100% agreed.
>>
>> Arthur
>>
>> The other exception is also where financial data is being stored. If you
>>
>>> have, say, a database containing sales order records, then as well as
>>> storing the individual values of each item in each order, you also need
>>> to
>>> store the total value of the order, the total price charged to the
>>> customer
>>> and the total paid by the customer. These three should, of course, be not
>>> only identical to each other but also to the sum of the individual items,
>>> so
>>> there is not only duplication but the potential for skew. But that, of
>>> course, is precisely *why* you store them, as any discrepancy indicates
>>> an
>>> error which needs to be investigated.
>>>
>>> Mark
>>>
>>>

--0015175cdbfc1766c6048f93a73c--