Re: [Phplib-users] OT: Database Design Help

[Jesse S. <ph...@sw...>]

You will need 3 tables

Products
    product_id
    product_name

sub_category
    sub_cat_id
    sub_cat_name
    cat_id

Category
    cat_id
    cat_name

So far this is pretty straight forward.  But you need one more table to make
the many to many join.

Product_sub_category
    product_id
    sub_cat_id


Now if you want to find all the products of sub category you would do
something like:

SELECT
    p.*
FROM
    product_sub_category psc,
    sub_category sc
    product p
WHERE
        sc.sub_cat_name = 'PURPLE'
    AND sc.sub_cat_id = psc.sub_cat_id
    AND psc.product_id = p.product_id

Or maybe you want all the products of a given category, then:

SELECT
    p.*
FROM
    category c,
    product_sub_category psc,
    sub_category sc
    product p
WHERE
        cat_name = 'RED'
    AND sc.cat_id = c.cat_id
    AND sc.sub_cat_id = psc.sub_cat_id
    AND psc.product_id = p.product_id


Now you want all the sub categories a given product is in:

SELECT
    sc.sub_cat_name
FROM
    product p,
    product_sub_category psc,
    sub_category sc
WHERE
        p.product_name = 'KEYBOARD'
    AND p.product_id = psc.product_id
    AND psc.sub_cat_id = sc.sub_cat_id


And so forth....

-- 
Jesse
> From: Bob Bowker <bo...@iN...>
> Date: Wed, 07 Nov 2001 06:03:43 -0800
> To: <Php...@li...>
> Subject: [Phplib-users] OT: Database Design Help
> 
> Hi --
> 
> My apologies for an OT subject, but I figured this group of people was the
> most logical place to seek help ...
> 
> I'm setting up a database with the following parameters:
> 2500 products
> 11 main categories
> 88 subcategories
> Each subcategory belongs to a single category, and each product belongs to
> several subcategories ...
> 
> ... and I can't wrap my head around the best layout.  Searching by name,
> product number, keywords, etc., is trivial, but I will need to search for
> and group products on the following terms:
> by category
> by subcategory
> 
> How do I normalize this data ...?
> 
> Thanks --
> 
> Bob.
> 
> 
> _______________________________________________
> Phplib-users mailing list
> Php...@li...
> https://lists.sourceforge.net/lists/listinfo/phplib-users