Calculated/Stored totals for Read Only objects

Tom_W posted on Wednesday, September 16, 2009

Hi All

What do people reckon is the best way of handling calculated totals for read-only objects?

Say we have a read only CustomerInfo class (for showing in a RO customer list) and each customer has:

an 'Invoiced this year' value (the sum of all the invoice lines this year, where the invoice line's total is price * quantity),
a 'Total Invoiced Ever' value
and an account balance (sum of all invoices ever less payments + credits etc) and we want to show these totals in a customer list,

am I better:

A) Having child collections on the info classes and doing the maths there (so Customer would have child collections of invoice lines, financial transactions etc)
B) Storing the totals in the database itself so they can be queried without joins and used to populate the read only objects
C) Writing a view that handles all the joins, totals etc and is the basis of the read-only objects

Option A seems the purest approach, all the maths is wrapped up in the appropriate classes. However for bigger lists it seems like we'll be creating a lot of objects to get at the total values.

Option B I loathe, stored totals have been the bane of my life with the legacy apps we are supporting. Do people think there is value in stored totals or am I right in thinking they are as desirable as dose of piles?

Option C seems like a good comprimise, but then we are duplicating the maths on the database, which seems like having the same business logic in two places to me, and therefore feels 'wrong' and like it will be a PITA for maintainability (e.g. when the logic gets changed in one place but forgotten in the others).

I guess this really harks back to the eternal 'how dumb should the database be' question. Any thoughts gratefully received.

rsbaker0 replied on Wednesday, September 16, 2009

There are pros/cons with each approach.

(C) will probably perform the best, as you can retrieve the data you need in a single round trip to the database server. However, as you noted this is a maintenance issue as now you have View definition that you have to keep in sync with your object classes as they evolves over time.

We have actually used (B) in some places, but I'd don't think I would have designed it this way had it been my choice. The only advantage I can think of is that the calculation can be done offline and that fetching it is faster; however, any change to the underlying data requires that the summary data be recalculated.

(A) will be quickest to implement (assuming you already have the objects basically written), but will be the slowest performance wise (unless you are already fetching all the needed data in one round-trip)

A possible hybrid approach would be to generate ad hoc SQL in your code instead of a view and use the resulting DataTable/DataSet to populate your objects. At least this keeps the logic with your object. However, if you support multiple back-end databases, there will likely be variations in the dialects to deal with.

lukky replied on Wednesday, September 16, 2009

Hello Tom,

What I find myself doing in such cases is kind of what you describe in A), but instead of doing it in a separate class, I do it directly in the RO object at fetch time.

In the case of the CSLAContrib templates that you use, simply implement partial method OnFetching() in your user code file. Since you have the Linq to SQL entity passed into that method, you can easily navigate the model to retrieve the OrderDetails and other data to do the math in there (of course the Linq to SQL model must include those). The result of the math is stored in public read-only properties that I also define in the user code file.

This might not be "pure" OO, but it gets the job done Smile [:)]

Have a nice day

ajj3085 replied on Wednesday, September 16, 2009

I think option C is the best; let the server do the hard work all up front. Yes, you may have to duplicate some logic... but if the logic is simply addition, I don't think it would be that difficult to manage.

In this case option A, you're using up bandwidth and memory, just to get a total... unless you fetch all the rows, do the addition, and only store the total in your BO.

Actually, if you're using linq, couldn't you have something like this?

var total = (
from lineitems in mgr.Context.LineItems
where lineitems.year = year
select lineitems.Amount ).Sum();

Then you don't need to load up a bunch of objects, and you can let the server do the work w/o a view?

tmg4340 replied on Wednesday, September 16, 2009

Option A, while technically the "purest", is probably not your best option. Why pull back all that data simply to sum it up and discard it? Unless your summation rules are very complicated - and it doesn't sound like they are, otherwise you probably would have already discarded the database-based options - this kind of thing is what databases are built for.

Option B can actually be reasonably accommodated, depending on your DB. SQL Server supports calculated columns, which automatically update and take up no database space other than the column definition. Having said that, I don't generally use them, preferring to use views instead. I know of no real performance concerns/issues, and my bias towards views is largely because that's the way I've always done it. And I don't know whether Oracle has a similar construct.

Option C certainly does duplicate the logic, though in this case it doesn't sound like terribly complicated logic, or logic that's often changing. And, as Andy has mentioned, you can probably get what you want using LINQ. That's still something of a logic duplication, but at least it's all in one place.

Having said that, if I had to have something on the database for this, I'd probably choose Option C, though I'd probably try to get what I wanted via LINQ first.

HTH

- Scott

Tom_W replied on Wednesday, September 16, 2009

Many thanks all, the Linq approach is one I'd completely overlooked, but sounds like the way forward as the logic stays in the business objects.

It occurs to me that for some of the more complex logic I can abstract the maths away to a separate helper function that can then be used by both the editable objects (passing in the values from their collections) and also the info objects (passing in the values returned via Linq). That should allow me to normalise the behaviour.