Results 1 to 5 of 5

Thread: Opinions Please

  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Opinions Please

    Hi, I have probably exhusted the topic of shapes etc... but I am still having a hard time determining the best solution for my problem:

    I have several products, each with several specific properties:
    Code:
    Double Tee
    -----------------------------------------
    Width | Height |Flange | Leg | Count
    
    Column 
    ------------------------
    Width | Height
    
    Round Column 
    -----------------
    Radius
    Now originally I wanted to create a scalable table structure, so with the help of several people on this site (and SQL Team) I have developed the following :
    tbShape
    ------------------
    ShapeID | Shape | XSectionFormula
    -------------------------------------------
    1 | Rect | Length X Width

    tbShapeAttributes
    ---------------------------------------
    fkShapeID | AttributeID | Attribute
    ----------------------------------------
    1 | 1 | Length
    1 | 2 | Width

    tbProduct
    ---------------------------------------
    ProductID | fkShapeID | Product
    --------------------------------------
    1 | 1 | Column

    tbProductAttributeValues
    --------------------------------------------
    fkProductID | fkAttributeID | Value
    ---------------------------------------------
    1 | 1 | 10
    1 | 1 | 10
    [/code]

    From the above table structure I was able to select a product
    and by obtaining the formula from the tbShape table, using a
    cursor, replacing the Attribute names in the formula with the
    attribute values from the tbProductAttributeValues table, using
    dynamic SQL, I am able to determine the cross section of any
    selected product.

    The Problem now is, what if I need to apply different functions to
    the data for any given product. This proves to be very difficult because
    the attributes for the product are not necessarily consistent.

    For Example, lets say the above was a slab 10 feet by 1 foot giving a cross section of 10 square feet. Because it is simple to get the cross sectional area, I can easily figure out the cubic feet of concrete used by multiplying the cross section by a length. But lets say the user want to get the cost / square foot? How is the application sure what attribute is the width of the product?

    I guess what I am getting at is why the structure below is not any better then the one above?

    Code:
    tbTemplateCategories
    ---------------------------------------
    CategoryID | Category
    
    tbTemplates
    ----------------------------------------
    TemplateID |  fkCategoryID | Template |
    -----------------------------------------
    
    tbDoubleTeeTemplates
    ------------------------------------------
    fkTemplateID | Width | Height | Flange | Avg. Leg Width | Leg Count
    
    tbWallTemplates
    -----------------------------------------
    fkTemplateID | Width | Height
    Now there would be a 1 - 1 relationship between the tbTemplates and tbDoubleTeeTemplates ON TemplateID - fkTemplateID. To add a new product, simple add the category, the new table, and then alter the Stored Procs which would use if() if else() statements based on the category to go to the appropriate template table.

    Also, now I can write any customized functions for any product without the worry of user mispelling an attribute between the formula and attributes, etc...

    Any opinions, thoughts on this would be appreciated!

    Mike B

  2. #2
    Join Date
    Feb 2004
    Posts
    134
    After a little research, I found that this is refered to as sub-typing. This seems to be a very logical approach to the scenerio I have outlined. Even for shapes, this should be the way to go rather then trying to create a shapes - shape properties 1:M relationship. It seems to be more sound, manageable, and mantainable. So are those three attributes worth the tradeoff of flexibility? I am not convinced the flexibility is even lost seeing how easy it is to add a category then a sub_entity table for the attributes?

    Mike B

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    Mike-

    We had a similar situation in our project and I tokk the exact same approach as yours. I have a tblProduct, tblAttribute, tblProductAttribute, tblProductAttributeValue. The challange was when the UI team asked me to return a product and all the attribute values in the same row. (The attributename should be the column name !!). The only way we could do it was through dynamic SQL. There was a lot of looping that goes on in the SP. I am not terribly pleased with this solution. While it gave us the flexibility of adding new products without changing the schema, there is a lot of performance hit we need to take that comes with it.

    That's just me.

    - cbarus

  4. #4
    Join Date
    Feb 2004
    Posts
    134
    Originally posted by sbaru
    Mike-

    We had a similar situation in our project and I tokk the exact same approach as yours. I have a tblProduct, tblAttribute, tblProductAttribute, tblProductAttributeValue. The challange was when the UI team asked me to return a product and all the attribute values in the same row. (The attributename should be the column name !!). The only way we could do it was through dynamic SQL. There was a lot of looping that goes on in the SP. I am not terribly pleased with this solution. While it gave us the flexibility of adding new products without changing the schema, there is a lot of performance hit we need to take that comes with it.

    That's just me.

    - cbarus
    That is what I am afraid of. I am wondering if the flexibility is worth it if we were to only add one product / ohhh, who knows. I have been with this company for 10 years and I have never seen a new product.

    Mike B

  5. #5
    Join Date
    Feb 2004
    Posts
    134
    Originally posted by sbaru
    Mike-

    We had a similar situation in our project and I tokk the exact same approach as yours. I have a tblProduct, tblAttribute, tblProductAttribute, tblProductAttributeValue. The challange was when the UI team asked me to return a product and all the attribute values in the same row. (The attributename should be the column name !!). The only way we could do it was through dynamic SQL. There was a lot of looping that goes on in the SP. I am not terribly pleased with this solution. While it gave us the flexibility of adding new products without changing the schema, there is a lot of performance hit we need to take that comes with it.

    That's just me.

    - cbarus
    Are there any calucluations with the the attributes of your products? How are these handled?

    Mike B

Posting Permissions

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