Double Precision as default data type for amounts

Questions and answers regarding general SQL and backend databases

Double Precision as default data type for amounts

Postby Bernd.N » Sun Dec 27, 2015 9:53 pm

Until today I did not pay attention to the fact that Servoy creates a double precision data type when I add a numeric field to a table.
When I imported Euro amounts today from a CSV file into a table I use in Servoy, I noticed that some amounts showed up with more than two digits after the point (see screenshot).

Now I worry if that could lead to potential problems in a business software that mainly deals with amounts that have exactly two decimal places.
My feeling is that such amounts should not be stored in double precision fields.

So I would like to ask about experience regarding this matter. Should double precision fields be changed to numeric ones in the data structure?
Especially accounting software must be always exact up to the cent.
Attachments
double precision.png
double precision.png (12.28 KiB) Viewed 1577 times
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 480
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Double Precision as default data type for amounts

Postby mboegem » Mon Dec 28, 2015 1:39 pm

Numeric stores data more precise than double precision, but can still store more than 2 decimals (depending on the definition)
I wouldn't limit my database to 'just' 2 decimals if there's a possibility I could sell it to a customer using more than 2 decimals.

Apart from DB storage issues, javascript has its share on this as well
Have a look at this older (but still applicable) post:
viewtopic.php?f=22&t=14057

So I think you need to change your focus, to fully cover this issue.
_____________________
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance SAN Developer

Image
User avatar
mboegem
 
Posts: 1386
Joined: Sun Oct 14, 2007 1:34 pm
Location: Hoofddorp, The Netherlands

Re: Double Precision as default data type for amounts

Postby marco.rossi » Mon Dec 28, 2015 2:22 pm

Hi Bernd,

I would not trunkate the number by changing the field type.
If you do that, you can risk to lose at least one decimal into the writing phase.

Ex:
If you try to write the number 39.345 on a "two digits" field, you risk to obtain 39.34 that it's not correct.
This behavior can happen on certain database type (I don't know if you are using postgresql or other kind
of database).

To avoid that or other kind of exceptions you can:

1 - Leave the field as it is (allowed to store more than two decimals) and convert the number on reading operations.
1.1 You can do that by 'Conversion' functionality built in Servoy on every field
1.2 Or you can create a view on your database as short representation of your full data.

2 - You can intercept by code the writing operation on the requested fields to have a rounded number to write on the database

I suggest you to choose the option 1.1 if you use a lot the foundset as Connector to your database.
Instead if you use a lot of datasets and the rawplugins to write/update data, I suggest the option 1.2 or the 2

Hope this help
Marco Rossi
Senior Analyst Developer
Freelance Consultant

IT Manager @Mantho
Webmaster @Sitoliquido
marco.rossi
 
Posts: 40
Joined: Sun Apr 12, 2015 9:33 pm

Re: Double Precision as default data type for amounts

Postby achiary » Mon Dec 28, 2015 8:59 pm

Hi Berndt,

Regarding internal calculations I suggest to use a rounding routine similar to the one mentioned by marc Boegem in viewtopic.php?f=22&t=14057 .

Regarding Database data types, I suggest to use numeric with 2 decimals for money amounts; the rule here is : store with the precission your end user expects to see data.

This will also avoid future problems when doing reports in which you add multiple rows (Servoy aggregations, Jasper totals, etc.)
achiary
 
Posts: 81
Joined: Wed Nov 14, 2007 3:29 pm
Location: Argentina

Re: Double Precision as default data type for amounts

Postby david » Mon Dec 28, 2015 9:37 pm

Servoy only has a few data type choices (TEXT, NUMBER, DATETIME, BLOB, etc) each of which defaults to one specific type depending on your database vendor and JDBC driver version. Your database has many options for each of these types. So if you're just creating columns via Servoy you're going to run into all kinds of issues with all of your column types — from calculations, formatting, indexing, performance, etc. Know your database and use the correct types — don't rely on Servoy's defaults.

For monetary stuff, always use an exact number type with two decimals. For MySQL we go with DECIMAL(19,2). Doing simple calculations always start by multiplying by 100 to get to an integer, do your simple maths on integers, then divide by 100 to store back as a decimal type.

Recommend using JS libraries (many out there) for more advanced financial calculations, currency formatting and currency conversion instead of rolling your own. Integrating into Servoy can be done with litte work usually — an example integrating a fractions library: https://www.servoyforge.net/projects/fractions/wiki
David Workman, Kabootit

Image
Everything you need to build great apps with Servoy
User avatar
david
 
Posts: 1724
Joined: Thu Apr 24, 2003 4:18 pm
Location: Washington, D.C.

Re: Double Precision as default data type for amounts

Postby Bernd.N » Tue Dec 29, 2015 6:48 am

Thanks for all your valueable contributions!
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 480
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Double Precision as default data type for amounts

Postby Bernd.N » Sun May 08, 2016 11:18 am

Ok, for Servoy beginners, I posted the way to add other data types you can not create inside Servoy here:
https://www.servoy.com/forum/viewtopic.php?f=12&t=21299
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 480
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 5 guests