Database design question

Question

Database design question

danny4499

Grasshopper

Points: 14
More actions
July 19, 2005 at 6:53 am

#165421

I have a spec for 3 web pages that will be used to obtain insurance data. One page will be for contents which will hold insurer,cover from and premium payable. Another page will be buildings which will hold insurer, cover from and premium payable (plus some additional fields). Another page will be critical illness which will be insurer, cover from and premium payable (plus some additional fields). What would be the best way to design the database, have separate tables for the above (as different data is held for each type of insurance) or have one table insurances which would make writing reports for insurances a lot easier?

Thanks
Danny

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

I think listing the additional fields would help...

If different data is held for each type of insurance then you would be better off having different tables for each type as well as 3 different tables to store the "details" (name, address etc...) of each insurer...

It all depends on what you need to store....if there are common data between the different types then it may make sense to combine them under one table...again depends on what the fields are...

**ASCII stupid question, get a stupid ANSI !!!**

danny4499 Grasshopper Points: 14 More actions · Answer 2

I was going to create a different table for each, as each type of insurance has common as well as different fields. But if you had to create a report on all insurances, it would be difficult as they are in seperate tables. You would have to do a union. So it may be better to hold it all in one table

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

Again - everything hinges on the common fields and separate fields...

eg: You could always have one master table for the common data (insurer, premium, from, to...whatever) with a masterInsurerID which would be 1, 2 or 3 depending on type (building, critical illness...) and an individual insurer ID for each of the different types...

that way you could query either the individual types or all of them if need be...

**ASCII stupid question, get a stupid ANSI !!!**

PhilM99 SSCrazy Points: 2214 More actions · Answer 4

I'd suggest using different tables:

There are more things making the data different than similar
The number of tables, that is the kinds of insurance, is static. Once it is set up, it's done
Use a good relational design as someone suggested so that insurers are referenced by their number. Index the joins.
The web pages that address the tables are different for content insurance, building insurance, etc. They can thus each address their own table.
Summarizing the insurance coverages by insurer, or by insured, or whatever can be achieved by unioning the tables as suggested but you can probably do it once based on the user's filters, and using asp.net caching you can store various combos of the data thus reducing the overhead. the resulting virtual table can be requeried at length with minimum overhead (the data is starting to grow old, but insurance moves slowwwwwwwllllllllyyyyyyy)

Mike Davis-147528 Valued Member Points: 55 More actions · Answer 5

I agree with sushila, using a master table that holds the common elements and child tables to hold the specific information.

Using unions when it is over 1000 records or so become performance issues.

Today your talking about premiums, tomorrow it maybe claims. It could grow rapidly.

We used the union approach and within a year had to move it to the master table approach.

MikeD

PhilM99 SSCrazy Points: 2214 More actions · Answer 6

Unions bad, joins good
Keep the logic out of the programming. That lets the SQL server operate on a set basis. So I would never define a multi-purpose table (say where auto insurance and building insurance and illness insurance were in the same table and they were differentiated by a table code or something like that)
It strikes me that the original question didn't tell us whether this was a collection of large numbers of insureds and their actual coverages, or a collection of a large number of insurers and the policies they offer.
Insured (name address blah blah)
-->Building coverage (address, premium, insurer code) <--insurer
-->Contents coverage (amounts, deductibles, named valuables, insurer code) <--insurer
-->Illness coverage (amounts, named persons, named illnesess, insurer code) <--insurer
Or simply:
Insurers (name address, number) <-- Coverages Offered (premiums etc)