Many to Many Index

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
dstefani
Forum Contributor
Posts: 140
Joined: Sat Jan 11, 2003 9:34 am
Location: Meridian Idaho, USA

Many to Many Index

Post by dstefani »

Hello,

I have a table of possible chioces of services offered.
A contractor can select from this list services that he offers that will be used as search critria. I need to tie the ID from the service to the ID of the contractor.

What I have started to do is build a table with two fields, contractor_id and service_id, then when I search, I would search "SELECT contractor_id FROM lookupT WHERE service_id = '$_REQUEST['service_id'] (not $_REQUEST of course), but you know what I'm trying to do.

My question is, how should I index the fields in the lookup table?
Neither is unique. Many contractors to Many Services.

Am I going about this right?

Thanks,

dstefani
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

many to many is solved by adding a "help entity" in a relational datamodel

[contractor] n ------- m [service]

[contractor] 1 -------- n [servicecontract] n ------ 1 [service]

Code: Select all

SELECT * 
FROM service AS s
INNER JOIN ON servicecontract AS sc ON s.service_ID=sc.service_ID
INNER JOIN ON contractor AS c ON c.contractor_ID=sc.contractor_ID
WHERE ....
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

tim, that's exactly what dstefani implemented.

dstefani, if any contractor is allowed to offer particular service only once ( i.e. "Jim offers car repair service, he can't offer two car repair services" [perhaps it sounds funny :)] ) I would go with unique index over two fields
Post Reply