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 j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
