Re: HELP Model Simple Pricing Structure
Date: Fri, 14 Jun 2002 21:58:35 -0400
Message-ID: <3d0aa298_4_at_news1.prserv.net>
jk,
> I’m building a database and trying to model ...
Great, but is the application OLTP or DSS / OLAP?
If the latter (DSS / OLAP), then I'd suggest picking up Ralph Kimball's "The Data Warehouse Toolkit : Practical Techniques for Building Dimensional Data Warehouses". The book discusses a number of data models, including ones for the insurance industry.
On the other hand, if your application is OLTP, and if I understand your requirements, the structure you don't want to implement is in fact the correct, normalized, structure. Normalized databases are not "concise", but are instead designed for performance and data consistency.
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which can be cut and pasted into Query Analyzer is appreciated.
"contrapositive" <contrapositive_at_hotmail.com> wrote in message
news: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:58:35 CEST