Re: Searching array fields - or should I redesign?

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(dot)lists(at)wanadoo(dot)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
>
>