advice needed on SQL table structure

Forum Moderators: open

Message Too Old, No Replies

advice needed on SQL table structure

edzillion

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?

Demaestro

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

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

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

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?