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

[Philip S. <ph...@st...>]

Jesse,

How would you go about storing attributes for the products?  Let's say you have
three product types:

- books
- CDs
- electronics

Each of these categories could have many attributes, and to make matters worse,
each one can have different attributes.  For example, a book would have an ISBN
number, which a CD wouldn't have.  The electronics might have a color attribute,
which CDs and books wouldn't have (at least there would be no point in storing
this attribute).

I've run into this situation a few times and have never found an ideal
solution.  The only solution I see is to have lots of attribute columns in the
products table and for every product a bunch of these would have null values
since certain attributes wouldn't be applicable.

Anyway, I would just like to hear how others have solved this problem in the
past since it must be a pretty common one.

Thanks
Philip


Jesse Swensen wrote:

> 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
>
> _______________________________________________
> Phplib-users mailing list
> Php...@li...
> https://lists.sourceforge.net/lists/listinfo/phplib-users