Thanks for the comments. Just to clarify, I gave these two values as examples. The readings could be between a handful for one vehicle type up to 40 or more for another type of vehicle.
On Thu, Dec 16, 2010 at 12:26 PM, Vincent Veyron <vv.li...@wanadoo.fr>wrote: > 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 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 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 without changing the > database structure. I would suggest another table for the attributes where > you can lookup if 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. > > -- > Vincent Veyron > http://marica.fr/ > Progiciel de gestion des dossiers de contentieux et d'assurance pour le > service juridique > >