I am trying to find a design pattern/data model pattern for a database
problem regarding value ranges/tiers. I actually got this to work a
few years ago on my own, but I figured this is likely a scenario that
more folks have run into and a standardized solution may exist.

Here's the scenario:

I need to apply a multi-tiered fee system based on the amount of money
held in an account. As the amount of money held in an account goes up,
the value of fees applied drop (an incentive to hold more money in the
account). I can use business logic to apply the fees, but I need to
know how to best store it in a database. I'll use the example of three
tiers.

1) The number of tiers can be infinite
2) The first tier can have a min value of "infinite" in negative (if I
explained that correctly)
3) The last tier can have a max value of "infinite" in positive


My current table schema looks like so:

The records for this example would look like so:

AccountID | Tier | MinValue | MaxValue | Fee |
------------------------------------------------------------------------|
15 | 1 | $00.00 | $10.99 | $5.00 |
15 | 2 | $11.00 | $20.99 | $3.00 |
15 | 3 | $21.00 | -$01.00 | $1.00 |


I use the value of -$01.00 in the MaxValue of Tier 3 to represent
"infinity" in that spot. The business logic would have to recognize
that anytime a MaxValue has a value of -1, that marks the end of the
ranges.

You'd have to validate the tier system anytime changes are made,
validate the enter range system to make sure each tier's MinValue
leaves no gap between it and the previous tier's MaxValue.

I have most concern about my design regarding using the -1 value as
the MaxValue infinity in the last tier. It seems like I kind of
"rigged" it to work, but a more efficient method exists somewhere.

On Nov 2, 12:15 pm, wrburgess <wrburg... (AT) gmail (DOT) com> wrote:
There is going to be a certain amount of "rigging" since you are
implementing a stepwise continuous function. I see nothing sacred
about using -1 as a Max value. To save some sanity, you might consider
using NULL. You have to special case it anyway. And NULL might be
better incase the company decides to charge different rates for
overdrawn accounts. otherwise you need to encode the first/last logic
elsewhere (like the tier level).

ed

I would use NULLs for extremes, then your code can use this predicate

some_value BETWEEN COALESCE (min_value, some_value)
AND COALESCE (max_value, some_value)

A UNIQUE constraint on min-value and max_value will allow for one NULL
in each column.

Thank you for the responses.

I take it that "stepwise continuous function" is the term applied to
my problem?

I usually avoid using nulls in my database fields because I always
seemed to run into problems with them (no need for me to explain, it's
due to lack of experience). But using a null value probable makes the
most sense.

On Nov 4, 4:04 pm, wrburgess <wrburg... (AT) gmail (DOT) com> wrote:
Anyone, experienced or not, is taking a risk using nulls. However,
null is probably better than using a special marker of your own.

Another option is to split out infinity to a separate table. In you
case:

Whether worth it or not, you will have to decide.

/Lennart

Yes.

Columns can have NULLs; fields cannot since the concept does not exist
in file systems. Do not be afraid of them, but be careful. Mother
Celko's heuristics are:

0) Assume everything NOT NULL and then justify making something NULL-
able. Cowboy coders will allow everything to be NULL-able without any
thought at all.

1) Look for a meaningful DEFAULT value instead of a NULL. Especially
watch the effect on math and aggregates in columns that on one ratio
or interval scales.

2) Look for meaningful missing value code(s) in the scale used for the
attribute ('missing', 'N/A', etc.) -- research it

3) Be sure that a NULL has one and only one meaning for that attribute
in the data model. And put that definition in the Data Dictionary!

Mother Celko's heuristics are...much appreciated!