I have to store the energy consumption of a large number of meters with a 1 minute frequency. The data coming to me is as meter readings for every 1 minute and I have to convert that into consumption. I can either store the data in rows for each day with 1440 (60*24) rows for a single day OR I can store a single row for a day with 1440 columns. I have to report both graphical and tabular data for a year, month, week, day .... 5 minutes. What should be my approach to do so? Rows or columns. Regards
The best way is to store consuption in one column, and time in another column. 1440 columns it is not good idea... How would look that query? How you think to sum values for one week for example in that case? How you will store data for another day? Don't do that :) When you creating reports, you simply use one column for data, and another for timeline values in chart. You can sum values for hour, day, week, month, year... you can do all calculation if you choose two columns model.
On the data volume question, 262 million rows is not 'too much' for SQL to handle, although you could implement a strategy that stores aggregated values for each (or a selection) of the time periods. For example storing at '5 mins' and 'daily' will allow you to report on all those time periods. But then you may find that if 80% of the queries are at '60 mins' then aggregating to that may also make sense. You could also do the same for meter groupings too. Once you are sure that the base data (1 min) is either not going to ever change, or is not needed then you can archive (or delete depending on your requirements).