HELP Model Simple Pricing Structure

From: contrapositive <contrapositive_at_hotmail.com>
Date: Fri, 14 Jun 2002 21:29:52 -0400
Message-ID: <3d0a9d03_6_at_nopics.sjc>

Hi. I’m building a database and trying to model the following real world scenario. The concept is simple enough to grasp. First some background, then examples:

An insurance company will pay for either n number of repairs or an unlimited number of repairs per claim. So I need to store this maximum number of repairs that the insurer will pay (or that they will pay an unlimited number) and also I need to store the pricing structure. In general, if an insurer is invoiced x repairs (not to exceed their max), they will pay: Repair Price 1 + Repair Price 2 + ... + Repair Price x.

Some examples:

Insurer A
Max. Number of Allowable Repairs: 25
Repair 1: $50
Repair 2: $10
Repair 3-25: $5

Insurer B
Max Number of Allowable Repairs: Unlimited Repair 1-10: $50
Repair 11-...: $10

Insurer C:
Max Number of Allowable Repairs: Unlimited Repair 1: $25
Repairs 2-10: $15
Repairs 11-20: $10
Repairs 21-...: $5

Insurer D:
Max Number of Allowable Repairs: 3
Repairs 1-3: $25

Examples:

Insurer A (3 repairs) is invoiced: $50 + $10 + $5 = $65.
Insurer B (5 repairs) is invoiced: $50 + $50 + $50 + $50 + $50 = $250.
Insurer C (20 repairs) is invoiced: $260.
Insurer D (5 repairs) is invoiced: $25 + $25 + $25 = $75 (since their max is

3)

I thought of linking my Insurer table to some other table which contains this pricing or benefit data. But I don’t want 10 records that all say $10, 10 more with $5, or however the pricing structure is. There’s got to be a more concise way. And how might I signify whether the number of allowable repairs is some number x or unlimited? I’m looking for something conducive to the math (summation) that I’ll have to do in my queries to compute the prices.

Thanks in advance.

-jk Received on Sat Jun 15 2002 - 03:29:52 CEST