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.