[ic] UserTag to return correct price for a given quantity using new pricing table

Kevin Walsh interchange-users@interchange.redhat.com
Fri Apr 19 06:27:00 2002


> 
> The PostgreSQL table structure:
> 
> Database  qty_pricing  qty_pricing.txt  __SQLDSN__
> ifdef SQLUSER
> Database  qty_pricing  USER         __SQLUSER__
> endif
> ifdef SQLPASS
> Database  qty_pricing  PASS         __SQLPASS__
> endif
> Database  qty_pricing  KEY          qty_pricing_id
> Database  qty_pricing  AUTO_NUMBER  1
> Database  qty_pricing  COLUMN_DEF   "qty_pricing_id=int NOT NULL PRIMARY KEY"
> Database  qty_pricing  COLUMN_DEF   "sku=VARCHAR(64)"
> Database  qty_pricing  COLUMN_DEF   "price_group=VARCHAR(12)"
> Database  qty_pricing  COLUMN_DEF   "quantity=int DEFAULT 0 NOT NULL"
> Database  qty_pricing  COLUMN_DEF   "price=VARCHAR(12)"
> Database  qty_pricing  INDEX         sku
> Database  qty_pricing  INDEX         price_group
> 
This sort of thing has always worked well for me.

Note that this table also makes it nice and easy to dump the price
breaks onto the flypage.  Instead of all that messing about working
out whether q5 == q2 etc., you just loop through the table and dump
whatever pops out.  If the q5 value should be the same as the q2,
then the merchant need not enter a q5 value at all.

Also, I would suggest changing the type of your 'price' column
from VARCHAR to something a little more numeric.  Although there's
nothing wrong with what you have done in this context, your choice
of VARCHAR just doesn't look right. :-)

>
> Future enhancements:
> Add price_group feature, if the product belongs to a price group then
> lookup based on both the sku and price_group and pick the lower of the
> two prices for the given quantity.
>
Another future enhancement to your tag:  A price_schedule column
would allow you to offer different price breaks for different
classes of user.  You could have 'retail', 'dealer' and 'employee'
price breaks, for instance.  Perhaps this is what you intended your
price_group column to be used for, although it didn't read that way
to me.

>
> Add flag to control break behavior, if a flag is set in the productdb
> then only return a price for an exact quantity being ordered and ignore
> the last exceeded break point.  (Emulate how the quantity lookup works
> out of the box)
>
I can't see that being too useful.

>
> I hope someone else finds this useful. Thanks again for everyone's help
>
Thanks for posting your work.  The person who was attempting to
charge "\$20" for Playpal code should take some notes.

-- 
   _/   _/  _/_/_/_/  _/    _/  _/_/_/  _/    _/
  _/_/_/   _/_/      _/    _/    _/    _/_/  _/   K e v i n   W a l s h
 _/ _/    _/          _/ _/     _/    _/  _/_/    kevin@cursor.biz
_/   _/  _/_/_/_/      _/    _/_/_/  _/    _/