On Sun, 18 Jan 2009 22:12:07 +0100
Ivan Sergio Borgonovo <mail@webthatworks.it> 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