Re: Database Question

# ashley@pcraft.com / 2006-12-20 00:01:29 -0700: > I'm starting to log weather data to a database and I'm trying to > figure out what's the best way to create the tables. The reports are > coming in every minute, of every hour, 24 hours a day. Eventually, I'd > like to do some calculations on the statistics, displaying daily values > (which can be broken down to hourly), but then also daily and monthly > averages. > > To me, it doesn't make sense to dump everything into one big table, > but I can't figure out what's the best way to break it down either. Why doesn't it make sense? (That is a honest question: breaking the table makes no sense to me, so I'm really curious about your conclusions.) > Keep in mind that the only data I have, is what comes in for that > minute. The daily averages I have to calculate myself (later.) But I > can't see one large table being very effective when it comes to > calculating that stuff. Use materialized views. I use triggers and/or rules (in PostgreSQL) in situations like the one you describe. > So, how should I break the tables down? Create a new table every > day (20061219_data, 20061220_data, etc.) and insert all the values in > it? Or, break it down per values (temp_table, humidity_table, etc.) and > insert daily data in them? Imagine you have the data broken into monthly tables. I want to see average values from 2005-11-13 till 2006-02-16, what will you do? -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991