mgm_03 Posted November 18, 2003 Share Posted November 18, 2003 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 !! Link to comment https://forums.phpfreaks.com/topic/1389-need-advice-for-databasetable-planning/ Share on other sites More sharing options...
Barand Posted November 18, 2003 Share Posted November 18, 2003 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] Link to comment https://forums.phpfreaks.com/topic/1389-need-advice-for-databasetable-planning/#findComment-4602 Share on other sites More sharing options...
mgm_03 Posted November 19, 2003 Author Share Posted November 19, 2003 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!! Link to comment https://forums.phpfreaks.com/topic/1389-need-advice-for-databasetable-planning/#findComment-4615 Share on other sites More sharing options...
Barand Posted November 19, 2003 Share Posted November 19, 2003 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. Link to comment https://forums.phpfreaks.com/topic/1389-need-advice-for-databasetable-planning/#findComment-4619 Share on other sites More sharing options...
mgm_03 Posted November 19, 2003 Author Share Posted November 19, 2003 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!!! Link to comment https://forums.phpfreaks.com/topic/1389-need-advice-for-databasetable-planning/#findComment-4632 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.