I'm using a decimal column to store money values on a database, and today I was wondering what precision and scale to use.
Since supposedly char columns of a fixed width are more efficient, I was thinking the same could be true for decimal columns. Is it?
And what precision and scale should I use? I was thinking precision 24/8. Is that overkill, not enough or ok?
This is what I've decided to do:
DECIMAL(19,4)I don't think a float for the conversion rate is an issue, since it's mostly for reference, and I'll be casting it to a decimal anyway.
Thank you all for your valuable input.
This question is related to
sql
database
database-design
currency
Sometimes you will need to go to less than a cent and there are international currencies that use very large demoniations. For example, you might charge your customers 0.088 cents per transaction. In my Oracle database the columns are defined as NUMBER(20,4)
The money datatype on SQL Server has four digits after the decimal.
From SQL Server 2000 Books Online:
Monetary data represents positive or negative amounts of money. In Microsoft® SQL Server™ 2000, monetary data is stored using the money and smallmoney data types. Monetary data can be stored to an accuracy of four decimal places. Use the money data type to store values in the range from -922,337,203,685,477.5808 through +922,337,203,685,477.5807 (requires 8 bytes to store a value). Use the smallmoney data type to store values in the range from -214,748.3648 through 214,748.3647 (requires 4 bytes to store a value). If a greater number of decimal places are required, use the decimal data type instead.
If you're going to be doing any sort of arithmetic operations in the DB (multiplying out billing rates and so on), you'll probably want a lot more precision than people here are suggesting, for the same reasons that you'd never want to use anything less than a double-precision floating point value in application code.
4 decimal places would give you the accuracy to store the world's smallest currency sub-units. You can take it down further if you need micropayment (nanopayment?!) accuracy.
I too prefer DECIMAL to DBMS-specific money types, you're safer keeping that kind of logic in the application IMO. Another approach along the same lines is simply to use a [long] integer, with formatting into ¤unit.subunit for human readability (¤ = currency symbol) done at the application level.
If you were using IBM Informix Dynamic Server, you would have a MONEY type which is a minor variant on the DECIMAL or NUMERIC type. It is always a fixed-point type (whereas DECIMAL can be a floating point type). You can specify a scale from 1 to 32, and a precision from 0 to 32 (defaulting to a scale of 16 and a precision of 2). So, depending on what you need to store, you might use DECIMAL(16,2) - still big enough to hold the US Federal Deficit, to the nearest cent - or you might use a smaller range, or more decimal places.
4 decimal places would give you the accuracy to store the world's smallest currency sub-units. You can take it down further if you need micropayment (nanopayment?!) accuracy.
I too prefer DECIMAL to DBMS-specific money types, you're safer keeping that kind of logic in the application IMO. Another approach along the same lines is simply to use a [long] integer, with formatting into ¤unit.subunit for human readability (¤ = currency symbol) done at the application level.
We recently implemented a system that needs to handle values in multiple currencies and convert between them, and figured out a few things the hard way.
NEVER USE FLOATING POINT NUMBERS FOR MONEY
Floating point arithmetic introduces inaccuracies that may not be noticed until they've screwed something up. All values should be stored as either integers or fixed-decimal types, and if you choose to use a fixed-decimal type then make sure you understand exactly what that type does under the hood (ie, does it internally use an integer or floating point type).
When you do need to do calculations or conversions:
When converting a floating point number back to an integer in step 3, don't just cast it - use a math function to round it first. This will usually be round, though in special cases it could be floor or ceil. Know the difference and choose carefully.
Store the type of a number alongside the value
This may not be as important for you if you're only handling one currency, but it was important for us in handling multiple currencies. We used the 3-character code for a currency, such as USD, GBP, JPY, EUR, etc.
Depending on the situation, it may also be helpful to store:
Know the accuracy bounds of the numbers you're dealing with
For real values, you want to be as precise as the smallest unit of the currency. This means you have no values smaller than a cent, a penny, a yen, a fen, etc. Don't store values with higher accuracy than that for no reason.
Internally, you may choose to deal with smaller values, in which case that's a different type of currency value. Make sure your code knows which is which and doesn't get them mixed up. Avoid using floating point values even here.
Adding all those rules together, we decided on the following rules. In running code, currencies are stored using an integer for the smallest unit.
class Currency {
String code; // eg "USD"
int value; // eg 2500
boolean converted;
}
class Price {
Currency grossValue;
Currency netValue;
Tax taxRate;
}
In the database, the values are stored as a string in the following format:
USD:2500
That stores the value of $25.00. We were able to do that only because the code that deals with currencies doesn't need to be within the database layer itself, so all values can be converted into memory first. Other situations will no doubt lend themselves to other solutions.
And in case I didn't make it clear earlier, don't use float!
If you're going to be doing any sort of arithmetic operations in the DB (multiplying out billing rates and so on), you'll probably want a lot more precision than people here are suggesting, for the same reasons that you'd never want to use anything less than a double-precision floating point value in application code.
4 decimal places would give you the accuracy to store the world's smallest currency sub-units. You can take it down further if you need micropayment (nanopayment?!) accuracy.
I too prefer DECIMAL to DBMS-specific money types, you're safer keeping that kind of logic in the application IMO. Another approach along the same lines is simply to use a [long] integer, with formatting into ¤unit.subunit for human readability (¤ = currency symbol) done at the application level.
I would think that for a large part your or your client's requirements should dictate what precision and scale to use. For example, for the e-commerce website I am working on that deals with money in GBP only, I have been required to keep it to Decimal( 6, 2 ).
We recently implemented a system that needs to handle values in multiple currencies and convert between them, and figured out a few things the hard way.
NEVER USE FLOATING POINT NUMBERS FOR MONEY
Floating point arithmetic introduces inaccuracies that may not be noticed until they've screwed something up. All values should be stored as either integers or fixed-decimal types, and if you choose to use a fixed-decimal type then make sure you understand exactly what that type does under the hood (ie, does it internally use an integer or floating point type).
When you do need to do calculations or conversions:
When converting a floating point number back to an integer in step 3, don't just cast it - use a math function to round it first. This will usually be round, though in special cases it could be floor or ceil. Know the difference and choose carefully.
Store the type of a number alongside the value
This may not be as important for you if you're only handling one currency, but it was important for us in handling multiple currencies. We used the 3-character code for a currency, such as USD, GBP, JPY, EUR, etc.
Depending on the situation, it may also be helpful to store:
Know the accuracy bounds of the numbers you're dealing with
For real values, you want to be as precise as the smallest unit of the currency. This means you have no values smaller than a cent, a penny, a yen, a fen, etc. Don't store values with higher accuracy than that for no reason.
Internally, you may choose to deal with smaller values, in which case that's a different type of currency value. Make sure your code knows which is which and doesn't get them mixed up. Avoid using floating point values even here.
Adding all those rules together, we decided on the following rules. In running code, currencies are stored using an integer for the smallest unit.
class Currency {
String code; // eg "USD"
int value; // eg 2500
boolean converted;
}
class Price {
Currency grossValue;
Currency netValue;
Tax taxRate;
}
In the database, the values are stored as a string in the following format:
USD:2500
That stores the value of $25.00. We were able to do that only because the code that deals with currencies doesn't need to be within the database layer itself, so all values can be converted into memory first. Other situations will no doubt lend themselves to other solutions.
And in case I didn't make it clear earlier, don't use float!
I would think that for a large part your or your client's requirements should dictate what precision and scale to use. For example, for the e-commerce website I am working on that deals with money in GBP only, I have been required to keep it to Decimal( 6, 2 ).
The money datatype on SQL Server has four digits after the decimal.
From SQL Server 2000 Books Online:
Monetary data represents positive or negative amounts of money. In Microsoft® SQL Server™ 2000, monetary data is stored using the money and smallmoney data types. Monetary data can be stored to an accuracy of four decimal places. Use the money data type to store values in the range from -922,337,203,685,477.5808 through +922,337,203,685,477.5807 (requires 8 bytes to store a value). Use the smallmoney data type to store values in the range from -214,748.3648 through 214,748.3647 (requires 4 bytes to store a value). If a greater number of decimal places are required, use the decimal data type instead.
When handling money in MySQL, use DECIMAL(13,2) if you know the precision of your money values or use DOUBLE if you just want a quick good-enough approximate value. So if your application needs to handle money values up to a trillion dollars (or euros or pounds), then this should work:
DECIMAL(13, 2)
Or, if you need to comply with GAAP then use:
DECIMAL(13, 4)
Sometimes you will need to go to less than a cent and there are international currencies that use very large demoniations. For example, you might charge your customers 0.088 cents per transaction. In my Oracle database the columns are defined as NUMBER(20,4)
The money datatype on SQL Server has four digits after the decimal.
From SQL Server 2000 Books Online:
Monetary data represents positive or negative amounts of money. In Microsoft® SQL Server™ 2000, monetary data is stored using the money and smallmoney data types. Monetary data can be stored to an accuracy of four decimal places. Use the money data type to store values in the range from -922,337,203,685,477.5808 through +922,337,203,685,477.5807 (requires 8 bytes to store a value). Use the smallmoney data type to store values in the range from -214,748.3648 through 214,748.3647 (requires 4 bytes to store a value). If a greater number of decimal places are required, use the decimal data type instead.
If you were using IBM Informix Dynamic Server, you would have a MONEY type which is a minor variant on the DECIMAL or NUMERIC type. It is always a fixed-point type (whereas DECIMAL can be a floating point type). You can specify a scale from 1 to 32, and a precision from 0 to 32 (defaulting to a scale of 16 and a precision of 2). So, depending on what you need to store, you might use DECIMAL(16,2) - still big enough to hold the US Federal Deficit, to the nearest cent - or you might use a smaller range, or more decimal places.
A late answer here, but I've used
DECIMAL(13,2)
which I'm right in thinking should allow upto 99,999,999,999.99.
When handling money in MySQL, use DECIMAL(13,2) if you know the precision of your money values or use DOUBLE if you just want a quick good-enough approximate value. So if your application needs to handle money values up to a trillion dollars (or euros or pounds), then this should work:
DECIMAL(13, 2)
Or, if you need to comply with GAAP then use:
DECIMAL(13, 4)
Sometimes you will need to go to less than a cent and there are international currencies that use very large demoniations. For example, you might charge your customers 0.088 cents per transaction. In my Oracle database the columns are defined as NUMBER(20,4)
If you're going to be doing any sort of arithmetic operations in the DB (multiplying out billing rates and so on), you'll probably want a lot more precision than people here are suggesting, for the same reasons that you'd never want to use anything less than a double-precision floating point value in application code.
I would think that for a large part your or your client's requirements should dictate what precision and scale to use. For example, for the e-commerce website I am working on that deals with money in GBP only, I have been required to keep it to Decimal( 6, 2 ).
A late answer here, but I've used
DECIMAL(13,2)
which I'm right in thinking should allow upto 99,999,999,999.99.
4 decimal places would give you the accuracy to store the world's smallest currency sub-units. You can take it down further if you need micropayment (nanopayment?!) accuracy.
I too prefer DECIMAL to DBMS-specific money types, you're safer keeping that kind of logic in the application IMO. Another approach along the same lines is simply to use a [long] integer, with formatting into ¤unit.subunit for human readability (¤ = currency symbol) done at the application level.
Sometimes you will need to go to less than a cent and there are international currencies that use very large demoniations. For example, you might charge your customers 0.088 cents per transaction. In my Oracle database the columns are defined as NUMBER(20,4)
We recently implemented a system that needs to handle values in multiple currencies and convert between them, and figured out a few things the hard way.
NEVER USE FLOATING POINT NUMBERS FOR MONEY
Floating point arithmetic introduces inaccuracies that may not be noticed until they've screwed something up. All values should be stored as either integers or fixed-decimal types, and if you choose to use a fixed-decimal type then make sure you understand exactly what that type does under the hood (ie, does it internally use an integer or floating point type).
When you do need to do calculations or conversions:
When converting a floating point number back to an integer in step 3, don't just cast it - use a math function to round it first. This will usually be round, though in special cases it could be floor or ceil. Know the difference and choose carefully.
Store the type of a number alongside the value
This may not be as important for you if you're only handling one currency, but it was important for us in handling multiple currencies. We used the 3-character code for a currency, such as USD, GBP, JPY, EUR, etc.
Depending on the situation, it may also be helpful to store:
Know the accuracy bounds of the numbers you're dealing with
For real values, you want to be as precise as the smallest unit of the currency. This means you have no values smaller than a cent, a penny, a yen, a fen, etc. Don't store values with higher accuracy than that for no reason.
Internally, you may choose to deal with smaller values, in which case that's a different type of currency value. Make sure your code knows which is which and doesn't get them mixed up. Avoid using floating point values even here.
Adding all those rules together, we decided on the following rules. In running code, currencies are stored using an integer for the smallest unit.
class Currency {
String code; // eg "USD"
int value; // eg 2500
boolean converted;
}
class Price {
Currency grossValue;
Currency netValue;
Tax taxRate;
}
In the database, the values are stored as a string in the following format:
USD:2500
That stores the value of $25.00. We were able to do that only because the code that deals with currencies doesn't need to be within the database layer itself, so all values can be converted into memory first. Other situations will no doubt lend themselves to other solutions.
And in case I didn't make it clear earlier, don't use float!
I would think that for a large part your or your client's requirements should dictate what precision and scale to use. For example, for the e-commerce website I am working on that deals with money in GBP only, I have been required to keep it to Decimal( 6, 2 ).
If you were using IBM Informix Dynamic Server, you would have a MONEY type which is a minor variant on the DECIMAL or NUMERIC type. It is always a fixed-point type (whereas DECIMAL can be a floating point type). You can specify a scale from 1 to 32, and a precision from 0 to 32 (defaulting to a scale of 16 and a precision of 2). So, depending on what you need to store, you might use DECIMAL(16,2) - still big enough to hold the US Federal Deficit, to the nearest cent - or you might use a smaller range, or more decimal places.
We recently implemented a system that needs to handle values in multiple currencies and convert between them, and figured out a few things the hard way.
NEVER USE FLOATING POINT NUMBERS FOR MONEY
Floating point arithmetic introduces inaccuracies that may not be noticed until they've screwed something up. All values should be stored as either integers or fixed-decimal types, and if you choose to use a fixed-decimal type then make sure you understand exactly what that type does under the hood (ie, does it internally use an integer or floating point type).
When you do need to do calculations or conversions:
When converting a floating point number back to an integer in step 3, don't just cast it - use a math function to round it first. This will usually be round, though in special cases it could be floor or ceil. Know the difference and choose carefully.
Store the type of a number alongside the value
This may not be as important for you if you're only handling one currency, but it was important for us in handling multiple currencies. We used the 3-character code for a currency, such as USD, GBP, JPY, EUR, etc.
Depending on the situation, it may also be helpful to store:
Know the accuracy bounds of the numbers you're dealing with
For real values, you want to be as precise as the smallest unit of the currency. This means you have no values smaller than a cent, a penny, a yen, a fen, etc. Don't store values with higher accuracy than that for no reason.
Internally, you may choose to deal with smaller values, in which case that's a different type of currency value. Make sure your code knows which is which and doesn't get them mixed up. Avoid using floating point values even here.
Adding all those rules together, we decided on the following rules. In running code, currencies are stored using an integer for the smallest unit.
class Currency {
String code; // eg "USD"
int value; // eg 2500
boolean converted;
}
class Price {
Currency grossValue;
Currency netValue;
Tax taxRate;
}
In the database, the values are stored as a string in the following format:
USD:2500
That stores the value of $25.00. We were able to do that only because the code that deals with currencies doesn't need to be within the database layer itself, so all values can be converted into memory first. Other situations will no doubt lend themselves to other solutions.
And in case I didn't make it clear earlier, don't use float!
If you're going to be doing any sort of arithmetic operations in the DB (multiplying out billing rates and so on), you'll probably want a lot more precision than people here are suggesting, for the same reasons that you'd never want to use anything less than a double-precision floating point value in application code.
The money datatype on SQL Server has four digits after the decimal.
From SQL Server 2000 Books Online:
Monetary data represents positive or negative amounts of money. In Microsoft® SQL Server™ 2000, monetary data is stored using the money and smallmoney data types. Monetary data can be stored to an accuracy of four decimal places. Use the money data type to store values in the range from -922,337,203,685,477.5808 through +922,337,203,685,477.5807 (requires 8 bytes to store a value). Use the smallmoney data type to store values in the range from -214,748.3648 through 214,748.3647 (requires 4 bytes to store a value). If a greater number of decimal places are required, use the decimal data type instead.
If you were using IBM Informix Dynamic Server, you would have a MONEY type which is a minor variant on the DECIMAL or NUMERIC type. It is always a fixed-point type (whereas DECIMAL can be a floating point type). You can specify a scale from 1 to 32, and a precision from 0 to 32 (defaulting to a scale of 16 and a precision of 2). So, depending on what you need to store, you might use DECIMAL(16,2) - still big enough to hold the US Federal Deficit, to the nearest cent - or you might use a smaller range, or more decimal places.
Source: Stackoverflow.com