phplib-users

[Phplib-users] OT: Database Design Help

[Bob B. <bo...@iN...>]

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.

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

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

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

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

Yes, this is a dilemma.  I am sure there are lots of ways to solve this
problem and a lot depends on the way the data is being used.  The way you
suggest is probably the best for speed.  But, I can make a mess of a table
by having lots of extra columns.

Another way would be to create a table of attributes.  The columns would be
something like

Product_attributes
    product_id
    attr_type
    attr_value

It makes queries a little harder because you have to join in this table and
you may want to add other columns to better describe the attribute.  But
this is how I would do it.
-- 
Jesse


> From: Philip Strnad <ph...@st...>
> Date: Wed, 07 Nov 2001 22:28:54 -0800
> To: Jesse Swensen <ph...@sw...>
> Cc: Bob Bowker <bo...@iN...>, "php...@li..."
> <Php...@li...>
> Subject: Re: [Phplib-users] OT: Database Design Help
> 
> 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.

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

[Bob B. <bo...@iN...>]

Hi --

Jesse (and others) focused my attention and helped solve the original issue 
- how to organize the basic data - but I have also addressed this new 
problem of attributes ...

I've got 2500 products, 11 categories, 88 sub-categories, and 24 attributes 
... each product can belong to several sub-categories, and attributes can 
change daily.  I have tried to normalize the data as follows:

attributes         id,attribName,icon,ref_AttribsFieldName
categories         id,catName
subcats    id,subName,ref_catId
prods              prodnum,name,keywords
prods_details      prodnum,price,colors, ... description
prods_attribs      prodnum,attr1,attr2, ... attr24
prods_assign       prodnum,ref_subcatIdCsv

MySQL queries are straightforward and fast ... I'm not happy with the CSV 
list of sub-categories to which a product belongs in the prods_assign table 
- but for now, using MySQL's "like '%xxxx%'" and a few extra lines of PHP 
seems simpler than 88 more fields ...

The one time-saver I stumbled on quite innocently (no deep thought and 
planning here!) is the ref_AttribsFieldName field in table attributes - it 
contains the name of the corresponding field in prods_attribs, which makes 
both the query and the input form variable naming quite simple ...

BTW, all queries get moved into a sys_procedures table as soon as debugging 
is complete, so all those messy JOINs are removed from the PHP as soon as 
we know they're working correctly ...

The design requirements are driven by the fact that we are creating a 
Control Panel page for the client to manage his own database ... 
add/edit/delete products and add/edit categories, sub-categories, 
attributes, etc. etc.  Leaves us to write code, not do data entry at 15% of 
our programming rate!

Bob.

At 05:56 AM 11/8/2001 -0500, you wrote:
>Yes, this is a dilemma.  I am sure there are lots of ways to solve this
>problem and a lot depends on the way the data is being used.  The way you
>suggest is probably the best for speed.  But, I can make a mess of a table
>by having lots of extra columns.
>
>Another way would be to create a table of attributes.  The columns would be
>something like
>
>Product_attributes
>     product_id
>     attr_type
>     attr_value
>
>It makes queries a little harder because you have to join in this table and
>you may want to add other columns to better describe the attribute.  But
>this is how I would do it.
>--
>Jesse
>
>
> > From: Philip Strnad <ph...@st...>
> > Date: Wed, 07 Nov 2001 22:28:54 -0800
> > To: Jesse Swensen <ph...@sw...>
> > Cc: Bob Bowker <bo...@iN...>, 
> "php...@li..."
> > <Php...@li...>
> > Subject: Re: [Phplib-users] OT: Database Design Help
> >
> > 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.
>
>
>
>_______________________________________________
>Phplib-users mailing list
>Php...@li...
>https://lists.sourceforge.net/lists/listinfo/phplib-users

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

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

I agree that having all those columns in one table would get messy and wouldn't be a
great idea in terms of database design.  On the other hand, with the attributes
table each attribute value would have to be of the same data type.  In most
situations this would not be an issue but it's worth taking note of.  I'd be
interested to see how amazon.com handles this sort of stuff (or any other big
e-commerce site for that matter), but I don't think we'll come across this info
anytime soon.

Philip


Jesse Swensen wrote:

> Yes, this is a dilemma.  I am sure there are lots of ways to solve this
> problem and a lot depends on the way the data is being used.  The way you
> suggest is probably the best for speed.  But, I can make a mess of a table
> by having lots of extra columns.
>
> Another way would be to create a table of attributes.  The columns would be
> something like
>
> Product_attributes
>     product_id
>     attr_type
>     attr_value
>
> It makes queries a little harder because you have to join in this table and
> you may want to add other columns to better describe the attribute.  But
> this is how I would do it.
> --
> Jesse
>
> > From: Philip Strnad <ph...@st...>
> > Date: Wed, 07 Nov 2001 22:28:54 -0800
> > To: Jesse Swensen <ph...@sw...>
> > Cc: Bob Bowker <bo...@iN...>, "php...@li..."
> > <Php...@li...>
> > Subject: Re: [Phplib-users] OT: Database Design Help
> >
> > 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.

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

[Manuel A. <mpa...@so...>]

At 06:03 07/11/01 -0800, you wrote:
>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=
=20
>several subcategories ...

I'd try this:

product: product_id + category_id + product_whatever_1 + ... +=20
product_whatever_n

category: category_id + category_whatever_1 + ... + category_whatever_n

subcategory: subcategory_id + category_id + ...

product_subcategory_assoc: product_id + subcategory_id

to search by attributes of a product: search in product table
to search by category: same as above
to search by subcategory: search in product_subcategory_assoc

being the number of categories and subcategories rather small, perhaps you=
=20
could hardcode the table somewhere in your code, but i don't know if they=20
need to be updated.

in addition, you can implement the product_subcategory_assoc association as=
=20
a "belongs_to" column in the product table of type SET. But i wouldn't do=20
that if the number of subcats a product belongs to is big.

Manuel Aristar=E1n
Bah=EDa Blanca, Argentina
mpa...@so...