Обсуждение: Searing array fields - or should I redesign?

Hello,
I have a process that is logging data from vehicles. I'm looking for thoughts on the pros and cons of different approaches to storing this data - and retrieving it. Different vehicles report different types of data. The current process stores the data in a multi-dimensional array.

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 detail table would then have the key, the type of measurement and then the value.

I guess a third approach would be to just have the detail table with duplication on the vehicle id and time - for each data type recorded.

Thanks,
Bryan.

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

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

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
attributeswithout changing the database structure. I would suggest another table for the attributes where you can
lookupif 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
asan 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
reallytrivial amount of data. 
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net