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 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(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net