Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Let's consider the following example of a class, which is an entity that I'm using through Entity Framework.

- InvoiceHeader
    - BilledAmount (property, decimal)
    - PaidAmount (property, decimal)
    - Balance (property, decimal)

I'm trying to find the best approach to keep Balance updated, based on the values of the two other properties (BilledAmount and PaidAmount).

I'm torn between two practices here:

  1. Updating the balance amount every time BilledAmount and PaidAmount are updated (through their setters)
  2. Having a UpdateBalance() method that the callers would run on the object when appropriate.

I am aware that I can just calculate the Balance in its getter. However, it isn't really possible because this is an entity field that needs to be saved back to the database, where it has an actual column, and where the calculated amount should be persisted to.

My other worry about the automatically updating approach is that the calculated values might be a little bit different from what was originally saved to the database, due to rounding values (an older version of the software, was using floats, but now decimals). So, loading, let's say 2000 entities from the database could change their status and make the ORM believe that they have changed and be persisted back to the database the next time the SaveChanges() method is called on the context.

It would trigger a mass of updates that I am not really interested in, or could cause problems, if the calculation methods changed (the entities fetched would lose their old values to be replaced by freshly recalculated ones, simply by being loaded).

Then, let's take the example even further.

Each invoice has some related invoice details, which also have BilledAmount, PaidAmount and Balance (I'm simplifying my actual business case for the sake of the example, so let's assume the customer can pay each item of the invoice separately rather than as a whole).

If we consider the entity should take care of itself, any change of the child details should cause the Invoice totals to change as well.

In a fully automated approach, a simple implementation would be looping through each detail of the invoice to recalculate the header totals, every time one the property changes. It probably would be fine for just a record, but if a lot of entities were fetched at once, it could create a significant overhead, as it would perform this process every time a new invoice detail record is fetched. Possibly worse, if the details are not already loaded, it could cause the ORM to lazy-load them, just to recalculate the balances.

So far, I went with the Update() method-way, mainly for the reasons I explained above, but I wonder if it was right. I'm noticing I have to keep calling these methods quite often and at different places in my code and it is potential source of bugs.

It also has a detrimental effect on data-binding because when the properties of the detail or header changes, the other properties are left out of date and the method has no way to be called.

What is the recommended approach in this case?

share|improve this question

migrated from codereview.stackexchange.com Oct 10 '12 at 19:33

This question came from our site for peer programmer code reviews.

2  
I don't see any reason it has to be saved into database based on your description. –  Codism Oct 10 '12 at 19:43
add comment

2 Answers

up vote 1 down vote accepted

I see that this problem is not a programming problem as much as it is a schema design problem. Where to place the properties and method may comes secondary to identifying database columns if you are mapping tables to classes in 1-1 or near 1-1 fashion.

I personally see that after the data type has been changed, you should have run a report comparing old totals and new totals and would probably want to involve management then update the database in batch and get all data consistent once and for all.

On the other issues, the correct answer will depend on answering the following questions:

  1. Is your shop strict about normalization? If the answer is 'yes', do it the normalized way.

  2. Do other systems update the tables involved?

  3. Do you have less than 300 children per header? (300 is an approximate value of course)

  4. Approximately one user would be accessing the same invoice header or detail at any time

The table below summarizes my opinion based on the different answers of the questions. Note that (-) means 'don't care'.

I personally prefer working with non-redundant data whenever possible and would place data correctness and integrity over performance if I had to and it was up-to-me, so I am biased. I made the assumption that an invoice is usually accessed only few times after its creation.

Also notice that the calculation need not be physically stored as table column, instead it could be calculated in a view and that view is used by any program wishing to access this information. Make sure your reporting software can use views before you do this.

enter image description here

share|improve this answer
add comment

That's what I would call data duplication:

If c = a + b, then why do you need to store c? unless the computation is expensive, of course, but then it wouldn't be automatic.

Keeping such numbers in sync becomes a nightmare, involving:

  • Unwanted amount of updates
  • Complexification of the UI eventing
  • Extra db/network load

And some more.

My solution: don't do it. Recompute it when you want to display it, use Binding mechanisms to do so (available in WPF, SL and Javascript AFAIK).

share|improve this answer
    
Data duplication is sometimes necessary to obtain good performance. Imagine if, every time you needed an account balance, you had to go through the entire account history to calculate it. Imagine if that required looping through a thousand account transactions. Then, imagine that you had to do this with a thousuand customers in a report or a display. How log would it take to get the grand total? –  Robert Harvey Oct 13 '12 at 2:35
    
It's more of a plateau case you're describing: at points in time the balance is re-calculated, and any transaction after the last point sits until the balance is re-calculated again. That is far from the usual case. –  Baboon Oct 14 '12 at 14:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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