By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

How to store annual hourly data in a database

P: 3
I'm not that experienced with database design and I cannot figure out the best way to design this. I have annual hourly data (8760 values) for a million sites. The total size of the data is not a problem, but how would one store the data such that I can manipulate annual sets of data?

I often read that designing tables with 100s of columns is a mistake. Clearly, I cannot store the data as I would in a program, which is in an array with 8760 columns. So, would monthly tables work (744 columns) or tables with one week of data (168 columns)? Or perhaps 365 tables with a single day of data per record?

Any advice is appreciated.
Feb 19 '11 #1
Share this Question
Share on Google+
6 Replies

P: 27
did you mean columns or rows? I see no reason not to store 1 table with millions of rows.
Feb 19 '11 #2

P: 3
To clarify, I have a million sets of data, and each set contains 8760 values. This is hourly data for an entire year for 1 million sites.

The question is, how to efficiently store the data in a way that allows me to quickly "view" the data for particular set of sites? My application needs to load all the hourly data for a subset of rows. The simplest thing to do would be to store the data in a table with 8760 columns, one for every hour of the year. Apparently, this is not only bad practice, it's not even possible in most database architectures.

So, I could store the data in 365 tables, one for each day of the year and each with 24 columns (plus a column for the ID of course). There would still be 1 million rows in each table. To load the data for one site, however, I would have to query 365 tables. That seems painful. Is there a standard way of dealing with data of these dimensions?
Feb 19 '11 #3

P: 27
I'm posting from my phone so ill help more later but ill just say there is no reason to have separate tables for each day just have a field in the row for the day and sort them out after.
Feb 19 '11 #4

Expert Mod 10K+
P: 12,347
To elaborate on what NetDynamic said, you only need one table with 3 fields. Site, DateTimeField, and ValueField.
Feb 19 '11 #5

P: 3
Ahhhh ... I hadn't thought of that solution. So, instead of a wide table with a million rows, I'd have a narrow table with 8.7 billion rows. I have to admit, the idea of "billions and billions" of rows (with a nod to Carl Sagan) sort of scares me, but that's probably my problem, not postgre's problem.

Since the current Site ID is a long character string, I imagine a Site ID table should be created with an auto-increment index. The index would be used in the large data table.

Hmmm, makes sense. Thanks for the clue.
Feb 19 '11 #6

P: 27
If you have a ton of tiny data sets just use a delimiter and use only 1 column for each set, then in your script just explode it into an array or something and use as you wish.
Feb 20 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.