Insurance policy database

[santinimatias]

I am starting designing a database for insurance sales.


The design has to be a many-to-many relationship since:
- the client can have multiple insurance policies with the same company
- a company have many policies
- the policy can have many people covered (the client, the insured and the beneficiary)

The issues I have is that the policy can have multiple people involved: the client that purchases the insurance, the insured, and the beneficiary. All three can be the same person, or they can be different people.
So I though of two ways to design this database:
1. three tables: people (with their info), company (and their info), and people-company (with PeopleIDFK, CompanyIDFK, client, insured, beneficiary)
2. Splitting people-company into two tables where the role of the people is in one table.

Please check the two options I hand sketch, and please advice. Thank you!

[orange]

There are a number of free data models related to Insurance at http://www.databaseanswers.org

Business rules at http://www.databaseanswers.org/data_...kers/facts.htm
Model at http://http://www.databaseanswers.or...kers/index.htm

[santinimatias]

Thank you for the response. however, those data models are for single companies, or brokers that work for one company. The model options I uploaded are for a broker that sells insurance packages for multiple companies. I would appreciate if you can pinpoint which of the two models makes more sense.
Thank you again.

[orange]

I agree those models may not suit your needs. They are generic and meant to show concepts and most often need some customization.

Have you tried writing a paragraph or two about the business of Insurance Sales as it applies to you?

This is a great exercise to clarify your thinking and resolving "gray areas" where definitions are similar but not always the same. There is a good tutorial on how to work from business description to a properly designed data base at http://www.rogersaccesslibrary.com/T...lationship.zip

A few thoughts from 30000 feet:
I would try to define Client, Insured and Beneficiary as Roles.
(in my view the Client buys the Policy --{assumption that Person A can buy a policy that Insures person B})
In each Policy these 3 roles exist. They may be populated by 1 to 3 people.
Each Person (people) would be identified uniquely in a separate table.
A Policy is provided by an InsuranceCompany to a Client for a named Insured and a named Beneficiary.
A Client may have 1 or many Policy
A Broker may represent 1 or many InsuranceCompany
An InsuranceCompany can have 1 to many PolicyTypes.
A Policy is for 1 PolicyType.


Hope this is helpful, and welcome to the forum.

[santinimatias]

thank you so much... this is what i was looking for.
I will check the link you included...
Thank you again.