On Tue, Nov 16, 2010 at 12:18:35PM +0100, Arjen van der Meijden wrote: > On 16-11-2010 11:50, Louis-David Mitterrand wrote: > >I have to collect lots of prices from web sites and keep track of their > >changes. What is the best option? > > > >1) one 'price' row per price change: > > > > create table price ( > > id_price primary key, > > id_product integer references product, > > price integer > > ); > > > >2) a single 'price' row containing all the changes: > > > > create table price ( > > id_price primary key, > > id_product integer references product, > > price integer[] -- prices are 'pushed' on this array as they change > > ); > > > >Which is bound to give the best performance, knowing I will often need > >to access the latest and next-to-latest prices? > > If you mostly need the last few prices, I'd definitaly go with the > first aproach, its much cleaner. Besides, you can store a date/time > per price, so you know when it changed. With the array-approach > that's a bit harder to do. > > If you're concerned with performance, introduce some form of a > materialized view for the most recent price of a product. Or reverse > the entire process and make a "current price"-table and a "price > history"-table. That's exactly my current 'modus operandi'. So it's nice to have confirmation that I'm not using the worst schema out there :) -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
