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 ;)