Re: Implementing product attributes

"Fernando" <spamers_at_must.die> wrote in message news:s8u6dsgnvpgrf3q4imhpnhdlirlrs248iv_at_4ax.com...

> Hi!
 

> I have a datbase with n products. Each of these products
> belongs to 1 and only one cathegory.
 

> For example: I have 10 different beers, 5 magazines and 30
> books. So, every beer belongs to the cathegry "beverages", while every
> book belongs to "Books", etc...
 

> The attributes of a product depends on which cathegory it
> belongs too, and are usually very different from cathegory to
> cathegory. While the list of attributes is a function of the
> cathegory, the values of those attributes is a function of the
> product.
 

> What's the best way to model this thing? O:-) Right now I
> have a products table and a cathegories table, but how do you handle
> the product attributes, while enforcing data integrity (never letting
> a book to be assigned to "beverages", etc...)? O:-)

I don't know if this is always the *best* way, but many projects have used sub-tables to model this. It mirrors an object hierarchy, sort of. Say we have an Product table, with Book and Beverage sub-tables. Each product has a Description, say, while books and beverages each have attributes unique to books and beverages.

Product
ID Description
-- -----------

1     The Satanic Verses
2     ToothRot Cola
3     Tommyknockers, The

1            666     Rushdie, Salman
3            667     King, Stephen

Beverage
ProductID Liters Manufacturer
--------- ------ ------------
2 2.0 American Denture Association

You can determine what type of product a row in the Product table represents by seeing what rows have the same ProductID in the sub-tables. There may or may not be an additional field in Product that also contains type information. You'll need triggers to ensure that the same Product ID doesn't appear in both the Book and Beverage sub-tables, just like you'd need something similar if you rolled everything up into one table, like so, to make sure no book has a value in the Liters field, etc:

Product

ID  Description         Type  ISBN  Author            Liters  Manufacturer
--  -----------         ----  ----  ------            ------  ------------
1   The Satanic Verses  Book  666   Rushdie, Salman
2   ToothRot Cola       Bev                           2       American Denture Association

Either scheme gets unwieldy fast. I'd love to hear about alternatives!

--
Joe Foster <mailto:jfoster_at_ricochet.net>  Space Cooties! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!