Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    Canada
    Posts
    6

    products , attributes and details

    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

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Jan 2004
    Location
    Canada
    Posts
    6
    Originally posted by r123456
    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.

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Feb 2004
    Posts
    1
    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

  7. #7
    Join Date
    Jan 2004
    Location
    Canada
    Posts
    6
    I am curious to see the performance as well, we will have > 50000 products and > 100 'types'....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •