Andy,
I would put the calculation in the view. Besides the stated performance benefit, it allows you to make changes to your calculations without having to make changes to the object.
The issue with storing aggregate columns is that you have to update your schema in order to stay current with requirements. If your users wanted to see a further breakdown of your tax amounts (arbitrary example, not sure if it applies), then you would have to store a new column for each and update each accordingly. If you modify your calculations then you have to update the affected aggregate columns.
Christian
Personally I would create a line item collection under your Quote info object. It would eliminate any normalization issues and encapsulate all your business logic within your object. I have used the summarization approach at the summary level before. The main problem is that unless your documentation and processes are solid, other developers may corrupt your data. Then you will have to build additional processes to validate that data. It becomes a nasty cycle.
![Smile [:)]](/emoticons/emotion-1.gif)
Triggers are a perfectly reasonable solution to maintaining synchronization between source data and it's denormalized (and optionally aggregated) copy.
They have the virture of putting all the behavior for synchronizing the data in one place instead of in (possibly) many objects that may each serve just one use case.
Frankly, I believe taht manipulating business data is the business object's job. Manipulating copies of data in the database made for performance reasons is the database's job, it's not a business activity at all.
===== second, unrelated point =============
One person made a point on this topic, and I strongly disagree.
From a business point of view, I would not think that changing a quoted price for a product on a quotation line item when the product's price changes is a good idea. If you do that, then you will have some very unhappy customers! You will have quoted one price to them and then, when you look it up later, you will think you quoted them a different price. Bad, bad, bad! A guaranteed way to make your potential customers angry if the prices have risen since you issued your quote - they will think you were lying to them. And a great way to lose money they were willing to pay if prices had fallen since the quotation was given. A quotation expiration date is the usual way to deal with that - the prices quoted are good until the quote expires.
david.wendelken:
From a business point of view, I would not think that changing a quoted price for a product on a quotation line item when the product's price changes is a good idea. If you do that, then you will have some very unhappy customers! You will have quoted one price to them and then, when you look it up later, you will think you quoted them a different price. Bad, bad, bad! A guaranteed way to make your potential customers angry if the prices have risen since you issued your quote - they will think you were lying to them. And a great way to lose money they were willing to pay if prices had fallen since the quotation was given. A quotation expiration date is the usual way to deal with that - the prices quoted are good until the quote expires.
You are right, I did miss part of the requirement. I missed the distinction between "quote-in-preparation" and "quote-sent-to-customer". :)
That said, you have one or two places for keeping prices up-to-date.
1) When a product updates its price, you need to copy the new price to un-sent quotations.
This is NOT a denormalization issue. The quotation line item price and the product current price are two different pieces of data. Thus, this is a pure business logic issue and the code should be invoked from the business object. Naturally, the business object needs to distinguish between sent and un-sent quotations.
Alternatively, you could modify your database design and store sent quotations in one set of data tables and quotations in-process in another. When a quotation is sent, it copies the current product prices and moves itself to the sent quotation tables (and removes itself from the unsent ones.) The in-process quotation line item would not actually have a line item price, as it would look up the product info for the base line item price. This would avoid the entire update the unsent quotation line item issue entirely. It also has the advantage of avoiding fragmentation of your quotation table storage area. Sent quotations shouldn't (normally) be changed, whereas unsent quotations might be changed a lot (depending upon your business) as you dicker with the customer over requirements.
2) If you choose to denormalize the totals into the quotation table from the quotation line item table:
Every time you update a quotation line item price, the denormalized total needs to be updated. For this purpose, sent/unsent is of no consequence. (If you allow a change to quotation line item then you have to update the totals, period.) This is a pure database issue and a trigger would be excellent here. No matter how many different objects mess with quotation line item data, the database can keep them synchronized.
Optionally, there is an option in Sql Server 2005 (maybe earlier) to define a computed column. Haven't double-checked how it works yet, but it might do the trick for you.
david.wendelken:
Alternatively, you could modify your database design and store sent quotations in one set of data tables and quotations in-process in another. When a quotation is sent, it copies the current product prices and moves itself to the sent quotation tables (and removes itself from the unsent ones.) The in-process quotation line item would not actually have a line item price, as it would look up the product info for the base line item price. This would avoid the entire update the unsent quotation line item issue entirely. It also has the advantage of avoiding fragmentation of your quotation table storage area. Sent quotations shouldn't (normally) be changed, whereas unsent quotations might be changed a lot (depending upon your business) as you dicker with the customer over requirements.
I use this a lot for Person data (one table with ALL persons, and separate tables for specific kinds of persons, like Employees, CustomerContacts, etc). If you want a list of all quotations with all details, use OUTER JOINS to merge the tables.
1) When a product updates its price, you need to copy the new price to un-sent quotations.This is NOT a denormalization issue. The quotation line item price and the product current price are two different pieces of data. Thus, this is a pure business logic issue and the code should be invoked from the business object. Naturally, the business object needs to distinguish between sent and un-sent quotations.
That's how it is in the database as well. Currently I'm copying the price into the line item table whenever the quote is saved; maybe I should only copy the price information to the line item table when the item is locked. The BO's could then use the line item price only if its there, otherwise use the product price. (Maybe I need a list of just product ids + their prices for a price list..)
Alternatively, you could modify your database design and store sent quotations in one set of data tables and quotations in-process in another. When a quotation is sent, it copies the current product prices and moves itself to the sent quotation tables (and removes itself from the unsent ones.) The in-process quotation line item would not actually have a line item price, as it would look up the product info for the base line item price. This would avoid the entire update the unsent quotation line item issue entirely. It also has the advantage of avoiding fragmentation of your quotation table storage area. Sent quotations shouldn't (normally) be changed, whereas unsent quotations might be changed a lot (depending upon your business) as you dicker with the customer over requirements.
Every time you update a quotation line item price, the denormalized total needs to be updated. For this purpose, sent/unsent is of no consequence. (If you allow a change to quotation line item then you have to update the totals, period.) This is a pure database issue and a trigger would be excellent here. No matter how many different objects mess with quotation line item data, the database can keep them synchronized.
Optionally, there is an option in Sql Server 2005 (maybe earlier) to define a computed column. Haven't double-checked how it works yet, but it might do the trick for you.
ajj3085:
Alternatively, you could modify your database design and store sent quotations in one set of data tables and quotations in-process in another. When a quotation is sent, it copies the current product prices and moves itself to the sent quotation tables (and removes itself from the unsent ones.) The in-process quotation line item would not actually have a line item price, as it would look up the product info for the base line item price. This would avoid the entire update the unsent quotation line item issue entirely. It also has the advantage of avoiding fragmentation of your quotation table storage area. Sent quotations shouldn't (normally) be changed, whereas unsent quotations might be changed a lot (depending upon your business) as you dicker with the customer over requirements.
Well, I'm not a fan of denormalization to begin with. Keep in mind that I also have Orders and Invoices, whose line item elements function identically to the Quote. I'll also very likely have an RMA document in the next phase, although i'm not clear if it will be function similar to the existing documents. So with this method, i'm up to six identical tables.
You misunderstood. You would not have identical tables with this approach. The line item tables for unsent quotations would not contain the current product price. They would be different (only) in that regard.
Every time you update a quotation line item price, the denormalized total needs to be updated. For this purpose, sent/unsent is of no consequence. (If you allow a change to quotation line item then you have to update the totals, period.) This is a pure database issue and a trigger would be excellent here. No matter how many different objects mess with quotation line item data, the database can keep them synchronized.
That would be a fine solution as well, except that product price changes won't be reflected, which was a problem when this solution was originally suggested. Its not so much of a problem if the user's have to open the quote for editing to see any data, but that would be a poor user experience I would think. If they have a list of quotes (which are read only objects) it would be ideal to give them summary data there (which is actually why I posted this; the editable business objects load everything and thus always get the correct data.. so its only an issue for my readonly summary objects).
Nope, not true. Product prices would be reflected in unsent quotations!
A change to a product price (via the product business object) should cause an update to all unsent quotation line items for that product.
update quotation_line_item
set product_unit_price = @new_product_unit_price
where product_id = @product_id
and quotation_id in (select quotation_id
from quotation
where quotation_status = 'Not Sent'
)
The database trigger on quotation_line_item would be responsible for updating the quotation total_price column value for the changed quotations.
You misunderstood. You would not have identical tables with this approach. The line item tables for unsent quotations would not contain the current product price. They would be different (only) in that regard.
Nope, not true. Product prices would be reflected in unsent quotations!A change to a product price (via the product business object) should cause an update to all unsent quotation line items for that product.
update quotation_line_item
set product_unit_price = @new_product_unit_price
where product_id = @product_id
and quotation_id in (select quotation_id
from quotation
where quotation_status = 'Not Sent'
)The database trigger on quotation_line_item would be responsible for updating the quotation total_price column value for the changed quotations.
Ahh, I see. That would work, although I would have concerns about performance. There is functionality that allows prices for products to be updated in one transaction.. and I know LineItems will be a table that will quickly accumlate many rows.
One of the advantages of the "un-sent" line item table is that it wouldn't get really large - as quotes were sent it would shrink back. :)
Disadvantages are as you noted, multiple similar tables.
Computer science is the science of giving away some features you want in order to get other features you want more strongly. :)
Copyright (c) Marimer LLC