Need to Develop SQL Server 2005 Database to Store Insurance Rates

Question

Good Evening All,

A client has asked that I develop a web application as part of his existing site built in ASP.net 3.5 that will enable his brokers to generate quotes for potential client groups. Such quotes will need to be derived using rate tables stored in a SQL Server 2005 database.

The table structure is as follows:

[dbo].[PlanRates](
[AgeCategory] [int] NULL,
[IndustryType] [int] NULL,
[CoverageType] [int] NULL,
[PlanDeductible] [float] NULL,
[InpatientBenefit] [float] NULL,
[Rate] [float] NULL,
[OPMD15Copay] [float] NULL,
[OPMD25Copay] [float] NULL

Question: Assuming I use page validation in the web application to verify input against business logic, do you anticipate issues arising relative to the web application returning a quotation using the above database table layout? If so, can you suggest a better way to structure my table?

Bonus goes to anyone who has programmed web-based insurance quoting systems.

Thanks much for your help and guidance.

Answer 1

I would suggest using parameterized queries to save an retrieve your data to protect against SQL injection.

EDIT

It looks like

[AgeCategory] [int] NULL,
[IndustryType] [int] NULL,
[CoverageType] [int] NULL,

Are probably foreign keys, if so you may not want to make them null-able.

Answer 2

I would definitely add a surrogate primary key, e.g. PlanRatesID INT IDENTITY(1,1) to make each entry uniquely identifiable.

Secondly, I would think the fields "PlanDeductible", "InpatientBenefit", "Rate" are money values, so I would definitely make them of type DECIMAL, not FLOAT. Float is not very accurate and could lead to rounding errors. For DECIMAL, you need to specify the amount of significant digits before and after the decimal point, too, e.g. DECIMAL(12,3) or something like that.

That's about it! :)

Answer 3

NULLable category, types and rates?

Category and type columns are lookup fields to other tables storing additional information for each type.

You should check which of the columns is really nullable, and define how to deal with NULL values.

As rates can change, you should also consider a date range in the table (ValidFrom, ValidTo DATETIME), or have a history table associated with the table above to make sure past rate calculations can be repeated. (Might be a legal/financial requirement)