homepage Welcome to WebmasterWorld Guest from 54.198.148.191
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
advice needed on SQL table structure
edzillion




msg:3711209
 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




msg:3711247
 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




msg:3711299
 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




msg:3711308
 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




msg:3734218
 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.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved