Different price per dealer

[calpolymis]

Hi. I have a database based on the structure of Northwind sample database, that is one price per product in the product table.

Recently the requirement has been changed, and I need to have multiple prices per product for different dealers. For example, product A sells for $10 to dealer A, but $15 to dealer B, etc.. Right now I have 4 different dealers, and the number might grow in the future. Again, it is based on the Northwind sample database, so the relationship looks like:
Customers(1)-(Many)Orders(1)<-(Many)OrderDetails(Many)->(1)Products

What could be the best structure to handle this situation? Thank you for your advice.

 

[MajP]

you need to pull the price field out of your product table and make a new table.

tblProduct_CustomerPrices
productID
customerID
customerPrice

If you link to this table by both productID and customerID it will return the product price for that customer.

 

[MajP]

replace the word customer with dealer to avoid confusion.

 

[calpolymis]

Hello, MajP. Thanks for your reply. I'll try that and report back.