Page 1 of 1

Many to Many Index

Posted: Thu Nov 04, 2004 12:28 pm
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

Posted: Thu Nov 04, 2004 1:04 pm
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 ....

Posted: Thu Nov 04, 2004 3:51 pm
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