Page 1 of 1

Looking for a solution... please help

Posted: Sun Jan 08, 2006 1:53 pm
by will83
Hi,

I have 2 mysql tables in my database.

One table I have insurance types (ie Pet insurance, Motor Insurance etc)

And in the other table I have Insurance Company profiles.

Now an insurance company may be able to provide more than one type of insurance so I want to be able to reference more than one insurance type to a companies profiles.

Can anyone think of a method to do this?? I'm thinking along the lines of adding each insurance type's id to that company's profile but thats where i'm stuck.

Many thanks

Will

Posted: Sun Jan 08, 2006 1:55 pm
by timvw
So a company can have many types, and a type can be offered by many companies, this is what we call a n - m relationship. It is solved by adding another "link" table so you end up with 2 1-n relationships.

companyinsurance ( company_id, insurance_id)

Posted: Sun Jan 08, 2006 2:03 pm
by Weirdan
this is a classic example of many-to-many relation. Such a link in relational databases is expressed using additional table which maps company to its services/products:

Code: Select all

Company:
id | name
----------------
1  | Microsoft
2  | IBM
3  | Intel

Service:
id | name
----------------
1  | Software
2  | Hardware

Company_Service:
id | company_id | service_id
------------------------------------
1  | 1          | 1         
1  | 2          | 1         
1  | 2          | 2         
1  | 3          | 2
Here Microsoft offers only software, Intel offers only hardware while IBM offers both (thus it has two rows in Company_Service table

Posted: Sun Jan 08, 2006 2:10 pm
by will83
Thank you both of you,

Fully understood.

Will