On Wed, 2003-12-03 at 07:02, David Garamond wrote: > What do people recommend for storing money amounts? I've seen people use > NUMERIC(18,3) and other use NUMERIC(18,4). Which one is more appropriate > and why? This is considering various existing currencies, some having > low rates (like IDR, in which you can have large amount up to hundreds > of trillions) and some high rates (like USD, in which you can have small > amount like 0.1 cent). Are there places/industries which involve values > lower than 0.1 cent?
I think you should match the customer's data and use whatever precision is necessary for it. The needs of a small shop will not be the same as a currency trader's. You should not regard amounts in different currencies as equivalent. You cannot add Euros to dollars and get a meaningful figure; so they should not be in the same column. If you are handling multiple currencies, your database design needs to be a lot more sophisticated than having a single money column. > And what about 'factor' field in currency conversion table? Should I use > REAL, or DOUBLE PRECISION (do we need 15-16 digit precision?) or NUMERIC > (exact numbers). The factor should range between 1E-3 (e.g. converting > IDR to USD) to 1E4 (e.g. converting IDR to pounds/euros). You should only use NUMERIC for money; any kind of floating point representation will lose detail somewhere along the line. (I suppose you could use BIGINT for Japanese Yen.) -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "What shall we then say to these things? If God be for us, who can be against us?" Romans 8:31 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
