need suggestion: fields for money

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
php12342005
Forum Commoner
Posts: 79
Joined: Mon Mar 21, 2005 3:35 am

need suggestion: fields for money

Post by php12342005 »

waht kind of fields do you use for currencies (money)?
i.e. a good may be sold in different currencies,
i.e. $100.10 US dollars or $60.32 EU dollars,
how do you describe the situation?

I prepare to use:
(field 1) DOUBLE for amount of money (i.e. 100.10)
(field 2) CHAR(20) for type of money (i.e. US)

but I feel this way is not good, please give suggestions.

thanks
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Look into NUMERIC instead of Double, but otherwise I'd do what you show. one field for amount, one field for currency. If your using InnoDB in MySQL or another DB with referential integrity, I'd make the currency column a foreign key to a list of currencies to catch mistakes.
php12342005
Forum Commoner
Posts: 79
Joined: Mon Mar 21, 2005 3:35 am

Post by php12342005 »

good suggestion,
thanks.

NUMERIC(M,B) - I guess M is bytes, what does B mean?
a foreign key to a list of currencies to catch mistakes
I will do it also.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

M is digits, B is number of decimal points so

NUMERIC(10) would be an integer with up ten digits + sign.
NUMERIC(10,2) wuld have two decimal place digits and 8 non-decimal place digits.

If you're dealing with prices often NUMERIC(XXX,2) is good where you've chosen an appopriate XXX, then adding 1 or 2 just in case.

If you're dealing more with exchange rates and finanncial matters, you typically need to use 3 decimal places (or more) depending on local laws.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I know € euro forces you to use 4 decimals :)
User avatar
AnarKy
Forum Contributor
Posts: 119
Joined: Tue Nov 02, 2004 1:49 am
Location: South Africa

Currency

Post by AnarKy »

Perhaps you could store all amount in one currency type (e.g US Dollar)
Then you could use conversions to convert the amount from US Dollars to
the local currency based on the exchange rates...
(which you may need to update or get from an external source.)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

But if i have money in euro... i don't want to see i suddenly have less euros because dollar rates changed :P

Having a (currency, amount) setup seems more appropriate ;)
Post Reply