CODEWITHSUNDEEP
mysqldatabase-design
Aran
Aran

Reputation: 3338

Storing numbers for different formats in MySQL

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.

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.

Upvotes: 0

Views: 376

Answers (2)

CMartins
CMartins

Reputation: 3293

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

Upvotes: 1

lc.
lc.

Reputation: 116458

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?

Upvotes: 3

Related Questions