Thread: Searing array fields - or should I redesign?
Hello,
I have a process that is logging data from vehicles. I'm looking for thoughts on the pros and cons of different approaches to storing this data - and retrieving it. Different vehicles report different types of data. The current process stores the data in a multi-dimensional array.
eg, insert into logtable values ( 'vehicle123', now(), {{'voltage','13'},{'rpm','600'}};
However, I am not sure how I can write a query - for example to read all records where the voltage field is less than 13. Performance in this case is not a real significant issue.
Would I be better off redesigning and having a master / detail kind of structure? Where the master table would have the vehicle id, timestamp and a key to the detail table. The detail table would then have the key, the type of measurement and then the value.
I guess a third approach would be to just have the detail table with duplication on the vehicle id and time - for each data type recorded.
Thanks,
Bryan.
I have a process that is logging data from vehicles. I'm looking for thoughts on the pros and cons of different approaches to storing this data - and retrieving it. Different vehicles report different types of data. The current process stores the data in a multi-dimensional array.
eg, insert into logtable values ( 'vehicle123', now(), {{'voltage','13'},{'rpm','600'}};
However, I am not sure how I can write a query - for example to read all records where the voltage field is less than 13. Performance in this case is not a real significant issue.
Would I be better off redesigning and having a master / detail kind of structure? Where the master table would have the vehicle id, timestamp and a key to the detail table. The detail table would then have the key, the type of measurement and then the value.
I guess a third approach would be to just have the detail table with duplication on the vehicle id and time - for each data type recorded.
Thanks,
Bryan.
> eg, insert into logtable values ( 'vehicle123', now(), {{'voltage','13'},{'rpm','600'}}; > > However, I am not sure how I can write a query - for example to read all records where the voltage field is less than 13.Performance in this case is not a real significant issue. > > Would I be better off redesigning and having a master / detail kind of structure? Where the master table would have thevehicle id, timestamp and a key to the detail table. The second approach would work quite well. table logentry id primary unique vehicleid int logtime timestamp table logdetail logid int attribute varchar/int value decimal textvalue varchar You can retrieve logentries for specific vehicles, timeframes and attributes - and you can extend more log attributes withoutchanging the database structure. I would suggest another table for the attributes where you can lookup if it is atext or numeric entry. Just my two cents - and performance always matters (later in progress) ;-)
Le mercredi 15 décembre 2010 à 19:12 +0100, Jan Kesten a écrit : > > eg, insert into logtable values ( 'vehicle123', now(), {{'voltage','13'},{'rpm','600'}}; > > > > However, I am not sure how I can write a query - for example to read all records where the voltage field is less than13. Performance in this case is not a real significant issue. > > > > Would I be better off redesigning and having a master / detail kind of structure? Where the master table would have thevehicle id, timestamp and a key to the detail table. > > The second approach would work quite well. > > table logentry > id primary unique > vehicleid int > logtime timestamp > > table logdetail > logid int > attribute varchar/int > value decimal > textvalue varchar > > You can retrieve logentries for specific vehicles, timeframes and attributes - and you can extend more log attributes withoutchanging the database structure. I would suggest another table for the attributes where you can lookup if it is atext or numeric entry. .. The problem with this approach is that you need to loop through your recordset in your code to collect all the values. If you only have one value per key to store per vehicule, it's much easier to have one big table with all the right columns, thus having just one line to process with all the information . So, from your example : create table logtable( id_vehicle text, date_purchased date, voltage integer, rpm integer); the corresponding record being vehicle123, now(), 13, 600 this will simplify your queries/code _a lot_. You can keep subclasses for details that have more than one value. Adding a column if you have to store new attributes is not a big problem. -- Vincent Veyron http://marica.fr/ Progiciel de gestion des dossiers de contentieux et d'assurance pour le service juridique
On Dec 16, 2010, at 11:26 AM, Vincent Veyron wrote: >> table logdetail >> logid int >> attribute varchar/int >> value decimal >> textvalue varchar >> >> You can retrieve logentries for specific vehicles, timeframes and attributes - and you can extend more log attributeswithout changing the database structure. I would suggest another table for the attributes where you can lookupif it is a text or numeric entry. > .. > > The problem with this approach is that you need to loop through your > recordset in your code to collect all the values. > If you only have one value per key to store per vehicule, it's much > easier to have one big table with all the right columns, thus having > just one line to process with all the information . So, from your > example : > > create table logtable( > id_vehicle text, > date_purchased date, > voltage integer, > rpm integer); > > the corresponding record being > vehicle123, now(), 13, 600 > > this will simplify your queries/code _a lot_. You can keep subclasses > for details that have more than one value. Adding a column if you have > to store new attributes is not a big problem. Plus, that logdetail table will have a per-row overhead of 24+4 (or 8)+4 (or 8)+1 bytes, assuming attribute is stored asan int (which you'd want). That's a minimum of 33 bytes per attribute, and you don't even have payload yet. Entity-attribute-value (what logdetail is) is extremely expensive. You want to avoid it at all costs unless you have a reallytrivial amount of data. -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net