What do scale and precision mean when specifying a decimal field type in Doctrine 2?
DatabaseSymfonyOrmTypesDoctrine OrmDatabase Problem Overview
I'm creating a decimal field to hold a financial figure in Doctrine2 for my Symfony2 application.
Currently, it looks like this:
/**
* @ORM\Column(type="decimal")
*/
protected $rate;
When I entered a value and said value was persisted to the database, it was rounded to an integer. I'm guessing that I need to set the precision and scale types for the field, but I need someone to explain exactly what they do?
The Doctrine2 documentation says:
> precision: The precision for a decimal (exact numeric) column (Applies only for decimal column) > > scale: The scale for a decimal (exact numeric) column (Applies only for decimal column)
But that doesn't tell me an awful lot.
I'm guessing precision is the number of decimal places to round to, so I assume that should be 2, but what is scale? Is scale the significant figures?
Should my field declaration be this? :-
/**
* @ORM\Column(type="decimal", precision=2, scale=4)
*/
protected $rate;
Database Solutions
Solution 1 - Database
Doctrine uses types similar to the SQL types. Decimal happens to be a fixed precision type (unlike floats).
Taken from the MySQL documentation:
> In a DECIMAL column declaration, the precision and scale can be (and usually is) specified; for example: > > salary DECIMAL(5,2) > > In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point. > > Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99.
Solution 2 - Database
Just a quick note: I had to remove the quotes from the attributes precision and scale, like so:
@ORM\Column(type="decimal", precision=8, scale=2)
Solution 3 - Database
@Column(type="decimal", precision=5, scale=2) means 123.45
Solution 4 - Database
I know this one is old, but why oh why are programmers still using decimal... just use 64bit integer and define the value as cents (or thousands) and not wholes. Everything simplifies from that.
i.e. instead of storing 123.45 just store 12345, do all calculations on integers and when needed present the value to user as 123.45
P.S. And at least for now no-one has 2305843009213693952 cents or 23.058.430.092.136.939,52 wholes on their account.
Solution 5 - Database
* @ORM\Column(type="decimal", precision=10, scale=2)