Minimum price selection

Situation looks like this:

We have product 'A123' and we have to remember lowest price for it. Prices for one product comes from random number of shops and there is no way to tell when shop 开发者_开发知识库x will send us their price for 'A123'.

So I had SQL table with columns:

product_number
price
shop (from which shop this price comes)

An SQL function for updating product price looks like this (this is SQL pseudo-code, syntax doesn't matter):

function update_product(in_shop, in_product_number, in_price)
    select price, shop into productRow from products where product_number = in_product_number;
    if found then
        if (productRow.price > in_price) or (productRow.price < in_price and productRow.shop = in_shop) then
            update row with new price and new shop
        end if;
    else
       insert new product that we didn't have before
    end if;

the (productRow.price < in_price and productRow.shop = in_shop) condition is to prevent situation like this:

In products table we have

A123 22.5 amazon

then comes info from amazon again:

A123 25 amazon

Thanks to above condition we update price to higher which is correct behavior.

But algorithm fails in this situation: again we have a row in the products table:

A123 22.5 amazon

then comes info from merlin

A123 23 merlin (we don't update because price is higher)

then comes info from amazon

A123 35 amazon

and we update table and now we have:

A123 35 amazon

but this is wrong because merlin earlier has lower price for that product.

Any idea how to avoid this situation?

The only way you are going to solve your problem is keep track of the price per shop and then only return the lowest current price. So for example you would need a table like the one you already have, but when you select out of the table something like:

select min(price)
  from products
  where product_number = :my_product

Personally if it were me, I would keep a time stamp of when you receive the product price updates so you would be able to ascertain when you got the update.

To make this work you should maintain a table that contains the following:

• Product
• Supplier
• LatestPrice

Then identify the current best supplier by querying that table - you can either do this when requested or when the table is updated either way you simplify the problem at the price of slightly more complex schema and queries

Additional (following comment):

Ok, this is going to mean that you need to store more data - but you don't have a huge amount of choice - the data is required to solve the problem so you either: a) have to update prices from all suppliers concurrently and then choose the best price from that snapshot or b) store the prices as you get them and pick the best price from the data you've got. The former implies a fairly hefty overhead in terms of fetching and processing data whereas the latter is basically a fairly modest storage problem and something any decent databases will cope with easily.

Basically, the problem is that you only store the lowest price from 1 vendor. You have to keep records of prices of all vendors, and use a selection query to select the minimum.

For example, If you have:

A123 22.5 Amazon

and you got:

A123 23 Merlin

You have to insert it, even if it is with higher price, because it's a different vendor. So you'll have:

A123 22.5 Amazon
A123 23 Merlin

When you get the new price from Amazon, for example: 25, you just update it. So you'll get:

A123 25 Amazon
A123 23 Merlin

then select the lowest price, Merlin, in this case.