Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MikeB
Constraint Violating Yak Guru
387 Posts |
 Posted - 2004-04-21 : 11:56:03
|
| This is a cross post with dbForums.comhttp://www.dbforums.com/t995021.htmlHi, 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 | CountColumn ------------------------Width | HeightRound 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 WidthtbShapeAttributes---------------------------------------fkShapeID | AttributeID | Attribute----------------------------------------1 | 1 | Length1 | 2 | WidthtbProduct---------------------------------------ProductID | fkShapeID | Product--------------------------------------1 | 1 | ColumntbProductAttributeValues--------------------------------------------fkProductID | fkAttributeID | Value---------------------------------------------1 | 1 | 101 | 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 theattribute values from the tbProductAttributeValues table, usingdynamic 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 tothe data for any given product. This proves to be very difficult becausethe 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 | CategorytbTemplates----------------------------------------TemplateID | fkCategoryID | Template |-----------------------------------------tbDoubleTeeTemplates------------------------------------------fkTemplateID | Width | Height | Flange | Avg. Leg Width | Leg CounttbWallTemplates-----------------------------------------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 |
|
|
|
|
|
|
|
Please, opinions wanted.