Little help with database design (ecommerce, mysql)

[eluser]Jondolar[/eluser]
I just added this feature to my shopping cart. My approach was to create an attribute table for the "attribute type" and "attributes" and an attribute_product table for the many <-> many relationship and an attributes_attributeproduct table to hold the individual attributes for each product (which allows each product to be found under multiple attributes). Each product can have multiple attributes and each attribute can have multiple products. The key for me was that an attribute had a parent categoryid so each product had to fall under that parent category (be a child/sub-child).

An attribute type would be Memory, HD Size, Ram Size, etc. An attribute would be 512MB, 1GB, 2GB (for memory as an example). So the attributes_attributeproduct table has an entry for every attribute that that product has for every attribute type.

Hmm, this was actually easier to implement than to describe.

Key points:
You may want your products in sub-categories to have the same attribute types as parent categories in some cases.
You may want a product to have multiple attributes (product may come in multiple colors or have options for adding more memory so you will want to find the product no matter how much ram they chose).
You may want the search to allow for multiple attributes to be selected per attribute type. Best Buy does not do this but I found it a very nice addition.

Good luck with your project.