Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

advice needed on SQL table structure



2:49 pm on Jul 30, 2008 (gmt 0)

5+ Year Member

We are getting price data every minute from Monday at 00:00 to Friday 24:00 UTC (universal time code). The guy who set up the database has set it with the naming convention:


And it has a row for every minute in a certain day, containing the data. So each UTC day has roughly 1440 rows, and is in a separate table.

Is this a good scalable structure for storing the data?



3:28 pm on Jul 30, 2008 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member

Ed, absolutely not.

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..

Something like this

TABLE: Price_Data_Track
pdt_id (int, pKey) date_time(timestamp) item_id (int) price (float)
1 2008-07-24 01:18:47.973713 0987 89.76
2 2008-07-24 01:19:47.973713 0987 89.99
3 2008-07-24 01:20:47.973713 0987 90.01
4 2008-07-24 01:21:47.973713 0987 88.76
5 2008-07-24 01:18:47.973713 3456 67.76
6 2008-07-24 01:19:47.973713 3456 67.99
7 2008-07-24 01:20:47.973713 3456 68.01
8 2008-07-24 01:21:47.973713 3456 66.76

TABLE: Items
item_id (int, pKey) item_name item_desc
0987 ¦ widgets ¦ things to plug into places
3456 ¦ doohickee ¦ a whatcha-ma-call-it


4:09 pm on Jul 30, 2008 (gmt 0)

5+ Year Member

I think I need to do some reading. To me this seems counter-intuitive.

Having different tables for different dates did seem wrong to me since the data needs to be used for many timezones and this means making more queries as the data needed will span more than one table.

Would I not have different tables for different products at least (there will only ever be less than 10)?


4:17 pm on Jul 30, 2008 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member

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.


9:56 am on Aug 29, 2008 (gmt 0)

5+ Year Member

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?

Featured Threads

Hot Threads This Week

Hot Threads This Month