I am wondering if storing the value of a total like TotalSalePrice in a database column is good practice or should I just have a property in the entity that returns the total by iterating through the entity collection that makes up the total. The one reason that I can think of for storing this in the database is for reporting or searching at some later point.

Why I'm asking this is because I'm trying to figure out a good solution for making up the total and then saving it for an entity.

The problem is something like this. To do this I have to manually iterate over the item collection any time I want to save the order. I would much rather this be done automatically some how. Maybe I can somehow override the TotalSalePrice property to do this for me?

CustomerOrderEntity order = new CustomerOrderEntity();
foreach(OrderItemEntity item in order.OrderItem)
    order.TotalSalePrice += item.SalePrice;

Does anyone have any thoughts on this?

tprohas wrote:

I am wondering if storing the value of a total like TotalSalePrice in a database column is good practice or should I just have a property in the entity that returns the total by iterating through the entity collection that makes up the total. The one reason that I can think of for storing this in the database is for reporting or searching at some later point.

Why I'm asking this is because I'm trying to figure out a good solution for making up the total and then saving it for an entity.

The problem is something like this. To do this I have to manually iterate over the item collection any time I want to save the order. I would much rather this be done automatically some how. Maybe I can somehow override the TotalSalePrice property to do this for me?

CustomerOrderEntity order = new CustomerOrderEntity();
foreach(OrderItemEntity item in order.OrderItem)
    order.TotalSalePrice += item.SalePrice;

Does anyone have any thoughts on this?

Ding! Yes, unless there's a strong reason to do otherwise (namely performance) it's best to keep derived values as derived values; don't store them.

So, yes, create a new property called "TotalSalesPrice" on your CustomerOrderEntity, and do exactly as you've done - don't store the value.

The only problem I have with this approach is reusability. It is, in fact, business logic, and as such needs to be accessible from multiple places - reports come to mind, namely. Unless your reports are based on the generated entities they won't have access to this algorithm. So, you'll have to recreate it in your reports. It isn't ideal, but the other alternative is to store the algorithm in a stored procedure which, for obvious reasons, isn't ideal either.

However, for this sort of simple thing it's reallly not that big of a deal to duplicate the logic. We don't live in an ideal world, after all.

Jeff...

tprohas wrote:

I am wondering if storing the value of a total like TotalSalePrice in a database column is good practice or should I just have a property in the entity that returns the total by iterating through the entity collection that makes up the total. The one reason that I can think of for storing this in the database is for reporting or searching at some later point.

Why I'm asking this is because I'm trying to figure out a good solution for making up the total and then saving it for an entity.

The problem is something like this. To do this I have to manually iterate over the item collection any time I want to save the order. I would much rather this be done automatically some how. Maybe I can somehow override the TotalSalePrice property to do this for me?

CustomerOrderEntity order = new CustomerOrderEntity();
foreach(OrderItemEntity item in order.OrderItem)
    order.TotalSalePrice += item.SalePrice;

Does anyone have any thoughts on this?

As a general rule, you should not store calculated values in the database (there are acceptions to this rule, of course, like some datawarehousing scenerios).

You've outlined one way of calculating the value: using application logic. In many cases, this is acceptable. One downside is that it is inefficient. Another is ensuring that you always get the same result--if you define another report that calculates the same value using a different set of code, you could end up with two different results.

IMHO, the best solution is to let the database do the calculating for you. There are a number of ways of accomplishing this, but they all involve aggregate functions (like SUM in your case):

SELECT
    OrderId,
    SUM(UnitPrice * Quantity) Order_SubTotal
FROM
    [order details]
GROUP BY
    OrderId

You can do this in a view (which you can now map to an Entity in LLBLGen, woohoo!), a stored proc, a user defined function, or an LLBLGen aggregate function.

Using the database to calculate these values is faster (a database is inherently designed to efficiently make these types of calculations ), and ensures that you will always get the same result.

(Although, to be fair, you have to re-use the database calulation logic to ensure that you always get the same results, which you could also do if you use an application-side function.)

Thanks for the answers guys.

I just thought of another reason why I might want to keep this logic in the database. what if I want to sort my query results on this calculated value as well as other database columns? Wouldn't a calculated column in the database be the solution to this?

tprohas wrote:

Thanks for the answers guys.

I just thought of another reason why I might want to keep this logic in the database. what if I want to sort my query results on this calculated value as well as other database columns? Wouldn't a calculated column in the database be the solution to this?

Again, this is data integrity versus performance. There is no right answer, per se, only what's the right answer for your circumstances. This is what database normalization is all about finding the right mix of integrity versus performance.

You should always start with data integrity being the primary concern and only compromise it if there is a strong need, either due to performance, or perhaps even maintainability. But it has to be strong to risk compromising your data integrity, as that is everything.

If you have a need to sort by the total cost of the order, I would recommend creating a view that includes a column containing the calculated cost and then sort by that. As was mentioned above, you can even map an entity onto it. This solution gives you data integrity, maintainability, and reusability, at the cost of a bit of performance versus storing the totals themselves in a table column.

Jeff...

jeffreygg wrote:

tprohas wrote:

Thanks for the answers guys.

I just thought of another reason why I might want to keep this logic in the database. what if I want to sort my query results on this calculated value as well as other database columns? Wouldn't a calculated column in the database be the solution to this?

Again, this is data integrity versus performance. There is no right answer, per se, only what's the right answer for your circumstances. This is what database normalization is all about finding the right mix of integrity versus performance.

You should always start with data integrity being the primary concern and only compromise it if there is a strong need, either due to performance, or perhaps even maintainability. But it has to be strong to risk compromising your data integrity, as that is everything.

If you have a need to sort by the total cost of the order, I would recommend creating a view that includes a column containing the calculated cost and then sort by that. As was mentioned above, you can even map an entity onto it. This solution gives you data integrity, maintainability, and reusability, at the cost of a bit of performance versus storing the totals themselves in a table column.

Jeff...

I'm a little confused by your statement about data integrity. Are you saying that db calculated columns are good or bad for data integrity?

tprohas wrote:

jeffreygg wrote:

tprohas wrote:

Thanks for the answers guys.

I just thought of another reason why I might want to keep this logic in the database. what if I want to sort my query results on this calculated value as well as other database columns? Wouldn't a calculated column in the database be the solution to this?

Again, this is data integrity versus performance. There is no right answer, per se, only what's the right answer for your circumstances. This is what database normalization is all about finding the right mix of integrity versus performance.

You should always start with data integrity being the primary concern and only compromise it if there is a strong need, either due to performance, or perhaps even maintainability. But it has to be strong to risk compromising your data integrity, as that is everything.

If you have a need to sort by the total cost of the order, I would recommend creating a view that includes a column containing the calculated cost and then sort by that. As was mentioned above, you can even map an entity onto it. This solution gives you data integrity, maintainability, and reusability, at the cost of a bit of performance versus storing the totals themselves in a table column.

Jeff...

I'm a little confused by your statement about data integrity. Are you saying that db calculated columns are good or bad for data integrity?

Good, if by "calculated columns" you mean a column in a view created with an aggregate expression. Bad, if you mean a column in a table containing a pre-calculated value.

Basically, don't duplicate your data. If the value can be calculated, then don't store it.

Jeff...

I'm still having a problem with this because I want these calculated columns in my entities so creating views is not going to help me out. I have a CustomerOrder entity which I want to have a TotalSalePrice which is the sum of the OrderItem.SalePrice. I don't want to have a view that is identical to the CustomerOrder just so that I can have a calculated TotalSalePrice. So far it still seems best to just use the foreach method of calculating the TotalSalePrice in the entity. Is there something that I don't understand about this?

tprohas wrote:

I'm still having a problem with this because I want these calculated columns in my entities so creating views is not going to help me out. I have a CustomerOrder entity which I want to have a TotalSalePrice which is the sum of the OrderItem.SalePrice. I don't want to have a view that is identical to the CustomerOrder just so that I can have a calculated TotalSalePrice. So far it still seems best to just use the foreach method of calculating the TotalSalePrice in the entity. Is there something that I don't understand about this?

Nope, it's a fine solution. It just comes with it's own drawbacks, namely you'll have to duplicate the summing code in your SQL statements for your reports. That's all.

Jeff...