Customer specific price matrix design (Long)

Hi All,
Not sure if this is the right group so feel free to point me in a better
direction.
Most of our customers have a personal price for every product but Im having
a nightmare trying to produce a price matrix on the fly. I currently have a
solution in MS Access 2K which uses a series of append and update queries to
create a temp table, this takes a little over 2 minutes to generate and is
becoming problematic. The idea is to have the sales office be able to give a
customer a price over the phone - a 2-3 minute wait isnt going to work.
I have 3 sources of prices
global : This contains a default price for every stock item it is the base
of all prices
price set(s): these contain a group of stock items from global at a
discounted price
personal product prices : A customer specific price for a specific stock
item
All prices can and do change and frequently and there are are also quantity
discount breaks for the same stock Item.
We carry approximately 3500 products and have about 4000 customers.
Im currently in the process or moving things from MS Access 2K over to MS
SQL Server 2K
I realise that its difficult to visualise without the database but if
someone could offer some pointers,
Im put off buliding 1 big flat table due to the size and the frequent
updates - am I right to avoid doing this?
Is it correct to construct the temp table and then running update/inserts -
Im having problems with the lack of a unique ID as Stock code is duplicated
for different quantity.
I can quickly generate a temp table containing all the information from each
source adding a field value for the process number (1,2,3 ) what Id like is
the record with the highest process number, but if I use a group by and max
as soon as I add price in the view I get all the records again.
I cant help but think I have something fundamentally wrong as its causing
such a problem. Can anyone point me at some info or share some insight.
TIA."Annon" <Ieatlotsofspam@.yum.yum> wrote in message
news:rNomf.5843$iz3.8@.text.news.blueyonder.co.uk...
> Hi All,
> Not sure if this is the right group so feel free to point me in a better
> direction.
> Most of our customers have a personal price for every product but Im
> having
> a nightmare trying to produce a price matrix on the fly. I currently have
> a
> solution in MS Access 2K which uses a series of append and update queries
> to
> create a temp table, this takes a little over 2 minutes to generate and is
> becoming problematic. The idea is to have the sales office be able to give
> a
> customer a price over the phone - a 2-3 minute wait isnt going to work.
> I have 3 sources of prices
> global : This contains a default price for every stock item it is the base
> of all prices
> price set(s): these contain a group of stock items from global at a
> discounted price
> personal product prices : A customer specific price for a specific stock
> item
> All prices can and do change and frequently and there are are also
> quantity
> discount breaks for the same stock Item.
> We carry approximately 3500 products and have about 4000 customers.
> Im currently in the process or moving things from MS Access 2K over to MS
> SQL Server 2K
> I realise that its difficult to visualise without the database but if
> someone could offer some pointers,
> Im put off buliding 1 big flat table due to the size and the frequent
> updates - am I right to avoid doing this?
> Is it correct to construct the temp table and then running
> update/inserts -
> Im having problems with the lack of a unique ID as Stock code is
> duplicated
> for different quantity.
> I can quickly generate a temp table containing all the information from
> each
> source adding a field value for the process number (1,2,3 ) what Id like
> is
> the record with the highest process number, but if I use a group by and
> max
> as soon as I add price in the view I get all the records again.
> I cant help but think I have something fundamentally wrong as its causing
> such a problem. Can anyone point me at some info or share some insight.
>
You'll need to post your table DDL, sample data and desired output for any
real information.
David|||which takes precedence? (i assume customer -> price set -> global)
how does a price set relate to the customer? (if it does)
if it doesn't, how do you know which to use? (assuming a stockno can be
in more than one price set)
what do you mean by a "process number"?
it would probably be best to avoid a big flat table in this case, but if
you make the source for this matrix a data warehouse, then that's
probably what you'll have.
DDL will certainly help get better answers...
Annon wrote:
> Hi All,
> Not sure if this is the right group so feel free to point me in a better
> direction.
> Most of our customers have a personal price for every product but Im havin
g
> a nightmare trying to produce a price matrix on the fly. I currently have
a
> solution in MS Access 2K which uses a series of append and update queries
to
> create a temp table, this takes a little over 2 minutes to generate and is
> becoming problematic. The idea is to have the sales office be able to give
a
> customer a price over the phone - a 2-3 minute wait isnt going to work.
> I have 3 sources of prices
> global : This contains a default price for every stock item it is the base
> of all prices
> price set(s): these contain a group of stock items from global at a
> discounted price
> personal product prices : A customer specific price for a specific stock
> item
> All prices can and do change and frequently and there are are also quantit
y
> discount breaks for the same stock Item.
> We carry approximately 3500 products and have about 4000 customers.
> Im currently in the process or moving things from MS Access 2K over to MS
> SQL Server 2K
> I realise that its difficult to visualise without the database but if
> someone could offer some pointers,
> Im put off buliding 1 big flat table due to the size and the frequent
> updates - am I right to avoid doing this?
> Is it correct to construct the temp table and then running update/inserts
-
> Im having problems with the lack of a unique ID as Stock code is duplicate
d
> for different quantity.
> I can quickly generate a temp table containing all the information from ea
ch
> source adding a field value for the process number (1,2,3 ) what Id like
is
> the record with the highest process number, but if I use a group by and ma
x
> as soon as I add price in the view I get all the records again.
> I cant help but think I have something fundamentally wrong as its causing
> such a problem. Can anyone point me at some info or share some insight.
> TIA.
>
>|||If you can't post the DDL, explain the rules.
ML
http://milambda.blogspot.com/|||"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:ODmZj5R$FHA.436@.TK2MSFTNGP10.phx.gbl...
> which takes precedence? (i assume customer -> price set -> global)
> how does a price set relate to the customer? (if it does)
> if it doesn't, how do you know which to use? (assuming a stockno can be in
> more than one price set)
> what do you mean by a "process number"?
> it would probably be best to avoid a big flat table in this case, but if
> you make the source for this matrix a data warehouse, then that's probably
> what you'll have.
> DDL will certainly help get better answers...
Hi Tery,
Your precedence is right, customer - price set then global, 1 to 3.
What im looking for,
Item Qty Price
Item1 1 2.25
Item1 10 1.5
Item2 10 1.9
Item3 100 4.99
Item4 1 2.2
The best I've come up with so far is to copy each stage (global, price set
then customers price) into a temp table with triggers to check if the
stock_Code and Qty match then update rather than append.
Any help suggestions welcome.
(SQL server is all really rather new to me still)
DDL hopefully below.
CREATE TABLE [dbo].[tbl_Customers] (
[ID] [numeric](18, 0) NOT NULL ,
[Company] [char] (10) NULL ,
[ID_Price_set_Global] [numeric](18, 0) NULL ,
[ID_Price_set_Main] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_Matrix_Base] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Price_Set_ID] [numeric](18, 0) NULL ,
[Company_ID] [numeric](18, 0) NULL ,
[Stock_Code] [varchar] (10) NOT NULL ,
[Quantity] [numeric](18, 0) NOT NULL ,
[Price] [float] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_Price_Sets] (
[ID] [numeric](18, 0) NOT NULL ,
[Price_set_ID] [numeric](18, 0) NOT NULL ,
[Description] [varchar] (50) NULL
) ON [PRIMARY]
GO
insert into tbl_customers
([ID],Company,ID_price_set_global,ID_Pri
ce_set_main) values (1,'Test1',1,2)
Go
insert into tbl_price_sets ([ID],Price_set_ID,[Description]) values
(1,1,'Price set 1 Global')
insert into tbl_price_sets ([ID],Price_set_ID,[Description]) values
(2,2,'Price set 2 Band A')
Go
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item1',1,2.25)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item1',10,2)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item2',10,3)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item3',100,4.99)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item4',1,2.2)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(1,null,'Item2',10,1.9)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
(null,1,'Item1',10,1.5)
Go|||Annon (Ieatlotsofspam@.yum.yum) writes:
> Your precedence is right, customer - price set then global, 1 to 3.
> What im looking for,
> Item Qty Price
> Item1 1 2.25
> Item1 10 1.5
> Item2 10 1.9
> Item3 100 4.99
> Item4 1 2.2
> The best I've come up with so far is to copy each stage (global, price set
> then customers price) into a temp table with triggers to check if the
> stock_Code and Qty match then update rather than append.
Is that table structure you posted carved in stone? Or can you change it?
I started to compose a query from your repro, but I found it darn
difficult as tbl_Matrix_base is so scattered. And there were also
some thing I did not understand. What does those two ID_price_set in
Customers mean? And your sample data had:

> (Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
> (1,null,'Item2',10,3)
> (Price_set_Id,Company_ID,Stock_code,Quan
tity,Price) values
> (1,null,'Item2',10,1.9)
How do I know which is which?
First of all, you need to keep track of which is which. To start with
you need a table to define sellable items:
CREATE TABLE sellable_items (stockcode varchar(10) NOT NULL,
qty smallint NOT NULL,
stdprice float NOT NULL,
CONSTRAINT pk_selleable_items PRIMARY KEY (stockcode, qty))
This table could just as well hold the standard price.
A price-set table would then look like this
CREATE TABLE priceset (pricesetid integer NOT NULL,
pricesetname varchar(30) NOT NULL,
CONSTRAINT pk_priceset PRIMARY KEY (pricesetid))
The actual prices in a price set would be in:
CREATE TABLE pricesetprices (pricesetid integer NOT NULL,
stockcode varchar(10) NOT NULL,
qty smallint NOT NULL,
price float NOT NULL,
CONSTRAINT pk_pricesetprices PRIMARY KEY
(pricesetid, stockcode, qty),
CONSTRAINT fk_prp_priceset FOREIGN KEY(pricesetid)
REFERENCES pricesets (pricesetid),
CONSTRAINT fk_prp_sellable FOREIGN KEY (stockcode, qty),
REFERENCES sellable_items (stockcode, qty)
)
Then then customer-specific prices:
CREATE TABLE customerprices (customerid integer NOT NULL,
stockcode varchar(10) NOT NULL,
qty smallint NOT NULL,
price float NOT NULL,
CONSTRAINT pk_customerprices PRIMARY KEY
(customerid, stockcode, qty),
CONSTRAINT fk_cup_customer FOREIGN KEY(customerid)
REFERENCES tbl_Customers (ID),
CONSTRAINT fk_cup_sellable FOREIGN KEY (stockcode, qty),
REFERENCES sellable_items (stockcode, qty)
)
Now to get all prices for a customer you can do:
SELECT s.stock_code, s.qty, price = coalesce(cup.price, prp.price,
s.price)
FROM sellable_items s
CROSS JOIN (SELECT * FROM tbl_Customers WHERE ID = @.custid) c
LEFT JOIN pricesetprices prp ON c.pricesetid = prp.pricesetid
AND s.stock_code = prp.stock_code
AND s.qty = prp.qty
LEFT JOIN customerprices cup ON c.customerid = cup.customerid
AND s.stock_code = cup.stock_code
AND s.qty = cup.qty
A very important thing to note here is the use of foreign keys, and
multi-column primary keys. A common mistake is to add an ID column to
each table and think you have a primary key. For basic concepts like
customers, this can be a good thing, but for composed entities like the
price plan is not.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns972985B77D074Yazorman@.127.0.0.1...
> Annon (Ieatlotsofspam@.yum.yum) writes:
<SNIP elegant solution>

> Now to get all prices for a customer you can do:
> SELECT s.stock_code, s.qty, price = coalesce(cup.price, prp.price,
> s.price)
> FROM sellable_items s
> CROSS JOIN (SELECT * FROM tbl_Customers WHERE ID = @.custid) c
> LEFT JOIN pricesetprices prp ON c.pricesetid = prp.pricesetid
> AND s.stock_code = prp.stock_code
> AND s.qty = prp.qty
> LEFT JOIN customerprices cup ON c.customerid = cup.customerid
> AND s.stock_code = cup.stock_code
> AND s.qty = cup.qty
> A very important thing to note here is the use of foreign keys, and
> multi-column primary keys. A common mistake is to add an ID column to
> each table and think you have a primary key. For basic concepts like
> customers, this can be a good thing, but for composed entities like the
> price plan is not.
>
Thanks for the solution Erland, unfortunatly as is always the way - Im stuck
with backward compatibility I cant rip out the tables and replace them with
the correct design. As you point out the problem is not know what is what
and from where it came. All the data is held in 1 table the tbl_Matrix_base,
customer, price set and global all lumped together. As time was pressing I
kept the temp table created a compound key field from Company & Stock_Code &
Qty , created a unqiue index on it but with ingnore_dup_key on. and ran the
3 insert queries. It adds new records but fails to add duplicates - as long
as I keep the insert order correct it gives me the results I need.
Its far from perfect - but its bought me more time, I'll take apart your
example and see if I can work it back into the live system.
Thanks again for the help.