Consecutive ranges - best practice

[spikepl]

Time and time again I deal with ranges. For example, shipping, where shipping cost is determined by weight.

What would be the generally best way of storing such ranges?

WeightRange    Cost
0-1                 1
1-5                 3
5-10               10
>10                20

Now I have different options for storing this data

I can give the upper limit of each range and the price for that range, like in tblRates1.

Con: This requires searching for the appropriate limit when I want to find the cost for a given shipment.

Pro: Simple to change, no redundant data

tblRates1
----------
RateID    Weight     Cost
1              1         1
2              5         3
3             10        10
4           9999        20

I can also give ranges, like in tblRates2.

Con: Redundancy, since we know that a range begins where previous range ends. But so what?

Pro: SImple in use, since I can join on non equi joins and get the right cost directly without subqueries

tblRates2
----------
RateID    WeightLow   WeightHigh  Cost
1              0        1         1
2              1        5         3
3              5       10        10
4             10     9999        20

When managing documents, there is a similar but not exactly alike situation:

Revisions
DocID   RevID  ValidFrom
1         1    1/1-2013
1         2    1/1-2014
1         3    5/5-2014

Again, one could store the data with just one of the validity-range limits, or with both limits. The latter method entails updating two records when a new revision is issued: the one for the new revision and the last one, to set the end of validity for that previous revision.

Comments?

 

[Rabbie]

I think it is a matter of personal taste. I would always go just storing the upper limit but I can see why people might prefer the other method

 

[gemma-the-husky]

whichever you do, you will end up needing to investigate and use a join type called a non-equi join

you are searching for the right record by not using an equality operator, but by something like

pricelevel = dmin("upperbound","tbllimits","upperbound>=" & target)

ie - the lookup will find all of the bands that exceed the target, but the one you need is minimum of these. This identifies the band, then you need another read to find the price.

I am pretty sure you can include a join of this type directly in a query - but I would need to experiment.

 

[spikepl]

I am still curious. I made a small sample DB, where some item's shipment price depends on weight. In tblPrices1 the price is given for the max weights, and in tblPrices2 it is given as a range. If you riun Query 1 you'll get shipment price for 4 diffreent items, based on tblPrices2. What woulkd be the best aproach to have shipping price calculcated based on tblPrices1?