Re: Searing array fields - or should I redesign?

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