[thelist] RE: DB question: product attributes

Chris W. Parker cparker at swatgear.com
Mon Apr 29 17:30:01 CDT 2002


> I would go for a unique product ID for every value
> >  combination and keep
> >  it in the one table. That would be easy to set up and
> >  maintain, less 'logic'
> >  at run time, and would allow much easier addition of an out
> >  of stock flag.
> >  The fact that there are a limited number of products would
> >  mean there wasn't
> >  much overhead finding the correct record.
> >  Sorry it's not an eloquent solution but I tend to think in a
> >  linear fashion!
>
> Do you mean something like:
> id1 T-shirt, large, green
> id2 T-shirt, large, blue
>
> Because if this is what you mean, I HIGHLY recommend against
> it. Because if
> you have a separate product id for each combination, then if
> someone does a
> search for shirts, what they get is a list of all of the
> combinations.

i agree. if there are only 3 products with 2 attributes each, that might
not be so bad. but for example the site i work on has over 700 products
with some products having maybe 30 attributes. so assuming on average,
even 2 attributes for each product, that's 1400 items that the user gets
to sort through to find what they want. (although i wouldn't surprised)
i would assume that no one is so dumb that they would look through every
single product to find something rather than inputing some search terms
in a search. but still instead of having 10 products you could end up
with 90.

a solution would be to do it the "hard" way with more logic and create a
seperate table that contained the information for the attributes.

(someone please correct me if i'm wrong.)

products
--------------------
id (a unique identifier but not auto-incrementing)
name (name of product)
description (description of product)
price (price of product)


productAttribute
--------------------
id (a unique identifier, auto-incrementing)
productID (this is the id of the actual product, comes from the id field
of the products table)
type (this is the type of attribute, size, color, etc.)

productAttributeDetails
--------------------
prodAttrID (id of the product attribute from the productAttribute table)
description (this is the description of the type of attribute, i.e.
blue, large, etc.)


now some sample records...
(i'm trying to keep the line length really short so that it doesn't wrap
on the db example.)

products
--------------
id    name     description       price
--------------------------------------
br1   a brain  a brain of one    10.00
lm01  lemans   a fancy race     100.00


productAttributes
--------------
id    productID      type
--------------------------------------
1     br1            IQ
2     br1            Color
3     lm01           Car Type
4     lm01           Car Color


productAttributeDetails
--------------
prodAttrID        description
--------------------------------------
1                 100 - normal
1                 150 - really smart
1                 50000 - geniusx5000
2                 pink
2                 off white
2                 yellow
3                 BMW
3                 Mercedes Benz
3                 Jaguar
4                 White
4                 Silver
4                 Green



as for the SQL statement part of this, i'm no help.

(i think maybe this post was over-kill?)


chris.



More information about the thelist mailing list