There should be one main table not a table per day.
The table should have a time stamp field and then any data that relates to it either in a seperate field or in a lookup table using Ids depending on what you are storing. This way you can track many items in one table, index the id, yes the data will get huge.... that is what databases are for.... you can archive every month or year or whatever and empty the tables and start fresh if you have to..
If you look at my example I DO have a separate table for products, I called it Items though.
Do some reading and ask around because getting as much info as you can is always a good thing and learning about this won't hurt either, but I do this type of thing a lot and it may not be the best way but I am sure there isn't a much better way other then storing it in a flat file like a website log would be.
Msg#: 3711207 posted 9:56 am on Aug 29, 2008 (gmt 0)
Hi Just coming back to this after a while. I have set up the table structure how you proposed. I am now trying to populate a graph from the data, specifically a graph that shows data from 00:01 to 24:00 on a specific day. The only time field in each row is the unix timestamp - I could do a search to find the first timestamp after 00:00 on a certain day - but I am worried that this would be a significant performace hit. Would it make sense to add another field with a simplified date and time to make it quicker to select data based on time/date? Or would that be just as performace intensive?