Re: rounding problems

[Justin <justin@xxxxxxxxxxxxxxx>]

Craig Ringer wrote:

Justin wrote:

  

WE have several columns in table defined with numeric (20,10) thats is
just insanity.
    

Not necessarily. I have a few places where a monetary value is
mulitiplied by a ratio quantity. For some of the historical data
imported from another system the ratio can be irrational or at least not
representable in any small precision value.

I ended up needing a precision of 8 numeric digits to acceptably
represent these ratios, resulting in a numeric(16,8) type to permit
ratio values up to 99999999.99999999 . I probably could've got away with
numeric(13,8) or even numeric(12,8) but as space and performance aren't
utterly critical it didn't seem to be worth the risk of hitting limits
and overflows later. As it is I'm tempted to go to 10 digits of
precision, as there's still a 3 cent difference between the totals from
the old system and the same data imported into the new system.
  

That 3 cent difference is over how many transactions ??? 

The differences i'm seeing are getting into the hundreds of dollars in 1 quarter within this stupid application.

The person/persons who laid this database out do not or did not understand the compound rounding errors. I'm just trying to figure out how best to fix it. 

You'll encounter similar situations in your materials consumption
tracking (as you detailed below) and other places. So don't discount the
 use of high precision numeric values just yet.

Personally I'd be tempted to use a `double precision' (float8) for
things like materials consumption. Materials consumed in huge quantities
will have lower price rates, and materials consumed in tiny quantities
will often be priced higher. With wally-numbers: You're not going to
care about the 0.0003 kg of steel consumed at a price of $0.00001 , but
the same amount of something valuable might have a detectable (if still
sub-cent) value. Floating point numbers are IMO better for that than BCD
numeric. However, since the float will just get converted to numeric
during multiplication with a numeric price-per-mass ratio it may well
not be worth worrying about it.

There's a use for that numeric(20,10).
  

I'm moving all the numeric fields to numeric(20,8) .  I feel its pretty safe with that scale setting.  I agree data storage and performance aren't critical concerns as they once were

  Unless your doing scientific calculations which we do,
do.   Having that many decimal points for an accounting package is just
nonsense and then its rounded to 4 or 6 in Inventory  and Wip tables
then 2 when the numbers finally hit the GL tables. Who ever laid
these tables out has never had to try and get numbers to balance and
agree across tables :-( .   Every time i dig a little deeper i keep
finding stupid things like this.
    

It sounds like you might have quite a bit of compounded rounding error
from the successive stages of rounding as data moves through the system.
Maybe you're rounding too aggressively?
  

Thats the problem the database layout is crap. 

I like to store a bit more precision than I have to, unless there's a
business rule that requires rounding to a particular precision. For
example, if your invoice items are rounded to whole cents you'd probably
round the calculated invoice item price when inserting into an invoice
item table.

Of course, that means that
   sum(calculation of invoice item price)
   <>
   sum(rounded price of invoice items)
  

because of rounding. That's fine; you can't balance the two things
exactly because they're actually subtly different things. If you're
using an appropriate rounding method for financial data, like
round-to-even, you'll only ever get a couple of cents difference and
that should be expected and ignored.
  

I normally would but given all the tables are showing different values when summed over a Accounting period its adding up to significant differences between all the tables. 

Some people may think i'm crazy trying to track this down but when
you're only consume 0.003186 lbs of a metal per part that cost 22.7868
per  lb and the work order calls fro 1148 parts.  how the machine rounds
becomes a big problem   (.00318611*1148) = 3.65765 lbs consumed *
22.7868 = $83.3462 . Sense GL tables are 2 decimal the value is stored
as $83.35
    

Thinking about correct rounding and precision is very important, and far
from crazy.

  

The question quickly becomes what number is the correct number.
    

Sometimes the answer is "both of them" - even though they are different.
See the example above with rounded invoice items.

--
Craig Ringer