Complex Multiple Attribute Relationships

[physx]

First of all, here is what I'm running:

 

MySQL: 5.0.81

PHP:         5.2.9

 

Here is what I am handling: 

* I have a list of possible service categories (A/C repair, Janitorial... etc... about 272)

* For each category, there are several vendors that perform that particular service.

* I have a list of customers that will use these various services

* Each customer is assigned a "zone #" that describes their geographic location

* Each vendor can serve multiple zones and multiple categories, and any combination.

 

What is the best way to set the vendor tables up, to allow for the multiple category associations and multiple zones it can service?

 

Example:

Vendor A offers Painting and a/c repair services. This vendor offers his Painting service for Santa Monica, CA (zone 6 ) AND Pasadena, CA (zone 8 ),BUT he offers his a/c repair service ONLY to Pasadena, CA (zone 8 )....

 

Please help... I am relatively new to mysql.

 

Thanks

 

 

[Zane]

ONE I would have a table for all the services offered...by vendors

- service_id

- vendor_id

- zone_id

 

TWO I would have the actual services table

- service_id

- service_name

 

And the zones table

- zone_id

- zipcode

//I would use the zipcode to create a mile range..for example Zone 3 could be 90210 .. Beverly Hills and anything 5 miles from that center point.

 

And of course the vendor table

- vendor_id

- vendor_name