Special Price for customers

Going by only what you've told me, I would probably have a seperate table named "SpecialPricing" or something similar.  This table would have your customer identifier along with product type(s) and special pricing for that product for that customer.  Does that make sense?

You have to link table of customers and a table of products via 1 more table
:)

Have a look at the attached code. It creates three sample tables and then the query will list all the prices for the customer with ID=1. When there is a customised price it will take priority to general price on a product

create table Customers (
	 customerId int primary key
	,customerName varchar(32)
);
create table Products (
	 productId int
	,productName varchar(32)
	,productPrice decimal(15,2)
);
create table CustomerPrices (
	,customerPriceCustomer	int
	,customerPriceProduct	int
	,customerPricePrice		decimal(15,2)
);



select customerName, coalesce(customerPricePrice, productPrice) as CustomisedPrice
from Customers
left outer join CustomerPrices on customerId = customerPriceCustomer
left outer join Products on productId = customerPriceProduct
where customerId = 1

Select allOpen in new window

Do not have prices in the Products table.  It is the wrong place for it.  You should have a cross-reference table between the Products and Customers that stores the price based on Product and Customer.

acperkins,

It is all relative. If you don't need to track historical data price stored by the product is quite natural thing. If most of the customers have the standard price (or if in general you have standard prices) you may still keep it in the Products table and only store customised prices in an additional table.

>>It is all relative. <<
That may well be, but in this particular case it is not.  It does not make any sense to keep the pricing associated with Products.  

And I will go even further, that is like keeping inventory counts in the Products table.  They do not belong there.  It defeats the whole point of any type of normalization.  Accuse me of being a purist if you like, but to say that it is relative is to put it frankly, meaningless.

Let me word it a different way.  Let's assume that you have a table that only stores "customized prices".  So now you have to maintain a flag for each client that are special and have a different price.  So you have to ask every time, is flag set then check this table otherwise check this other.  If we cannot see it from a logical point of view at the very least consider the gymnastics you need to do to get a pricing list for all clients.  Do you create a UNION statement for both tables or do you insert into a temporary table from each table and then output the result.

Is the client special or is the product special or both?  In other words how do you support the situation that a client has "special" pricing for some products and not for others?

Can you define special? How special is special?  Do we have special pricing and then very special pricing?  Can you see where this is going?

If you are going to go to all the trouble of maintaining a list of different prices in a different table, then do it right and create a pricing list based on product AND customer.

My dear purist,

I see your point about normalization but practically there is a point where you stop normalizing any further because in real life very often at the same time the table must be optimal for read and write. I'd love to have all pure and normalized but reality is harsh.

As to the problem with flags if you would kindly review my previous example you will see that it is not a problem at all. You don't need flags and you don't need unions. If you want to display prices for all customers than you join customers with products with an inner join and then outer join to customrPrises projecting the price using coalesc function passing potentially existing customised price as first argument and standard prise as the second.

One of the points in normalizing data is not to store redundant information. Let's assume that you've got 1000 customers and 1000 products. We don't need historical prices and as author of the problem mentioned only few customers have special prices for few products (let's say 100 customers and 100 products).

In your solution you will have a table with 10000 prices where 900 values are duplicated 900 times. In mine, you store there only 1000 unique values.

I have been using this approach for different standard / customized values quite successfully.

Oh, and don't take the offence for the Purist. I'm open to discuss.
Lof

lof, I tend to agree with you. I don't need historic data of price changes.
About the code you provided, after executing the query will i get only 1 dataset (datatable or whatever) with the normal prices joined with special prices???

lof, why in the CustomerPrices  table I dont have an id column for the table. what is my primary key, do I need one for this??

@orenisraeli21000: yes you will have only one table

@kulpe: your primary key should be defined to include both columns: customerPriceCustomer and customerPriceProduct  

My dear lof. thank you very much for all the info.
I'll check to code you provided in the next couple of days and be happy to give you the points.

Hello again,
Lets say I have a table 'Order' with (using your code)
customerId
productId
.
.
.
etc.
how do I get a customer orders with special price if exist???
I increased the points to 200

With orders and later invoices it is a good practice to store the prices separately from the base prices. But it is a business question first and only then technical.

What should happen if somebody has ordered an item and then you've changed the price? should the price been updated or should it stay as it was at the time of order? Do you want to keep historical orders for future reporting / data warehousing / mining? If so, surely you want to have the historic price rather than current.

That is why you should consider storing current price in the order's details table. And there you can have a field of bit type 'isPriceCustom' or something like that.

And to answer your question how to tell if the price is custom, have a look at the updated sample code

create table Customers (
         customerId int primary key
        ,customerName varchar(32)
);
create table Products (
         productId int
        ,productName varchar(32)
        ,productPrice decimal(15,2)
);
create table CustomerPrices (
         customerPriceCustomer  int
        ,customerPriceProduct   int
        ,customerPricePrice             decimal(15,2)
);



select customerName, coalesce(customerPricePrice, productPrice) as CustomisedPrice
	,case when customerPricePrice is not null then 1 else 0 end as PriceIsCustom
from Customers
left outer join CustomerPrices on customerId = customerPriceCustomer
left outer join Products on productId = customerPriceProduct
where customerId = 1

Select allOpen in new window

I don't need any historical data of prices nor price changing after ordering.
My order is made from  many products. and I need to display the right price, whether customeprice or not.
this was my original qustion

than just check the sample code and your answer is there.

I did. I get only products that have custome prices.
Lets say I have 120 products and for CustomerID = 1 I have 22 CustomerPrices.
Using the query above I get only 22 products, but I'm after the 120 joined with the 22 for the customerid =1

10x. I'll try later. I have so little knowledge in SQL query.

Now, That is working.
Great solutions. 10z for the help.
I hope you'll notice my next questions.

Great Help.

lof,
Please chack https://www.experts-exchange.com/questions/25086331/LINQ2SQL-return-Decimal-value-from-Stored-Procedure.html?anchorAnswerId=26406357#a26406357

lof,
can you help at : https://www.experts-exchange.com/questions/25340533/Sql-server-2008-reporting-services-many-reports-and-printing.html?anchorAnswerId=27621067#a27621067
???
good points