Frank,
As yet no response from Garry, but I wanted to ask:
You already have DateTable set up (presumably), so extending its application
into a relationship with the
breeding table is no big deal. Given that one suggestion was that Garry could
consider storing monthly
statistics in another table (from breeding), and thus saves processing each
time/only needs to update once per
month and presumably only for the month just passed; wouldn't it make sense
FOR HIM to combine the two
functions? Initially create the MonthlyStats table to contain only year and
month data (which can be related to
'drive' date-oriented SELECTing), and then update/populate each month's row as
and when the stats become
available. It might also offer opportunities to simplify the processing
outlined below.
Creating the monthly statistics and then storing them back in the DB is not
my favorite way to solve the problem. It violates one of the fundamental
rules of good relational database design - that Data is stored in one place
only.
=As it happens I do agree with you, however I'm playing 'devil's advocate' (a tactic
consultants often use to
ensure that people understand the whys-and-wherefores of what's being said).
=In the case of statistical databases the rules of normalisation/good db design are
often varied. Even in
relational applications the argument for de-normalisation frequently comes out of the
usage pattern - it is more
efficient in terms of response time, to 'cache' the answer to an FAQ (in another tbl)
than to repeatedly
calculate it.
=The 'mathematical' argument is to ask if the aggregation/statistics are in fact
multiple instances of the same
data/representation - I'm not even going to start on that one...
For example: if you created the January statistics on February 1 and on
February 2 someone updates the DB with a record that affected January, then
your monthly total is wrong. (I don't know about the rest of you but
occasionally I do get behind).
=it depends how these things are treated. In the case of breeding dogs etc, I imagine
that you're right. However
if there is the concept of a 'cut-off date', eg get your expenses claim in by x-day if
you want them paid during
this accounting month. Then there is a clear distinction between the months that has
little to do with when the
expenses were actually incurred!
=In the case of the breeders, if there was a monthly magazine, then statistics
calculations will have to be
completed in advance of the publication date - regardless of how late some of the data
was to arrive.
=How the data is treated when it arrives late, will vary by application/implication.
The average company
expenses system will put a claim from last month (or even before) through as if it was
a claim for 'this' month
and not worry too much. I've watch sales people play some pretty serious
tactical-games with their signing-up
deals to gain credit within a particular month/quarter...
To fix this you have to check every time someone updates the 'breeding'
table (in this example) and if the dates are in the past you'll have to
recalculate the monthly total (UGLY!!).
So we'd like to calculate the daily totals and monthly averages when called
for unless there is some reason we can't (poor performance perhaps).
=indeed - and whilst we don't know the volumes, the 'demand' does not look
significant, even at ten or one
hundred times the volume stated below!
A DateTable with one record for each day is under 4,000 records for 10 years
of days, since you will never update or delete from it and rarely add to it
you can index the heck out of it. SQL is build for just this kind of
problem.
Just my opinion...
=thanks.
=dn
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
