On 1/31/02 11:48 PM, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> From: "DL Neil" <[EMAIL PROTECTED]> > Reply-To: "DL Neil" <[EMAIL PROTECTED]> > Date: Fri, 1 Feb 2002 16:28:16 -0000 > To: "Frank Flynn" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>, "Garry Optland" > <[EMAIL PROTECTED]> > Subject: Re: [PHP-DB] Re: Date operations. > > 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. > > Your thoughts? > =dn > 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. 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). 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). 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... Frank -- 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]
