So you have two "single table" votes.. make this a third. I'm guessing that each time you collect data, it's going to be one of each piece of data every time.
Temperature, barametric pressure, humidity, wind direction, etc. You're not going to have 5 things all the time and like 3 other things only sometimes.
You'd want to split the data into separate tables if there was some data that was infrequently. Take a contact database for instance. You might have name, address, phone, birthday. But then maybe your company has forms that some people fill out. You wouldn't want all the data for a form that they may or may not fill out in the same table. You'd end up with a lot of empty spaces for the forms that some people never needed to fill out. So you'd put that data into a separate table and link them via a contact ID or something.
As for efficiency, it's probably more efficient to keep everything in one table and do your statistics by using SQL to filter down by date and use aggregate functions like SUM() and whatever your database's version of AVERAGE and other math functions are. This way, it's handled very quickly and efficiently inside the database engine before it returns any data (which is pretty much the slow part of database access.. especially when there's a lot of data to return. Data return and uber-complex joins.. but even they can be more efficient than returning too much data).
You could section your tables off by date if you want. 1/2 million records a year and you could get away with having a few years in one table, or keep it year to year. You gotta ask yourself though, if you're going to want statistics that cross multiple years or whatever boundary you set for your splitting. If so, you're really better off having it all in one table than trying to UNION the tables later (although that's viable too I guess.. it just gives me the willies.. bad experiences.. hah)
Just some additional thoughts on top of what's already been mentioned.
Oh yeah... buy a book and take it to the <insert database type> mailing list :) Good luck!
-TG
= = = Original message = = =
Someone's going to tell me to go buy a book, I just know it. I'll
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?
-- A
___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
