Help Managing a Database

[innovations]

Okay... I have an interesting setup and I am unshure of how to set up my db so that I can query my records correctly while keeping the db normalized.

Specs:
I have 4 different kinds of products (single sermons, sermon series, books and music CD's) and two of the items (single sermons and sermon series) are available up to 4 formats (CD, cassette, DVD and VHS).

I would like to categorize my products by speaker and by their main category and subcategory.

Problem:

Scenario 1) If I create a record in a products table for all of the different products and their formats, the single sermons and sermon series will be duplicated records except for their model #.

Scenario 2) If I create a table for each type of product, to display each product i would have to loop through all of the categories, then all of the subcategories and finally the product itself. That would be fine but the product is in 4 different tables.

Scenario 3) I haven't come up with a scenario 3 yet, but I have spent a lot of time trying with #2.

I definately need some help... real badly!

[nemonoman]

Actually, you're doing a pretty good job so far. You are right to think of the entities in this way:

Category, Subcategory, Product, Model. There are variations that might be suggested, but that's a pretty good start. Your database will be fairly normalized with that approach, and that is a good thing.

The coding to do the looping that you do describe isn't hugely hard.

The question you want to ask is:

What will the users want to do with this information?

Since you know a pretty good way to STORE the data, the next question is what is a pretty good way to SHOW the data?

[innovations]

What I would like to do is have a menu on every page of my site that has the following buttons:

• Category 1

• Category 2

• Category 3

• Category 4

• Category 5

• View by Author/Speaker

• View All Products

If a user clicks on one of the categories, I would like for the next page to display all of the products listed under their subcategory. For instance, if someone clicked on Category 1 this is what it would look like:

Category 1

Subcategory 1

1. product

2. product

3. prodct
   [/list=1]

   Subcategory 2

   1. product

   2. product

   3. prodct
      [/list=1]

      Subcategory 3

      1. product

      2. product

      3. prodct
         [/list=1]

         Due to the fact that my products are in four different tables (single sermons, series sermons, books, and music) I got this to work for retrieving the products out of one table but not all four. In order to list the subcategories and list the products associated with each subcategory, I had nest two loops:

         Psuedo Code

         select all subcategories of category id in url string {
            echo a subcategory
            select all products of that subcategory {
               echo a product
            }
         }

[nemonoman]

Yes, you are stuck doing loops. It's not a bad thing.

If you think about it, you can do this 2 ways:

You can do one query that retrieves all categories and subcategories and products:

SELECT category.name, subcategory.name, product.name
FROM product
INNER JOIN subcategory ON product.subcategoryID=subcategory.ID
INNER JOIN category ON
category.ID=subcategory.categoryID
ORDER BY category.name, subcategory.name, product.name

This would return like
Category 1 Subcategory 1 Product 1
Category 1 Subcategory 1 Product 2
Category 1 Subcategory 2 Product 1
Category 1 Subcategory 2 Product 2
Category 2 Subcategory 1 Product 1
Category 2 Subcategory 1 Product 2
Category 2 Subcategory 2 Product 1

etc.

Then loop through the returned rows, parsing them with PHP to create your navigation structure

OR you can do query on category, and for each returned row nest another query and return its subcategory, then query all products for each subcategory, etc.

There are other variations.

It would be nice if MySQL had a 'CONNECT BY' contstruct like Oracle does, that does these sorts of part-explosions more automatically -- but then it would be nice if Oracle were free, too.

[innovations]

Thank you very much! I will give that a try and see what I come up with.

Unfortunately, I will not be able to work on this until Monday so please remain subscribed to this thread so that when I post back on Monday you will be aware of it.

Thank you soo much!!!

Oh, one thing: Can I nest three loops together for category, subcategory and products? I was able to nest two by creating a list for each query but when I tried three it didn't work.

[nemonoman]

You can definitely nest loops in any number;

How I would do this:

SELECT category.name, subcategory.name, product.name
FROM product
INNER JOIN subcategory ON product.subcategoryID=subcategory.ID
INNER JOIN category ON
category.ID=subcategory.categoryID
ORDER BY category.name, subcategory.name, product.name

This would return like
Category 1 Subcategory 1 Product 1
Category 1 Subcategory 1 Product 2
Category 1 Subcategory 2 Product 1
Category 1 Subcategory 2 Product 2
Category 2 Subcategory 1 Product 1
Category 2 Subcategory 1 Product 2
Category 2 Subcategory 2 Product 1

$oldcat='';
$oldsubcat='';
$oldproduct='';

$result=mysql_query("SELECT category.name, subcategory.name, product.name 
FROM product 
INNER JOIN subcategory ON product.subcategoryID=subcategory.ID 
INNER JOIN category ON 
category.ID=subcategory.categoryID 
ORDER BY category.name, subcategory.name, product.name");

while($row=mysql_fetch_array($result)){
    if($oldcat<>$row[0]){
           echo "   $row[0] <BR>";
           $oldcat=$row[0];
    }
    if($oldsubcat<>$row[1]){
           echo "&nbsp;&nbsp; " //indent
            echo " $row[1] <BR>";
           $oldsubcat=$row[1];
    }
   if($oldproduct<>$row[2]){
           echo "&nbsp;&nbsp; &nbsp;&nbsp; " //more indent
            echo "  $row[2] <BR>";
           $oldproduct=$row[2];
    }
}