MySQL Storing Dollar Amounts Field Type

  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 437

Post 3+ Months Ago

There's so much on this topic that it's gotten a little out of hand rather to use DECIMAL, DOUBLE, or FLOAT for money. Here are my thoughts and a small issue I've ran into:

1. DOUBLE and FLOAT are vitually the same thing: The have an unspecified end point and are not specific. They can be used for money when dealing with estimated amounts. They can store negative amounts.
2. Decimal - Specific end point to give exact amount. The amount stored is the amount extracted. Can not store negative numbers.

Those are just a quick definition I go by. My problem is with negative numbers. I want to / have to use DECIMAL cause I need the precision, but how do I store negative amounts. I can insert a record from PHPmyAdmin with a negative amount into a DECIMAL field but it seems a script I'm using will not. Do I really have to add an additional column that contains an enum (0,1) to set whether or not the amount is negative or positive or what is the best way to store money?

Thanks for the advise.
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6254
  • Loc: Seattle, WA

Post 3+ Months Ago

Use INTEGER. Store all monetary values by their lowest denominator, i.e. cents. Don't bother with floating point numbers when there's already an indivisible unit available.
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 437

Post 3+ Months Ago

Ok, sounds like what you're saying is drop the decimal? So, store 7459.89 as a signed INT field, but insert 745989? Can you elaborate some?

Thank you. I'm imagining storing the full integer then dividing by 100 in my script.
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 437

Post 3+ Months Ago

BTW, DECIMAL can store negative numbers. The description on MySQL's site is a little mis-leading. I think it's talking more about how the sign is handled bitwise and that a literal - sign is not used.
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6254
  • Loc: Seattle, WA

Post 3+ Months Ago

devilwood wrote:
Ok, sounds like what you're saying is drop the decimal? So, store 7459.89 as a signed INT field, but insert 745989? Can you elaborate some?

Thank you. I'm imagining storing the full integer then dividing by 100 in my script.

Yep, exactly. Drop the decimal and store the value as cents rather than dollars. Let your application do the formatting.
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 437

Post 3+ Months Ago

I like this idea. I have functions that format the money anyway(money format, dollar sign, etc) so fixing this in my applications should be easy.

This should work nicely.

Thanks for your suggestion.

Post Information

  • Total Posts in this topic: 6 posts
  • Users browsing this forum: No registered users and 52 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.