David Bozarth

CS 355 – Database Design

7 October 2003

 

PROPOSED Dental Plan Rate Quote database

 

 

OVERVIEW

 

A consulting firm uses an Excel macro application that pops up forms for filling in customer information and generating tiered rate quotes. There is a need to store and retrieve customer information, as well as to store and retrieve rate model configuration data - instead of filling in the forms from scratch each time the application is run.

 

 

SYSTEM REQUIREMENTS

 

The database design should facilitate scaling from development desktop to enterprise. Records are likely to remain small (up to a few kilobytes of text), and each client connection is likely to use only one-to-a-few records at a time. Many simultaneous connections may need servicing, however, and there may be a large number of records pertaining to a group of customers being processed - and thus needing ready access - by a single local domain.

 

Connection to the database by a desktop instance of the Windows macro application is part of the current project implementation work, but not part of the database design. The database design and implementation should not be made "macro-aware", because the front end may evolve, the total application architecture may change, new client platforms (e.g., handheld devices) could be added, and so on.

 

The database schema should embody abstraction, because various developing rate calculation models could be deployed eventually. The schema needs to service the current front-end prototype as a consequence of its ability to service the "generic dental rate calculator". The model designer's knowledge should guide the specification of ER and schema.

 

 

NOTES ON SCHEMA

 

For now, the current desktop prototype uses three categories of data that should be maintained:

 

 

 

Typical queries or super-queries: