I'm finding that it's not quite as simple as I had imagined to maintain a table whose values are calculated by analyzing other tables. The source tables contain time series data, which can is updated several times a day. To calculate totals by day, week, month, etc., I don't want to have to start over from scratch, so I want to keep track of which records in the source tables have already been analyzed. Further complicating things, there are several analyses that take place and I may add others later. I don't always want to have to do all of them at the same time.
So, at the moment, I've added some Boolean enum columns to the source data tables, showing which analyses have been done. Do the analysis, set the Booleans to true. I'm not sure I really like that approach, since it can mean adding columns to rather large tables at times. I suppose those fields could be in an entirely separate table, joined by the primary key, but I don't like having to depart from normal form that way. I also considered having a table that keeps track of what analysis has been done, using a timestamp or auto-increment column, but realized that would get messed up if I don't process data in the order it arrived. I could keep track of a range, which would let me find earlier stuff that hadn't been done. Just to make it all a bit more complicated, the initial analysis is done in a temporary table, for huge performance gain, then inserted into its final resting spot. Any suggestions on how others have solved this sort of problem? Nick P.S. sql,query (to satisfy that danged filter) -- [EMAIL PROTECTED] (408) 904-7198 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
