I think I know the answer to this, but suggestions welcomed.
I have to store lots of information. An electricity reading every half
hour for hundreds of meters for several years.
The ways I thought of storing this in SQL were:
1. One table row per day with 48 fields for the readings.
144M table, 3M index
saves space and having a long skinny table
however, getting the min/max reading in a day is a pain. I
think I'll have to store it which goes against the grain
2. One table row per reading.
376M table, 231M index
big time long skinny table with more control information than
content, huge waste of disk space. However it is first normal
form so queries are easier.
3. Two tables, one to hold control information and one for the readings.
added complications because of the join and creation of an
internal link field.
A quick trial proved 1 to be cumbersome but space saving. I'm currently
using option 2.
any suggestions or recommenndations?