What is the recommended design for the following needs:

I have a "products" table that consists of field id, name and type.
I also have a attributes table that consists of all attributes for any and all products we have. I also have a attributes-join-products table, which joins up which attt can belong to which prod. My only issue now is. How/what is the best structure to store the actuall "details" of the product attributes?

Thanks

CG

If I understand the question, the answer is to create a one to many relationship between the attributes-join-products table and a product attribute details table.

If I understand correctly, the proposed table design is,

Products(Product_id, product_name)
Attributes(Attribute_id, attribute_name, Product_ID)

Product_Details(Attribute_ID, attribute_value)

I would not recommend this design, on the basis that "attribute_value" is restricted to a single datatype. As an alternative, I would create Super / Sub type entities.

ok thanks....good point on the single datatype....please explain a super / sub type example, lets see if I understand...

Thanks again.

Products (product_id, common_attributes, type)

ProductA(product_id, specific_attributes)
ProductB(product_id, specific_attributes)

I would recommend enforcing the constraint that a product_id can exist in at most one sub entity.

Do you have "real world" experience with this design? If so, have you any information about the performance in LARGE environments (>50.000 products and many > 100 subtypes)? Is the performance better than the more traditional Product/Attribute model?

Regards,
Ronald

I am curious to see the performance as well, we will have > 50000 products and > 100 'types'....