CODEWITHSUNDEEP
database-design
Enze
Enze

Reputation: 11

Database design issue, arbitrary price per customer

I'm working on a market website. Sellers and buyers could regist and make trade.

The difficulty I met, is that I need to allow sellers set different item price for each customer.

For example, buyer A sees the price for banana is 5 dollars, while buyer B has to pay 6 dollars to get the same goods.

I can choose any database I want. But how to make it happen?

Assume that I have 100,000 goods and 10,000 buyers.

Upvotes: 1

Views: 589

Answers (4)

HLGEM
HLGEM

Reputation: 96570

Alot of this is going to depend on your business rules and how you determine what the actual price the customer is quoted will be. SO the first order of business is to sort out your business rules.

Most of the time I have worked on somethign like this, ti worked roughly this way. There is a default price for each item. This along with the other information about the item is stored in the product table. Next customers are usually given discounts depending on some business rules or relationships. These discounts tend to be by vendor not by product. Most customers get the default values unless they meet the business rule. SO yo need a discount table that contains the vendorID and the customerid and the discount (Or markup up but usually the default prices is the highest one charged).

Next you need a table to store the prices quoted for a particular order. This table (the Price Quote table) is where you might make particular individual price adjustments as well as applying the default or discount adjustment for most items. Note you don't need to do this for every product-customer combination, only for those that are actually quoted to the customer.

Finally at some point you make the sale and the quote become an order. This data should be stored in a separate order table.

Upvotes: 2

TommCatt
TommCatt

Reputation: 5636

First, there is the definition of each item sold by each seller and the default price:

create table ItemPrices(
    SellerID      int not null,
    ItemID        int not null,
    DefaultPrice  currency not null,
    constraint PK_ItemPrices primary key( SellerID, ItemID ),
    constraint FK_ItemPrices_Seller foreign key( SellerID )
        references Sellers( ID ),
    constraint FK_ItemPrices_Item foreign key( ItemID )
        references Items( ID )
);

Then a relationship between buyer, seller and item.

create table PriceByBuyer(
    SellerID      int not null,
    BuyerID       int not null,
    ItemID        int not null,
    NewPrice      currency not null,
    constraint PK_PriceByBuyer primary key( SellerID, BuyerID, ItemID ),
    constraint FK_PriceByBuyer_Seller foreign key( SellerID )
        references Sellers( ID ),
    constraint FK_PriceByBuyer_Buyer foreign key( BuyerID )
        references Buyers( ID ),
);

To show the price of a certain item sold by a specific seller:

select  ip.SellerID, ip.ItemID, ip.DefaultPrice as Price
from    ItemPrices ip
where   ip.Seller = :ThisSeller
    and ip.Item   = :ThisItem;

But if there is an entry in PriceByBuyer for that item and seller for a particular buyer, then that is the price to be displayed rather than the default:

select  ip.SellerID, ip.ItemID, coalesce( pbb.NewPrice, ip.DefaultPrice ) as Price
from    ItemPrices ip
left join PriceByBuyer pbb
    on  pbb.SellerID = ip.SellerID
    and pbb.ItemID   = ip.ItemID
    and pbb.BuyerID  = :ThisBuyer
where   ip.Seller = :ThisSeller
    and ip.Item   = :ThisItem;

If there is no entry in PriceByBuyer for the specified item, seller and buyer, then pbb.NewPrice will be NULL and the coalesce will return the default price. If there is an entry, then pbb.NewPrice will not be NULL and that will be the price shown.

Upvotes: 1

PabTorre
PabTorre

Reputation: 3127

instead of setting an individual price for each buyer, set one price, and have each buyer have a "markup" that is added to the price when the buyer sees the price or makes a transaction... that way you don't have to keep track of 10,000 price streams for each good just 1 price and then handle the markup at the client side... you only need to provide the markup once for each buyer and then have the front end adjust the prices with that value. On the server side you just have a regular double auction market, so you don't have to reinvent the wheel.

Upvotes: 1

Enze
Enze

Reputation: 11

The first thing came to my mind is mysql. So I set up a price table, with "goods_id", "buyer_id", "price" as fields. The problem is that the table would be too large (100,000 * 10,000 records) and join operation could be slow.

If I set up a price table for each customer, and reduce the fields to "goods_id" and "price". Now I can read fast, but write is still problematical. When a seller modifies the price, all 10,000 tables needs to be updated. I could make the update of price asynchronously, however sellers sometimes want to rise the prices immediately.

If I only save the default price in mysql, which "price" is just a field of goods table. And I save all the special price requirements to some nosql, with a key consists of "goods_id" and "buyer_id". My price logic would be that try search the price in the nosql. if no match was found, I use the default price. However, by doing that, I lose the function of sorting items by price. Which pissed me off.

Is there a better way to do this?

Upvotes: 0

Related Questions