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.
Best regards, Arjen -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
