Re: Searching 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 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