This is a multi-part message in MIME format.
--------------000808010107030404040202
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
> im****ted 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 im****ted 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 im****tant, 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
>
--------------000808010107030404040202
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<br>
Craig Ringer wrote:
<blockquote cite="mid:48290E11.9000007@[EMAIL PROTECTED]
"
type="cite">
<pre wrap="">Justin wrote:
</pre>
<blockquote type="cite">
<pre wrap="">WE have several columns in table defined with numeric
(20,10) thats is
just insanity.
</pre>
</blockquote>
<pre wrap=""><!---->
Not necessarily. I have a few places where a monetary value is
mulitiplied by a ratio quantity. For some of the historical data
im****ted 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 im****ted into the new system.
</pre>
</blockquote>
That 3 cent difference is over how many transactions ??? <br>
<br>
The differences i'm seeing are getting into the hundreds of dollars in
1 quarter within this stupid application.<br>
<br>
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. <br>
<br>
<br>
<blockquote cite="mid:48290E11.9000007@[EMAIL PROTECTED]
"
type="cite">
<pre wrap="">
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).
</pre>
</blockquote>
<br>
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 <br>
<blockquote cite="mid:48290E11.9000007@[EMAIL PROTECTED]
"
type="cite">
<pre wrap="">
</pre>
<blockquote type="cite">
<pre wrap=""> 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.
</pre>
</blockquote>
<pre wrap=""><!---->
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?
</pre>
</blockquote>
<br>
Thats the problem the database layout is crap. <br>
<blockquote cite="mid:48290E11.9000007@[EMAIL PROTECTED]
"
type="cite">
<pre wrap="">
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)
</pre>
</blockquote>
<blockquote cite="mid:48290E11.9000007@[EMAIL PROTECTED]
"
type="cite">
<pre wrap="">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.
</pre>
</blockquote>
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. <br>
<blockquote cite="mid:48290E11.9000007@[EMAIL PROTECTED]
"
type="cite">
<pre wrap="">
</pre>
<blockquote type="cite">
<pre wrap="">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
</pre>
</blockquote>
<pre wrap=""><!---->
Thinking about correct rounding and precision is very im****tant, and far
from crazy.
</pre>
<blockquote type="cite">
<pre wrap="">The question quickly becomes what number is the correct
number.
</pre>
</blockquote>
<pre wrap=""><!---->
Sometimes the answer is "both of them" - even though they are different.
See the example above with rounded invoice items.
--
Craig Ringer
</pre>
</blockquote>
</body>
</html>
--------------000808010107030404040202--
|
