Computed column for simple multiplication?

I making开发者_如何学Python a simple inventory application. In a table i have columns Quantity, Price and TotalPrice. Which is better, make TotalPrice a Computed Column as Quantity*Price or do this calculation in my application and save it in TotalPrice?

One rule in database design is (in general): do not store information that can also be retrieved easily.

In your case I would not create a computed column, but do the multiplication during retrieval. Either by directly selecting price * quantity or by creating a view that contains this computation.

The view has the added benefit, that you can easily change the formula (e.g. including VAT) without the need to change all statements in your application.

If you update price or quantity you are forced to make a 2cd update on TotalPrice. What if another developer forgets the 2cd update? Now you have bad data.

You could have a trigger keep it in sync, but the possibility for bad data still exists. In some RDMS's the triggers do not fire for each row updated in a set-based update.

But sometimes it's needed to store calculated fields. If the table is BIG and you plan on searching the calculated field. (you must store the data to index it). In some designs you not only store the calculated fields, but aggregate calculations as well.

It's not always wrong to store calculated fields, just make sure you know the trade offs.

* EDIT * Another point. If you calculate "Amount_Paid", the actual amount the customer paid will be lost when you change the calculation formula.

Design to solve the problem. If that means breaking a rule of thumb, then break some thumbs. * END EDIT *

why store a computed column when you can do the computations in the query?

SELECT (Quantity * Price) as TotalPrice FROM my_table