Looking for a solution... please help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
will83
Forum Commoner
Posts: 53
Joined: Thu Nov 10, 2005 3:13 pm

Looking for a solution... please help

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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
User avatar
will83
Forum Commoner
Posts: 53
Joined: Thu Nov 10, 2005 3:13 pm

Post by will83 »

Thank you both of you,

Fully understood.

Will
Post Reply