Re: Re: php-db Digest 1 Feb 2002 07:48:33 -0000 Issue 1027

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