homepage Welcome to WebmasterWorld Guest from 54.227.160.102
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
advice needed on SQL table structure
edzillion

5+ Year Member



 
Msg#: 3711207 posted 2:49 pm on Jul 30, 2008 (gmt 0)

Hi
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:

symbol_DDMMYYYY

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?

ed

 

Demaestro

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



 
Msg#: 3711207 posted 3:28 pm on Jul 30, 2008 (gmt 0)

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

edzillion

5+ Year Member



 
Msg#: 3711207 posted 4:09 pm on Jul 30, 2008 (gmt 0)

Hmmmm
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)?

Demaestro

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



 
Msg#: 3711207 posted 4:17 pm on Jul 30, 2008 (gmt 0)

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.

edzillion

5+ Year Member



 
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?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved