[lukkyjay]

I'm a newbie to PHP/MySQL and I'm looking to setup an insurance quote engine with multiple companies. The input variables to pull the data from the database will be zip code, number of employees, employee age, employee gender, family status (single, subscriber & spouse, family, etc). I would like to pull the best rates in order of price from whichever companies have the best prices. Once I have the rate information, what do you think would be the best way to setup the rate tables in my database to accomplish this?

Being a rookie, I originally thought to have a table for each company. But maybe it would be easier to have a table for each age range (25-29, 30-34...) with the corresponding companyid?

Sorry, I know this is probably a pretty simple concept. But I can't quite get my head around all the options. Thanks in advance for your help!

[healdem]

why would you need to differentiate between insurance comapnies such that each insurer would have a separate table
or
why would you need to differentiate between age ranges such that each age range would have a separate table

an insurance company sahres the same attributes as other insureres, they all have a web page, they all offer insurance, they all have contact details, effectively there are one entity type. In many cases seemingly different insurers are in owned by the same company, writing the insurance in the same market, but branding themselves differently.

Starting out on the entity design / data model can be a dauitng task at first. Rudy (R937) has a great piece on his website by Paul Litwin on realtional design which may be worth a read

if you were to design your system using age ranges then ow do you propose to handle a customer who crosses an age range, say last year they were 29, this year 30.... or they are asking for a quote right now(they are 29), but by the time they actually come back to buy the insurance they are 30.

A table should contain the data of the same entity type, you can always break out the minor differences in a sub table.
so in this case Id expect to see something like

InsuranceCompany .. containing details of the insurance companies
InsuranceTypes ..defining waht types of insurance quotes you can offer
InsuranceCompanyInsuranceTypes .. associating each insurance company with the types of inuranc that your customers can get quotes from you. Effectively this holds details of the InsurtanceCompany priamry key and the InsuranceTypes primary key. there may be multiple insurance types for one company, there may be multiple companies for one insurancetype

CustomerTable .. details of the customer.. but it may be better to call this a person table, as people may request a quote,, but never actually buy anything from you. so you want to store everything that is pertinent to the person. things such as name, address, telephone, email.........

it may be tempting to stuff details of insurances bought by the customer in this table.. but I'd resist it. after all a customer may have multiple insurance policies form you, you may have a requirement to store details of the insurance for more than one year.

you may want some mechanism to track any dealings you have had with a person. for example you may want to know what their history is on visiting your website.. say they looked at home (building) and contents insurance, but didn't buy, then it may be worthwhile sending them an email in about a year. you may want to take notes of any telephone conversations you have had with a customer or potential customer
you may want to record details of information a customer has provided for the quote, the insurance company will undoubtedly want those details if an insurance is bought

a quote may have a limited life, ie expires in n days, you almost certainly should record specific details about the quote.

HTH

[lukkyjay]

Thanks healdem,
If I understand you correctly - one table (relation) for all the insurance companies? I can see how this would be a good idea, but it would be tough to change the rates for company1 if the rates for company2, company3, and company4 need to stay the same, wouldn't it?