5

I would like to know what do you think about persisting an "autocomputed property" to the database. For example I have these properties

decimal Price {get;set;}
decimal Tax {get;set;}
decimal PriceWithTax {get {return Price + Tax;}}

I think I need persist PriceWithTax. I can use this column, for example, in DB views. But I feel that this solution can lead to hidden bugs. What do you think?

Edit: Ok, I try to explain our real problem. We have a class "Order" which has collection of items - class ItemOrders. Class ItemOrder has property Price and class Order has property TotalPrice. And implementation of TotalPrice:

TotalPrice {get { return ItemOrders.Sum(i => i.Price); }}

And we need sorting, grouping in the database. So its not a bussiness rule but a "data rule".

5

The first question that you should ask yourself is whether this is a business rule or something purely related to data.

For example, taxes a person should pay every year depends on multiple complex business rules. Those rules are subject to frequent changes, so it is unwise to make a computed column containing such information. On the other hand, a day and a month of birth of the person extracted from the full birth date doesn't depend on business rules, and it's very improbable that it will change during the lifespan of the application; thus, creating a computed column for that looks fine.

In your example, it's difficult to say for sure without additional context, but I suppose that the price with tax is a business rule. What if there would be customers who shouldn't pay a tax? What if the tax would be different from case to case?

The second question to ask is whether storing the computed column would benefit your application. In my previous example with the birth date, it may make sense to store this additional information in a context where you should frequently search for persons based on their day and month of birth, independently of the year of birth.

In your case, if the price with tax is as simple as the addition of price and tax, why creating an additional column? How would it make either easier, or faster your application?

  • 1
    A use case where uses want to find all orders within a certain total amount would probably be enough of a reason to store the full order total. – Andy Mar 17 '14 at 0:58
1

Computed columns add nothing to your data, and they break the DRY principal - you are repeating your data.

If you want to use the computed column for your views, AFAIK, most databases today support computed columns in your views (meaning - you define the column in your view as Tax + Price).

The only reason I can think of that would require you to save computed data is if the computation is non-trivial and time/cpu/memory consuming, in which case the computation on insert will free you from making the computation over and over again.

Another reason might be so you can create an index on the computed column for easy lookup. Most databases today though also support some kind of function based indexes, which will be a superior solution.

  • 1
    Tax rates can change year to year, and some customers might be exempt. Tax is something that probably should be stored. Its not just about complexity or how long the calculation takes. – Andy Mar 16 '14 at 20:11
  • @Andy - The Tax column is not the calculated one - PriceWithTax is. Price + Tax will always be the same. I did not say anything about not saving Tax. – Uri Agassi Mar 16 '14 at 20:38
  • 1
    PriceWithTax could easily be stored as well; its often the case that a user will search for ranges of order totals. Also not mentioned is logic for shipping charges and other fees. This stuff isn't uncommon and can easily make the case for storing the calculated value, just as is the case with tax. – Andy Mar 17 '14 at 0:56
  • I address "searching for order totals" when I speak about creating an index on the computed column (the last paragraph). Of course I don't mention shipping charges - this question is not about retail - it is about database design, and everything I said will cross over to shipping charges, age or BMI... – Uri Agassi Mar 17 '14 at 5:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.