Try not to conceptualize in OO terms - it doesn't help much in RDBMS.

Some example data might help us understand your scenario but here's a best
guess:

CREATE TABLE Products (sku INTEGER PRIMARY KEY, product_desc VARCHAR(20) NOT
NULL UNIQUE)

CREATE TABLE ProductPrices (sku INTEGER NOT NULL REFERENCES Products (sku),
price_level INTEGER NOT NULL CHECK (price_level BETWEEN 0 AND 4 /* Default
price plus 4 levels */), price NUMERIC(8,2) NOT NULL CHECK (price>0),
PRIMARY KEY (sku, price_level))

CREATE TABLE Customers (customer_id INTEGER PRIMARY KEY, customer_name
VARCHAR(30) NOT NULL UNIQUE, price_level INTEGER NOT NULL CHECK (price_level
BETWEEN 0 AND 4))

Customers.Price_level represents the level of pricing that the customer is
entitled to. I'm assuming that 0 is the default price and 4 is the most
heavily discounted price. The query to get a price list for a given customer
(1234) is therefore:

SELECT P.sku, P.product_desc, MIN(R.price) AS price
FROM Products AS P
JOIN ProductPrices AS R
ON P.sku = R.sku
WHERE price_level<=
(SELECT price_level
FROM Customers
WHERE customer_id = 1234)
GROUP BY P.sku, P.product_desc

If this is not quite what you had in mind then maybe you could adapt the
above DDL statements and post some sample data (helps if you can post INSERT
statements rather than type it out as a list) to help us understand better.

Hope this helps.

--
David Portas
SQL Server MVP
--

Hi,

I need to design a table that stores products prices and can be subclassed.
Each product has it's own unique ID and a default price. Those prices can be
changed for specific customers, however for other customers without a
specific price, the default price is to be user.
It gets more complicated since those customers are build in an heirarchy up
to 4 levels. When a product's price has been modified on the topmost level
of the heirarchy - this record is to be used, and if not the "search" for
the correct price record should go down the heirarch, and if not found, the
default record is to be used.
The amount of products is about 150,000, and the subclassed prices are much
less (couple of thousands).

If anyone knows of a good way to implement it in SQL server, and would like
to share his ideas, please let me know.

Thanks,
Guy