I have a problem deciding how to store this data in my database. Any suggestions on the best way to do it? I don't know a hell of a lot about databases, I might add.

I have data coming in formatted like so, but rather than 4, the number of columns is approx 240, so each date has 240 unique values associated with it:

Date/Time 200,00 202,50 205,00  
2010.11.12  13:34:00  45,8214 43,8512  41,5369   
2010.11.12  13:35:00  461,9364  454,2612  435,5222 

Also, rows are associated with DataSites.

My first thought was to have a table like so: DataID (pk), DataSiteID, ParameterID, Date, Value, with an index on DataSite, Parameter and Date. The ParameterID refers to another table that stores the input column headers (200,00 202,50 205,00 ...).

My second thought was simply to have a table with all 240-odd columns. I have come up with a few other ways, but they are pretty unsatisfactory as well.

The problem I have with my first solution (not such a huge problem, but I don't like it), is that the Date and DataSiteID are going to be repeated for all 240 values in that input row, so it uses quite a bit of extra space.

There will be about 40gb of data a year coming in (in the above text format), and the data will be searched by DataSite, Parameter and Date. The amount of data coming in will most likely quadruple in a year or so.

Any good ideas? Thanks, James

edit: This is time series data, with the columns being measurements at different wavelengths. Data will want to be analysed within a relatively narrow range of wavelengths. There could also be extra wavelengths added in at some point in the future.

edit: Thanks for the answers guys, I really appreciate it :) I think I can probably find time to run some experiments with 500gb or so of test data. I'll post back with any conclusions ;)

  • 2
    Im guessing from the naming of the columns that this is some sort of observational time series data. If this is science data, I'd look to see if the science discipline has typical ways of organizing their data, or at the very least, what the science use cases are that make use of the data. – Joe Jan 24 '11 at 3:37
  • It is indeed time series data :) original post edited with a bit more info. – James Jan 24 '11 at 19:50

You could make a case either way, but if the data is going to be used for analysis and you often want to see multiple columns from that data at the same time, go with the wide table. Make sure you know your databases column quantity and row size limits. Make sure you get the datatypes right. If many of the columns are null, SQL Server allows you to optimize the table for that. You could also consider using a NOSQL (Not Only SQL) solution for analysis of this type of data.

If this data is going to be less for analysis, you might want to normalize it as stated in your question.


I had a very similar situation to yours, 257 fields with 30-50gb a year coming in. I ended up just keeping it simple, one long big boy table in SQL Server. My data was queried a fair bit but mainly on date and it worked well.

I could have broken the data down into logical smaller chucks (groups of 50 or so), but in this case there really wasn't much of an advantage to it so I saved myself the bother.

If I were feeling fancy now I might consider a NoSQL option which is a better fit in theory, but with mission critical data trying new things out isn't always great for the nerves.


So, to belatedly answer my own question (the project never went ahead in the end), when I managed to get some spare time I filled up a test table with 500gb of data with the table arranged as so:

My first thought was to have a table like so: DataID (pk), DataSiteID, ParameterID, Date, Value, with an index on DataSite, Parameter and Date. The ParameterID refers to another table that stores the input column headers (200,00 202,50 205,00 ...).

The database setup was the standard PostgreSQL install on an old dual core machine with 3gb of ram. I ran about a dozen different queries simply selecting data by DataSite Date and ParameterID, averaging data over a 1 hour time period, 1 day time period, and inserting new chunks of data. From memory, all queries took less than a second to execute. It was certainly much faster than I expected and quite useable. One thing that I hadn't thought about was that with the table indexed this way the index file was almost 500gb as well, so having a 240 column wide table instead would certainly save a lot of disk space.

  • But while saving space, it would have most assuredly affected the indexing speed. You might try again if you get the chance and go ahead and rotate it. – jcolebrand May 9 '11 at 1:26

In Postgres I would elegantly solve this with an array type or a varray in Oracle.

  • That would work, the only catch is that I would need to store the column headers for that DataSite somewhere, as without it the data doesn't mean anything, and they might vary/change (they aren't supposed to, but I've seen pigs fly before...) – James Jan 24 '11 at 20:05
  • In that case in my main data table I would have another column called "version", and another table mapping version to an array of column headings (so the array indexes match the data array). – Gaius Jan 24 '11 at 20:14

I don't know if it's useful for your problem, but for the columns I don't need to do direct requests on (cols that I never put in my WHERE condition), and which are only informative when I want all info about some specific rows, I combine them in a blog field JSON formatted.

  • Furthermore, compress that blob. Do the compression in the client, so that you are not adding a burden on the network and the server. – Rick James Apr 5 '12 at 20:05

I'd probably make the final decision of the design dependant of the distribution of the queried parameter_ids. That is, if there are a few parameter_ids that are queried almost exclusively, I'd put their values into a hot table and the remaining values into another cold table.

Otoh, if their query-distribution is more or less even, I'd load a sample set worth a few days into a table where one records keeps all values in order to see what the ratio is between records/db-blocks (or if there is even a row chaining problem, which is likely). Depending on that I would then do a further design decision.

Well, after reading it, I'd probably do both approaches for a desicion in parallel.


I was re-reading the question -- if I have this correct, then in each record you get as input, there are different values being tracked (based on the ParameterID):

The ParameterID refers to another table that stores the input column headers (200,00 202,50 205,00 ...).

... I don't know enough about how you're interacting with the data, but I'd be inclined to go with another option -- have a separate table for each parameter ID, and then if necessary have a view that would join the various different parameters by date and location into the wider (240 column) table; if it was important to keep the DataID accessible in the view, then you could use a UNION rather than a JOIN, but the columns will be sparsely populated.

  • By parameter I mean the column header, or wavelength. I had thought of doing it this way, but having 240 tables feels a bit clunky :) – James Jan 24 '11 at 19:56
  • @James ... it shouldn't be 240 tables ... only as many as the unique ParameterIDs. The view would then be as wide as the number of discrete wavelengths you have measurements at (plus the independant variables). ... You might want to look at how the OPeNDAP community handles things, as they're geared towards time series data. Most of the data I deal with are images (telescope, coronograph, magnetograph), so their stuff doesn't fit my work, so I don't know how they handle storage. (it might just be HDF/CDF/NetCDF/ASCII tables). – Joe Jan 24 '11 at 20:12
  • Unfortunately there are 240-ish unique parameters :( Thanks for the link :) – James Jan 24 '11 at 20:39
  • @James : also, is it irradiance data? If so, you might want to ask the folks at LISIRD ... I think they separate it into separate sets of data by experiment, and I don't know if they keep it in databases or just flat files. – Joe Jan 24 '11 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.