Searing array fields - or should I redesign?

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.

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 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

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 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.

Plus, that logdetail table will have a per-row overhead of 24+4 (or 8)+4 (or 8)+1 bytes, assuming attribute is stored as an 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 really trivial amount of data.
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net