Unanswered: Alternative to storing calculated value?
A situation has arisen during the development of my latest project, an invoicing system for an advertising agency. The agency runs ads in various national newspaper at different rates based on the number of lines the particular ad contains. A receptionist enters ads into the system and it calculates the number of lines (based on a constant of 25 characters per line) and then multiplies it by the selected line rate to generate a final total. This total is important and will be used at a later time to generate an invoice for the customer. My question is this, instead of storing the value in a table (which is taboo in databases), what alternative do I have for later retreiving this value. I need to output the value to a report and deduct from it any payments that have been applied to that particular ad charge. Any ideas?
If the item is going to be of significance (by significance I mean in relation to accounting or fiscal or tax reporting) then you can and should overide the db mantra that derived values should NEVER be stored. Probably it should have the suffix ", unless you gave a very good reason to store a derived value."
The reason you need to have confidence that what you invoiced / reported, whatever is the same when the customer comes round to pay. If there is a discrepancy then a new invoice has to be raised, otherwise your invoices may not match to payments.
In reality the mantra should be treated as a guidance (good guidance none the less) but not diktat.
Yes you could design some schema which caters for changes, applying retrospective corrections, but it strikes me as over complicating something, that is fairly straightforward.
It has the added advantage that accountants can understand it and relate to it, after all to most accountants third normal form probably means that all accountants are wearing the same shade of grey suit, the same shoes and the same shirt.
As duly stated by healdem, it is highly advised against (storing data in tables), but then again sometimes you have to break the rules. All depends on the user and the situations. IF you don't have a good alternative to storing it in tables, then do so by way of creating an Invoice History table so that when you create Invoices, you have a Query(Update/Append) to take it and send it to that table. But keep if out of the general view of others. That can be done several ways as I think you may already know of. So that is just food for thought. Sometimes rules are to be broken, but make sure you have a good case for it and realize all the shortcomings that possibly tag along. Though not a real solution, I hope this gives you more food for thought.