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:

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?

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 BAfter 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|||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|||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|||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