Re: Best data type to use for sales tax percent

So back to my question about representing sales tax, it looks like I
have two choices:

1) Use a numeric(5,5) column.  This has the advantage of storing the
sales tax in the exact representation of a percent (I can directly
multiply it against any subtotal to get the sales tax).  It also
"looks" nicer in the DB and probably has some style points.  It
appears to me there is a massive amount of overhead with these columns
though, something like 8bytes plus whatever storage is required for
the precision.  However, even if I had a million rows that's only like
8 megs on the disk.  I'm more worried about perf than disk size.  I
would assume there's only a perf hit doing math with these types, not
so much just loading them into a dataset.

2) Use an Int2.  I'd have to use a multiplier in my source code after
loading the data.  In the DB, the data would look kinda funky since
9.825% would be represented as 9825.   I think this is a more
efficient number for storage since it only requires 2 bytes.  I'd be
able to store up to 65% or so which is fine for any sales tax I've
ever heard of.

I'm kinda leaning towards using the numeric column type, simply
because it seems "cleaner" to me.  At work, we use multipliers all
over the place in our DB and it has turned into a complete nightmare.
I'm somewhat of a believer in just storing data exactly how you need
to use it.

Thanks!

Mike

On Fri, Oct 9, 2009 at 4:13 AM, Sam Mason <sam@samason.me.uk> wrote:
> On Fri, Oct 09, 2009 at 12:10:41AM -0700, Mike Christensen wrote:
>> Wouldn't (4,3) let me store 0.000 through 9.999?  Maybe I'm still not
>> following what both numbers mean.
>
> I think Rich was getting confused about how you wanted to represent your
> percentages.
>
>> I understand the point about states/counties with 3 decimal digits of
>> sales tax, so I'd probably want to do (5,5) which should give me
>> 0.00000 - 0.99999, and store 9.825% sales tax as .09825.  I'm
>> suggesting storing sales tax as a number between 0 and 1 so I can
>> easily multiply it against a subtotal to get the tax amount, storing
>> anything over 1.0 is unnecessary.
>
> This is how I'd normally do it.  Ratios for inside the code, just
> "format" them as percentages when you want the user to see them.
>
>> Also, if you just say "numeric" (without any numbers) then Postgres
>> lets you store any number you wish and will never do any rounding of
>> any sort, correct?  If there a price you pay for this in terms of
>> perf, bytes on disk, etc?
>
> It's not possible to do division accurately (not sure about the caveats
> in other operators).  For example, 1/3 is represented as "0.33333" and
> multiplying this by three again will give "0.99999".  When people say
> that numeric types are "exact" they're not giving you whole truth.
>
>> Another idea is if I'm tying myself down to a certain level of decimal
>> accuracy in the first place, why not just store everything as an Int2?
>>  9.825% would be stored as 9825 and I'll divide everything by 100000
>> when I calc sales tax.  If I'm not mistaken, integral data types are
>> faster for Postgres and less bytes on disk, right?  BTW, I will never
>> be doing any math using Postgres, it's just for pure storage..
>
> Not sure what range of values you have to cover; you wouldn't be able to
> do this with fixed width integer types:
>
>  select numeric '100' ^ 300;
>
> Numeric types allow you to do the above, the flexibility of allowing the
> representation of a number to get this wide that causes things to be
> slower.  It's not much slower though, I'd benchmark a test case that's
> meaningful to you and then can you make a sensible decision.
>
> --
>  Sam  http://samason.me.uk/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>