[thelist] DB question: product attributes

[Warden, Matt]

On Apr 25, Erik Mattheis had something to say about [thelist] DB question:...

>I have a bunch of products, most of which have attributes. The
>attributes change nothing about the product other than that products
>with a particular combination of attributes may be temporarily out of
>stock.
>
>I set up the DB as a products table, a product attributes table and a
>product attributes lookup table ... this seemed the right thing to do
>as there's few products, few attribute names and a whole lot of
>attribute values:
>
>Product    attribute_name  attribute_value
>T-shirt    color           green
>T-shirt    size            XL
>steak      size            20 oz
>steak      cooked          well-done
>potatoes   color           red
>potatoes   cooked          french-fried
>
>I've just been getting drool on my keyboard past that point ... I
>want to avoid the CMS giving my client an option to make a well-done
>russet T-shirt available for purchase on her site and at the same
>time have all configurations of T-shirts have the same product_id, as
>well as all steaks, etc.

Well, sir, you've thoroughly lost me.

However, I've done this before and I'll show you how I've done
it. HOpefully that will help.

Product
-------------
Product_ID
Name
Price
...


ProductAttr
-------------
ProductAttr_ID
Product_ID
Name


ProductAttrValue
-------------
ProductAttrValue_ID
ProductAttr_ID
Value


sample values:


Product
+-----------------------------------------+
| Product_ID |     Name      | Price |... |
+-----------------------------------------+
|     1      |    T-Shirt    | 10.00 |... |
|     2      |     Steak     | 13.50 |... |
+-----------------------------------------+

ProductAttr
+-----------------------------------------+
| ProductAttr_ID | Product_ID |    Name   |
+-----------------------------------------+
|       1        |     1      |   Size    |
|       2        |     1      |   Color   |
|       3        |     2      |   Cooked  |
+-----------------------------------------+

ProductAttrValue
+-----------------------------------------+
| ..AttrValue_ID | ProductAttr_ID | Value |
+-----------------------------------------+
|       1        |       1        | Large |
|       2        |       1        | Small |
|       3        |       2        | Black |
|       4        |       3        | Well  |
|       5        |       3        | Medium|
+-----------------------------------------+

basically, you want a:

product 1----M attribute 1----M value

because a product can have many attributes and each attribute can have
many possible values.

that help?

--
mattwarden
mattwarden.com