question

ikramonly avatar image
ikramonly asked

Data Normalization

Hello, I have a table naming CropPurchase(Date,CropId,CustId,UnitPrice,Qty,Total). Here column 'Total' is calculated by multiplying 'Qty' with 'UnitPrice'. My question is that isn't it against the rules of normalization to maintain 'Total'? is it a good practice to remove this column and calculate on front end(C# app)? Or there is no issue to maintain it in the database? thnx in advance,
sqlnormalization
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If you can store it once and the application isnt having to recalculate everytime, to me that is always a good thing.
0 Likes 0 ·
Thank you @SQLShark :)
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Different options here. You could calculate it and store it - but any changes to the price or quantity, and you have to make sure that you force a recalculation. You could use a computed column - so it is 'stored' on each row, but any changes to the price or quantity are immediately reflected. Have a view over the table that calculates the total, and only reference the view not the base table. Use stored procedures to access data and not direct table access - then put the calculation in the stored proc. Purist approach is to not store anything that is derivable - but there's a trade off between this and performance like @SQLShark says.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks @Kev Riley for explaining :) :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.