database/algorithm for a rate structure

Question

I have to calculate a price based on a rate structure along these lines:

$303.00 fixed price up to 500 units
$0.023 additional per unit from 501-10,000 units
$0.022 additional per unit from 10,001-25,000 units
$0.021 additional per unit from 25,001-50,000 units

I'm a little lost on setting up a database structure and algorithm (the larger sticking point) for calculating this. Has anyone done this? Is there a nice, elegant way of calculating this sort of thing?

edit: As an example, a 25,100 unit run would cost $303.00 for the first 500 units, $218.50 for the next 9,500 units, $330.00 for the next 15,000 units, and $2.10 for the next 100 units, for a total of $853.60.

It wouldn't be a simple 25,100 * $0.021 calculation - I'm well aware of how to select and calculate that.

Similar to the way income tax is assessed - on a marginal basis.

Answer 1

Something like this:

Product
-------
[PK] ProductID


Price
-----
[PK] PriceID
[FK] ProductID
Price
QtyMin
QtyMax

So effectively a 1-many relationship between product and price. You could use a sentinel value for the maximum if you require a flat rate regardless of quantity.

Answer 2

Yes, PHP has the if statement.

Answer 3

Python

from collections import namedtuple

RateRule= namedtuple( 'RateRule', ['qty_band','fixed','per_unit'] )    

rate_table = [
    RateRule(500, 303, None),
    RateRule(9500, None, 0.023),
    RateRule(15000, None, 0.022),
    RateRule(25000, None, 0.021)
]

def total_price( units, rate_table ):
    # Base
    total = rate_table[0].fixed
    units_purchased_so_far = rate_table[0].qty_band
    # Whole Price Bands
    rule = 1
    while units > units_purchased_so_far + rate_table[rule].qty_band:
        total += rate_table[rule].qty_band * rate_table[rule].per_unit
        units_purchased_so_far += rate_table[rule].qty_band
        rule += 1
    # Units within the top Price Band
    if units > units_purchased_so_far:
        total += (units - units_purchased_so_far) * rate_table[rule].per_unit
    return total

Answer 4

SELECT 
 CASE is_fixed_price
  WHEN 1
   THEN unit_price / ?  
  ELSE
   unit_price
  END
FROM rate_structure
WHERE ? BETWEEN min_qty AND max_qty

Where ? is the quantity your customer wants to order. Syntax off the top of my head, for mysql 5.x. The side effect of this is potential rounding error accumulation.

Answer 5

I assume you want something flexible, otherwise it would be trivial to hardcode it.

You could use a pricing table:

ID MAX    FIX    UNIT
1  500    303    0
2  9500   0      .23
3  15000  0      .22
4  25000  0      .21

Then you could calculate as follows:

$items = ?;
$cost = 0;
$rows = get_rows("select max, fix, unit from pricing order by id asc");
foreach ($rows as $r)
{
    if ($items <= 0)
        break;
    $cost += $r['fix'] + min($r['max'], $items) * $r['unit'];
    $items -= $r['max'];
}

I have assumed that you want the algorithm in PHP.

Answer 6

What I wound up doing:

size  units     fixed     per
1       500   303.000   0.000
1     10000     0.000   0.023
1     25000     0.000   0.022
1     50000     0.000   0.021



function calculate_price($size, $quantity) {
  global $db;

  $price = 0;
  $count = 0;

  // fetch rates from the database
  // note: $size is already sanitised by the calling function
  $query = "SELECT units, flat, per FROM rates WHERE size={$size} ORDER BY units ASC";
  $result = $db->query($query);

  // step through the rates
  while($rate = $result->fetch_object()) {
    // figure out how many of our units fall within this tier
    $tier_count = max(0, min($quantity - $count, $rate->units - $count));

    // calculate the price for this tier, including any flat rate
    $tier_price = $rate->flat + ($rate->per * $tier_count);

    // add tier price and count to the totals
    $price += $tier_price;
    $count += $tier_count;

    // store the last, largest number of units rate for any leftovers outside our tiers
    $last_rate = $rate;
  }

  // if some of our units fall outside our defined tiers, use the last tier's values for them
  if($count < $quantity) {
    $tier_count = $quantity - $count;
    $tier_price = $last_rate->flat + ($last_rate->per * $tier_count);
    $price += $tier_price;
    $count += $tier_count;
  }

  return $price;
}