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]

Reply via email to