Re: Searing array fields - or should I redesign?

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