need advice for database/table planning

[mgm_03]

In my application, there will be a few hundred different service categories and in some cases, sub-categories. Since any company can offer one or more services and sub-cats, logically, this means a many to many relationship with a mapping table.

 

However, I would like to maintain a separate table for each category because the idea of having, say, 300 categories and subcats all in one mapping table just feels like the wrong way to do it.

 

If I want to retrieve all the services company A offers, How do I write a SELECT statement that iterates through every service category table checking for the presence of company A\'s unique ID?

 

Any advice is appreciated !!

[Barand]

However, I would like to maintain a separate table for each category

 

Only one word to say about that - DON\'T, or you\'ll have to do several hundred separate queries.

 

Category

-----------

catid

category

 

Subcat

--------

subcatid

catid

subcategory

 

 

Service

----------

subcatid

companyid

 

Company

-----------

companyid

companyname

etc

 

[php:1:3daf3f8861]<?php

$cid = $searchid;

$sql = \"select c.category, sc.subcategory FROM

(service s INNER JOIN subcategory sc ON s.subcatid = sc.subcatid)

INNER JOIN category c ON sc.catid = c.catid

WHERE s.companyid = $cid\";

?>[/php:1:3daf3f8861]

[mgm_03]

hey man!

 

thanks...I would\'ve never thought to do this in a jillion years. I can\'t honestly say I understand it (being a dumbass noobie and all) but I\'ll read up on MySQL to understand the relations you defined in the query.

 

I don\'t suppose this query would also accomodate situations in which a category has no subcategory at all? I wonder if in those instances, I could just make a table for a category and subcategory with one entry in each table.

 

thanks again!!

[Barand]

You are right, in the model each cat must have at least 1 subcat so you would need cat=widget, subcat = widget where a cat had no subcats.

 

The services gives the many to many relation between company and subcat, letting you search for those companys offering a particular category or, conversely, the cats offered by a company, as above.

[mgm_03]

I appreciate the help and would be willing to offer a donation if you have Paypal. Alot of people on this board are very generous with their expertise but this particular solution solves a huge puzzle for me.

 

My database directory is for finding machine shops. In addition to offering many services, they also work with many types of materials. So, again, I have a situation of Cats (metals)(plastics)(etc) and Subcats(steel, copper, brass, etc.) ( ) ( )

 

It appears to be an identical table setup as we just reviewed. To retrieve all the Services and Materials a company offers, my initial thought is to concatenate both query statements with an \'AND\'. Or, just have 2 distinct queries.

 

Thanks, Mark

 

P.S. --------------------

If you can write a script that can remove G.Bush from the White House, you\'d be doing Planet Earth a huge favor!!!