I'm writing a metallurgical web application, that must store data (electrical parameters) recorded at different frequencies. For example:
- six parameters recorded with frequency of one measure per second,
- three parameters recorded with frequency of ten measures per second.
(all parameters are decimals with six digits following the decimal point --
How should I design database structure to store such values efficiently? And to be able to query this kind of datasets easily for presenting them on any kind of graph?
Possibilities (ideas?), I came so far with:
Store both parameters sets in two different tables, with one record per corresponding time unit, store all parameters in columns declared as above (decimals), and query one record from first table and ten records from second table per each point on drawn graph. That would make 11 records per second.
Store all parameters in one table, with one record per one second, store each parameter from first group in decimal columns and each parameter from second group as an array of decimals, and query one record from this table only per each point of drawn graph. That would make 1 record per second.
Something different (if both above are wrong).
I have no idea which solution would be better in terms of better performance and lesser data redundancy.
First solutions seems to consume much more database space, because you have eleven records per each second of measures (and we can deal even with thousands of devices getting their measures each second), but in the same time seems to be faster on parsing, because you have direct access to each value in each record and in each column. While, in second case, you should get much lesser database size (cause we have one record per each second and each device) but parsing this data seems slower, because you have to parse array of ten values per each second and each parameter measured with 1/100 ms freq.
If my (above) assumtions are correct, then this question narrows to answering, what is more important in today database design -- database size or parsing time? For low-cost solutions (like shared hosting or low-hardware VPSes) I'd assume, that database size matters far more and therefore I'd go into second option.
I'm mainly focused on using MySQL, but I can accept nearly any other kind of database, if there would be any advantage of using different RDBMS.
There is no need (at least in my case) to store in DB microtime in case of second group of paramteres (i.e. those, that are recorded with 1/100 ms frequency). They only need to be correctly linked to that "second" (timestamp) under which other parameters (recorded with 1/1 s frequency) are stored.
There is no need to store each parameter in a separate record in database. As my ideas shows, all parameteres can be stored in one record per each measure.