0

Am building a application that'll allow users to enter values in different currency formats.

My initial idea was to save the data in the following format.

  • amount - string
  • currency - string with ISO 4217 currency code e.g. GBP, USD, RUB

Is this design a good idea or should I use for the amount field, integer, decimal or float type?

Am unsure how the different styles of formatting will affect the storage.

Aran
  • 3,298
  • 6
  • 32
  • 33
  • 3
    It would be best if you could choose a type that has a meaning rather than just string, e.g. `DECIMAL(8, 2)` – Ja͢ck Jan 22 '13 at 16:25
  • Using DECIMAL type would really limit the amounts that users could store. – Aran Jan 22 '13 at 16:30
  • 1
    Of course, you can change the scale and precision, e.g. `DECIMAL(40, 5)` ... but the question didn't state any specific intent. – Ja͢ck Jan 22 '13 at 16:33
  • Well I kind of did? "allow users to enter values in different currency formats" – Aran Jan 22 '13 at 16:36
  • 2
    That's as specific as saying you want something that floats and when I suggest a rowing boat you go "woah, that won't fit my crew of 100 pirates, where are the guns?!" =p – Ja͢ck Jan 22 '13 at 16:40

2 Answers2

3

My vote would be for:

  • Amount - DECIMAL(18,4) -> you can of course change the size depending on what you need. I don't know my currencies well enough to tell you how many decimal places you need so I decided to go with 4. I know at least up to 3 exists (see Various country's "currency decimal places width" in the iPhone-SDK)
  • Currency - foreign key reference to a currency table

Why the FK reference and currency table? You can then store more information along with each currency (e.g. relevant culture or formatting info), as well as have an additional table with exchange rates if necessary, so you can add everything up into one base currency.

Besides, storage is cheap these days.

Note:

  • I would not go with a string for the amount, for the simple fact that you can't add them and you can't order by them (easily). You also lose the built-in validation that the amount is indeed a number.

  • I would also not go with a float for the amount because monetary amounts are supposed to be exact. You don't want to have to round every result to display it; what if you're off by a cent in the wrong direction?

Community
  • 1
  • 1
lc.
  • 113,939
  • 20
  • 158
  • 187
  • Thanks for the currency table suggestion, I had actually considered doing it similar but outside a database using JSON files to organise the information. My biggest issue I was trying to find out about was the decimal places and decimal length. – Aran Jan 22 '13 at 16:46
1

I go for DECIMAL but this may depend on the application of the "amount".

  • this may be useful for understanding the differences between float an decimal:

Difference between float and decimal data type

Community
  • 1
  • 1
CMartins
  • 3,247
  • 5
  • 38
  • 53