Re: Database Question

Ashley M. Kirchner wrote: > > Someone's going to tell me to go buy a book, I just know it. I'll go buy a book? (can't argue with you intuition now can I ;-) > ask anyway: > > 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. > 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. > > 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? I would keep everything in a single table (which will allow easier calculations - using a date range based where clause and whatever averaging SQL functions you desire) 60 * 24 * 365 = 525600 so in one year you have about half a million records in any given year, you might consider create a new table once a decade if you feel like being conservative with regard to potential table sizes. I guess that your using MySQL? I'm quite sure it can handle the amount of data you are going to have, in a single table. > > -- A >