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.