Joseph Michael wrote:. . . we don't end up with any precision issues or rounding issues or missing amount? . . .
Well, that's decimal and float gone for a Burton, isn't it? Does the format stored affect the number of additions possible? Doesn't it depend on the size of the individual data? What would you expect decimal(5, 2) or decimal(5, 2) to store?
I said decimal yesterday: that should have been double.
The number of transactions really doesn't matter. Even if you only had one transaction, you'd want to get it right.
And you're talking about the format in which you're going to store permanent data, aren't you? So that isn't going to involve any precision or rounding issues anyway, just choose the right length of field (not too short) and the right number of decimal places. Those other issues are going to arise during the course of calculations (and there's plenty of things which could go wrong), but just make sure you've taken care of them before storing the result in the database.
Fractions? You're going to have to figure out what that means. If I were creating a database to hold monetary values I would use a field with two decimal places. But you still haven't mentioned money so I expect it's something else. And as usual without knowing the requirements it's hard to make recommendations.
If the number of decimals after the dot is fixed, there's nothing preventing you from using an integer number. 314 will not mean $314 but instead $3.14. You just need to remember to add the dot at the correct place when you need to show it in reports or to the user.
Whatever you do, do not use double or float. They are imprecise, and you will end up losing money (literally - money will just disappear). An example:
The end result is not 1.0 but instead 0.9999999999999999. If you round that incorrectly, that's a cent you've lost.
If I recall correctly, decimal does not have the same problem, but in .NET it's apparently quite a bit slower than double or int. In Java you'd need to use BigDecimal which is definitely a lot slower. I have no idea what impact using decimals has on your database server.
But even if you have to add millions of records, you aren't going to have any problems about slow performance. You will have serious problems if you use doubles, as shown by Rob's arithmetic, which is a well‑known example.
Campbell Ritchie wrote:But even if you have to add millions of records, you aren't going to have any problems about slow performance. You will have serious problems if you use doubles, as shown by Rob's arithmetic, which is a well‑known example.
But that's only a problem if you need precise calculations. Which we have no idea what these numbers are for, since we haven't been told anything about them. For all we know they might be the GPS coordinates of locations in the plan for a hydroelectric dam. And even then your comments and Rob's are about what to do in the process of calculation with the numbers, which wasn't part of the question. All we were asked was how to store them in the database.
posted 3 months ago
I was presuming that precise calculations were necessary since the first post said.
. . . precision issues or rounding issues or missing amount . . .
It would have been better if I hadn't made a mistake in my first post.
I brought this back from the farm where they grow the tiny ads:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop