Jump to content

Archived

This topic is now archived and is closed to further replies.

mgm_03

need advice for database/table planning

Recommended Posts

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 !!

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

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!!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!!!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.