Hi

I'm soon to be developing a database to store information on electronic products.

There are numerous categories for these products (digital camera, laptop pc, graphics card etc.) and each category will have their own set of attributes that needs to be stored for each item.
E.g. a digital camera may store attributes about number of megapixels, memory card type, camera type (SLR, compact etc.) whereas a laptop would require attributes such as processor manufacturer, ram, hard disk size etc.

These attributes will allow the user to browse through the category then filter by a number of attributes, so for instance they may click:

Digital Cameras
-Memory Type = Secure Digital
--Camera Type = Compact
---Megapixels = 6

Or they could choose:

Laptop
-Memory: 1GB
--Hard Disk: 20GB-40GB
---Processor Type: AMD Athlon 64

The products themselves need to be stored in a separate table:

Products
ProductID
Manufacturer
AverageRating
NumberOfVotes
Category
etc.

I do not want to hardcode the attributes into the database as I need to be able to add different attributes at a later date.

I was thinking something along the lines of:

Products table (as described above)
Categories (for storing details about a product category)
Attributes:
- AttributeID
- Category (what product category this attribute applies to)
- Name (E.g. "Processor Type")

AttributeValues:
- AVID
- Attribute
- Product
- Value (E.g. "AMD Athlon 64")

This would allow a product to reside in a category but not require any attributes or only have details for certain attributes (and not appear in results filtered by attributes it doesnt have).

This design seems to be sensible, but how would I go about querying it.

I'd need to do something like (N.B. this is pseudo-SQL):
SELECT productID, productName (etc.) from products WHERE [product ID has certain attribute values in attributevalues table] AND category=[categoryID]

Obviously I could do this rather inefficiently using multiple queries in PHP such as (again, pseudo code):
Code:
$attributepairs = {616="AMD Athlon 64", 305="1GB", 24="40GB"}
$results = SELECT productID, productName [etc] FROM products WHERE category=[cat. ID]
for each ($result) in $results {
     $matchallattributes=true
     for each ($attribute) in $attributepairs {
           if ($matchallattributes=true) {
                if (numberOfResults(SELECT * FROM AttributeValues WHERE product=$result.product, attribute=$attribute.name, value=$attribute.value) == 0) {
                      $matchallattributes = false
                }
            }
      }
      if (matchallattributes)
           printProduct
}
But this seems horribly inefficient.

I'll be using MySQL to store the data (or possibly postgreSQL if MySQL doesn't support what I'm trying to do here).

Do you have any input on the design or if the design is OK, do you have input on how to query this?