Hello,

I'm curious about best practices involving products, their pricing and
modelling variable pricing for new versus "renewal" pricing.

The main thing of interest is the temporal modelling required for
recurrent billing. The products and pricing is fairly straightforward.

I suppose you would need some sort of products_purchases table which
would store all occurences of a product purchase. Then you could have
either explicit prices based on the number of years the product was
owned or some sort of percentage table indicating price changes as a
function of years of ownership.

All in all, most of this might be better done with stored procedures.

Just brainstorming for ideas about how this sort of thing has been
tackled in the past.

Now, time to step out of abstract and into practical. A big mistake
would be to have a "renewal product."

For instance, if you were a domain registrar and you had a
domain_registration product, the last thing you need is a
domain_registration_renew product so that you can have separate prices
for new versus renewal domains. Why? Because it is rigid and only
expresses new versus renew whereas recurrence can have many degrees of
depth based on years of renewing....

On Wed, 7 Jul 2010, metaperl wrote:

You've got to start with a business rule, of course. Then you model that.
Do you have a business rule? Depending on what the rule is, you might be
better off doing it as tables, sprocs, or in app code.

Two approaches i've seen are price lists and discounts.

'Price lists' means somehow having multiple prices defined for each
product (or SKU), and offer different ones at different times - it might
be as simple as 'full price' and 'sale price', or there might be prices
for each customer (more common in B2B, where you negotiate prices as part
of a procurement contract), or you could do tiered pricing based on
quantity for bulk discounts, or your new/renewal or new/1y/2y/3y etc.

'Discounts' means you have a single price for each product, and then a set
of discounts which applied under certain conditions. So, as above, an 'on
sale' discount, a 'preferred customer' discount, discounts specific to
each customer, discounts for buying in bulk, etc. It boils down to much
the same thing as the price lists approach, just expressed in different
arithmetic.

The discounts approach is intrinsically more flexible, because you can
combine discounts. And you handle these 'pricing policy' discounts
alongside more user- or situation-specific discounts like Half Price
Fridays, spring sales, 10% off for new users, 20 guineas off with
such-and-such a coupon, etc. Plus, you can express discounts in lots of
ways - a constant amount off, a percentage off, and more creative things
like free shipping on an item, buy one get one free, and so on and so
forth.

The downside is that it may present a more complex mental model to your
business users (content managers, merchandisers, or whatever you call
them). They may prefer to think in terms of "we charge second-year renewal
customers 55 sestertii a peck-hour" rather than "we charge second-year
renewal customers 15 sestertii less for a peck-hour than new customers".
You could implement that interface in terms of discounts, but that would
be perverse.

Anyway, i know this doesn't help with the specific question of renewal
pricing, but there you go. Basically, you have to talk to your business
users until you have a good understanding of their mental model of the
domain, and what they want from the system. And, of course, this may not
be what they say they want from the system.

Also, they actually aren't different products. If you have things like
product titles and descriptions in the product table, then you will be
denormal.

But let's think this through. If you address the denormality by factoring
out the degenerate columns, then the table you pull out is, de facto, a
product table, and your original product table is now a sort of 'product
proposition' table, about selling a certain product at a certain price
under a certain name. That's basically the price lists approach i outline
above.

And, of course, in some systems, such as the one i'm working on now, the
solution to the pricing problem is "just send the whole basket off on a
web service call to SAP and use whatever price it comes up with" .

tom

--
:-( bad :-) bad :-| good

On Jul 7, 1:33*pm, Tom Anderson <t... (AT) urchin (DOT) earth.li> wrote:


yes, I agree. and be sure to write the code so that you can have
negative discounts as well as positive ones. Sometimes after you lock
in a customer, business wants to charge more, leading to negative
discounts

agreed. it's very indirect thinking.

Let me use a history table for price changes. The fact is that a
price had duration. This is the nature of time and other continuums.
So a basic history table looks like this in SQL/PSM

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL -- industry standard
REFERENCES Inventory(upc),
price_prev_date DATE NOT NULL,
price_start_date DATE DEFAULT CURRENT_DATE NOT NULL,
price_end_date DATE, -- null means current price
CHECK(price_start_date < price_end_date),
CHECK (price_start_date = price_prev_date + INTERVAL 1 DAY), --
prevents gaps
PRIMARY KEY (upc, price_start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
etc.);

You use a BETWEEN predicate to get the appropriate price. You can
enforce the "one null per item" with a trigger but techically this
should work:

CHECK (COUNT(*) OVER (PARTITION BY upc)
= COUNT(price_end_date) OVER (PARTITION BY upc) +1)

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.price_start_date
AND COALESCE (price_end_date, CURRENT_DATE);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE price_end_date IS NULL;

Now your only problem is to write a stored procedure that will update
the table and insert a new row. You can do this with a single MERGE
statement, or with a short block of SQL/PSM code:

CREATE PROCEDURE UpdateItemPrice
(IN in_upc CHAR(13), IN new_item_price DECIMAL (12,4))
LANGUAGE SQL
BEGIN ATOMIC
UPDATE PriceHistory
SET price_end_date = CURRENT_DATE
WHERE upc = in_upc;
INSERT INTO PriceHistory (upc, price_prev_date, price_start_date,
price_end_date, item_price)
VALUES (in_upc, CURRENT_DATE, CURRENT_DATE + INTERVAL '1' DAY, NULL,
new_item_price);
END;

This will make the price change go into effect tomorrow.

There is a common kludge to repair a failure to design a history table
properly that you can put in a VIEW if you are not able to set things
right. Assume that every day we take a short inventory and put it in
a journal. The journal is a clip board paper form that has one line
per item per day, perhaps with gaps in the data. We want to get this
into the proper format, namely periods shown with a (start_date,
end_date) pair for durations where each item had the same quantity on
hand. This is due to Alejandro Mesa

CREATE TABLE InventoryJournal
(journal_date DATETIME NOT NULL,
item_id CHAR(2) NOT NULL,
PRIMARY KEY (journal_date, item_id),
onhand_qty INTEGER NOT NULL);

WITH ItemGroups
AS
(SELECT journal_date, item_id, onhand_qty,
ROW_NUMBER() OVER(ORDER BY item_id, journal_date, onhand_qty)
- ROW_NUMBER() OVER(PARTITION BY item_id, onhand_qty
ORDER BY journal_date) AS item_grp_nbr
FROM Journal),

QtyByDateRanges
AS
(SELECT MIN(journal_date) AS start_date,
MAX(journal_date) AS end_date,
item_id, onhand_qty
FROM ItemGroups
GROUP BY item_id, onhand_qty, item_grp_nbr)

SELECT start_date, end_date, item_id, onhand_qty
FROM QtyByDateRanges;

This might be easier to see with some data and intermediate steps

INSERT INTO InventoryJournal
VALUES('2007-01-01', 'AA', 100),('2007-01-01', 'BB', 200),
('2007-01-02', 'AA', 100),('2007-01-02', 'BB', 200),
('2007-01-03', 'AA', 100),('2007-01-03', 'BB', 300);

start_date end_date item_id onhand_qty
==========================================
'2007-01-01' '2007-01-03' 'AA' 100
'2007-01-01' '2007-01-02' 'BB' 200
'2007-01-03' '2007-01-03' 'BB' 300

Now, download the Rick Snodgrass book on Temporal Queries in SQL from
the University of Arizona website (it is free).

On 2010-07-11, --CELKO-- <jcelko212 (AT) earthlink (DOT) net> wrote:
How?


--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

Opps! You need a REFERENCES to the history table assure that the
price_prev_date is also used as a price_end_date. SQL Serrver is bad
about cycles, however.

On 2010-07-14, --CELKO-- <jcelko212 (AT) earthlink (DOT) net> wrote:
Newsgroups: comp.databases
From: Jasen Betts <jasen (AT) xnet (DOT) co.nz>
Subject: Re: products, pricing, recurrence
References: <2257bfac-c046-4f1a-bab0-4e06176a197a (AT) a30g2000yqn (DOT) googlegroups.com> <89955351-c2c8-4b82-a6b2-e45e5d4591ce (AT) y4g2000yqy (DOT) googlegroups.com>
Organization: Dis (not Dat) Organisation
Followup-To:
X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Z k(>h!~o2ID@6{uf8s;a+M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W;(T"1(739R%m0Yyyv*gkGoPA .$b,D.w:z+<'"=-lVT?6{T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f %.kl^:7X8rQWd[NTc"P"u6nkisze/Q;8"9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^

On 2010-07-11, --CELKO-- <jcelko212 (AT) earthlink (DOT) net> wrote:
ah, now I see you can't cant have an FK constraint on a computed
expression only on columns so the check is needed to force the
correct value for the FK into the price_prev_date cell.

It seems it would be simpler to drop the price_start_date column,
or change the meaning (and name) of price_end_date to be the
first day of the next price.

UPC char(13) seemed odd but then I realised
space paddeding for shorter codes (like ean-8)
is not a problem and using a fixed-width column is probably more
efficiemnt. If needed a check can be added to ensure only valid
codes are entered.




--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

I would not put a shorter code in a long column. The fixed length
column serves several purposes: (1) the length is a quick validation
of the general shape (2) the regular expression for the content is
easy -- [:digit:]{13} (3) Writing the check digit formula is easier.

The book trade in the US has done a good job of getting from ISBN-10
to ISBN-13 as part of the UPC to EAN conversion. Actually, I ought to
be using CHAR(15)to get ready for the GTIN (Global Trade Item Number)
codes that will be in place in a few years. Those "permanent natural
keys" seem to keep changing and the world does not end