CS 355 – Database Design
5 November 2003
A
consulting firm uses an Excel macro application that pops up forms for filling
in customer information and generating rate quotes. There is a need to store
and retrieve customer information, as well as to store and retrieve rate model
configuration parameters - instead of filling in the forms from scratch each
time the application is run.
Customers
are typically corporate offices employing from 50 to 1000 persons. Dental plan
rate calculations are influenced by population figures, provider pricing, plan
features, geography, and previous business relations with the firm.
These
values represent both plan and model settings. The rate calculator engine
performs according to these settings, and provides monthly benefit rates tiered
with respect to level of service performed.
The
stored settings, as well as business information, are to be accessible using
retrieval and update queries. It is also desirable to maintain multiple
versions of plans that can be compared, reviewed and repositioned.
There
are seven tables, three of which (Account, Contact, Industry) maintain
infrequently changing data describing the customer and the business
relationship.
The
Plan table stores tuples that represent specific plan versions for specific
customers, and has a dual primary key. The Tier table has the same two key
components plus two more components; its tuples represent discrete tiered
parameters (e.g., the in-network deductible figures or the
out-of-network co-pay figures) for a specific Plan.
The
ModelType table is accessory to Plan. The ModelConfig table maintains group
assignments of special dental services to certain rate levels; the ability to
manipulate these assignments independently is a feature of this class of
benefit models.