HELP Model Simple Pricing Structure
Date: 14 Jun 2002 18:36:12 -0700
Message-ID: <4e07f6b1.0206141736.1eebdb71_at_posting.google.com>
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 theirmax 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:36:12 CEST