annual price increases

[cluce]

Working on a database to invoice time and materials for a construction company. The item prices are stored in tables and the invoice uses calculated fields for the totals. At the beginning of 2004 the prices will increase say 3 percent. If the prices are changed in the tables then all of the invoices for 2003 are recalculated. What would be the general approach for this problem? Your help is much appreciated.

Thanks
Clint

 

[jsteph]

You should never use calculated fields with invoicing. What if a customer disputes an invoice and you need to bring up a historical view of that invoice? Now it's going to calculate based on the current prices, instead of the prices at the time. Or an audit.

One approach for this kind of stuff would be to use a price table with a date range--same product multiple times with different date range. This is not the 'Product' table, but the 'price' table, which at it's simplest has a Product ID, a price, and a date range, and possibly it's own unique id.

In my opinion though, the invoice itself should always have it's own price fields regardless. The Order or Estimates can use the Price table for lookup when entering them, but an invoice should be saved 'as is' with no calculated or lookup fields that could ever change if something else changes. That situation would drive the accountants insane.

When entering prices, have a 'from' and 'to' date on each product/service's record, and on whatever form you change prices, do the logic and validation to make sure there are no 'holes' in the ranges. A new price for an existing product is a new record--with a new range that doesn't overlap and it doesn't skip any previous record for that product. This way you have a historical record of what the 'list price' was at any given time, but you also have the actual invoice--which may have had discounts or whatever that might be totally arbitrary.
-jsteph