Jump to content


Photo

need advice for database/table planning


  • Please log in to reply
4 replies to this topic

#1 mgm_03

mgm_03
  • Members
  • PipPipPip
  • Advanced Member
  • 49 posts
  • LocationFort Worth, TX

Posted 18 November 2003 - 07:36 PM

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 !!
Guru?...in my dreams!
Sorry Everybody

----------------------

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 18 November 2003 - 08:08 PM

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]
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 mgm_03

mgm_03
  • Members
  • PipPipPip
  • Advanced Member
  • 49 posts
  • LocationFort Worth, TX

Posted 19 November 2003 - 02:13 AM

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!!
Guru?...in my dreams!
Sorry Everybody

----------------------

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 19 November 2003 - 08:12 AM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 mgm_03

mgm_03
  • Members
  • PipPipPip
  • Advanced Member
  • 49 posts
  • LocationFort Worth, TX

Posted 19 November 2003 - 08:58 PM

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!!!
Guru?...in my dreams!
Sorry Everybody

----------------------




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users