On Sun, 18 Jan 2009 22:12:07 +0100 Ivan Sergio Borgonovo <mail@xxxxxxxxxxxxxxx> wrote: > I've to apply a discounts to products. > > For each promotion I've a query that select a list of products and > should apply a discount. > > Queries may have intersections, in these intersections the highest > discount should be applied. > > Since queries may be slow I decided to proxy the discount this way: Actually: premature optimization is the root of all evil (Knuth). Although I haven't reached any definitive conclusion clean design and normalisation seem paid off. A normal query to retrieve a list of products seems nearly unaffected by keeping a create table Promo ( PromoID serial primary key, PromoStart timestamp, PromoEnd timestamp, ..); and a create table PromoItem( PromoID int references Promo (PromoID) on delete cascade, ItemID int references Product (ProductID) on delete cascade, Discount numeric(4,2) not null ); and looking for max discount in a join on the fly. That's on a 1M items and on 40K products on promo. Distribution of promo was random, I'll dig further to get an idea of worst case. What's important is that a simple search over the catalogue takes nearly the same time that a query that search through the catalogue and find the appropriate discount. Thanks to Knuth and to Postgresql coders. I'll post a more detailed solution as soon as it's enough refined and if I'm sure of its correctness. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
