SQL Server Performance Forum – Threads Archive

Normalization vs Query performance

Hi! What would be the best approach for this:
–Table X
Code
PriceType1
Price1
PriceType2
Price2
PriceType3
Price3 –Table Y
CodeY
CodeX
PriceType (either Price, Price1 or Price2 on TableX) so i have this query SELECT y.CodeY,
CASE PriceType
WHEN PriceType1 THEN Price1
WHEN PriceType2 THEN Price2
WHEN PriceTYpe3 THEN Price3
END PriceValue
FROM TableY y JOIN TableX x ON x.Code=y.CodeX I know tableX seems not normalized but it is like so because pricetype will never exceeds 3. So the question, is it better to save the equivalent Price value than save the type and have a case expression in my sql statement? Instead of above structure I will have:
–Table Y
CodeY
PriceValue –in replacement of PriceType

Hi ya, I’d opt for the first design, the idea of redundantly carrying prices seems to be too scarey for my liking <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Having said that the second option would be required if you had to preserve CodeY’s price even if it was subsequently changed on tablex (e.g. when selling stuff the product has a price, but the customer’s order needs to carry the price paid, since the product’s price changes over time) There is a performance reduction, since it will need to read both tables, but if you have a clustered index on tablex.code you should be ok Cheers Twan

]]>