Main pricing and support for custom client pricing (Database Architecture ideas help - Rails)

Question

Hi all,

I have a personal project I'm planning and I came to a small hurdle.

I want to have an item with price that will be the default for all clients/users. However, in my business I have some clients that are grandfathered in to some special pricing. In the case of these grandfathered in cases, I'll manually plug their special price in my admin section. Then all they will see is their special pricing while the regular users/clients see the default price.

What is the best and simplest way to design the back-end for this?

FYI - I'll be using rails as my framework.

Many thanks!

-Tony

Answer 1

Typically I would create a separate table for the client specific pricing, like this:

Products (
   ProductID,
   ProductPrice
)

ProductSpecialPricing (
   ProductID,
   ClientID,
   ProductPrice
)

Then, when you go to get the pricing, you would use a query like this to make sure you get the appropriate price:

SELECT 
   ProductID,
   COALESCE(psp.ProductPrice, p.ProductPrice) AS ProductPrice
FROM Products p 
LEFT JOIN ProductSpecialPricing psp ON psp.ProductID = p.ProductID 
WHERE psp.ClientID = ?