How Do You Choose Numeric Data Type Precision in a Database?
Learn how to choose numeric data type precision in a database, and why DECIMAL beats FLOAT for money and percentages.
Expected Interview Answer
Choosing numeric precision means matching a column’s storage size and rounding behavior to the exact range and accuracy the data needs — using fixed-point types like DECIMAL for money and identifiers, and floating-point types like FLOAT or DOUBLE only for scientific values where tiny rounding error is acceptable.
Every numeric type trades storage size against range and exactness. INT gives exact whole numbers within a fixed byte range, DECIMAL(p,s) stores an exact number of total digits (p) and digits after the decimal point (s) so money never drifts, and FLOAT/DOUBLE store an approximate binary representation that is compact and fast but can misrepresent values like 0.1 exactly. Picking too small a type risks overflow or truncation errors in production; picking an unnecessarily large or imprecise type wastes storage or introduces rounding bugs that surface only under scale.
- Prevents silent overflow or truncation errors
- Avoids floating-point rounding drift in financial data
- Keeps storage and index size proportional to actual need
- Makes query results deterministic and reproducible
AI Mentor Explanation
A scoreboard tracking runs uses whole numbers because you cannot score a fraction of a run, so an integer column is the natural fit, just like a batter tally. But a batting average like 47.83 needs a fixed number of decimal digits agreed upon by the scorers, not an approximate binary guess, or two scorers could compute slightly different averages from the same innings. Choosing DECIMAL with a fixed scale for the average mirrors how a scorer fixes exactly two decimal places by convention, so every recalculation lands on the identical figure.
Step-by-Step Explanation
Step 1
Identify the value domain
Determine whether the column needs whole numbers, exact fractional amounts, or approximate scientific magnitudes.
Step 2
Pick exact types for money and counts
Use INT/BIGINT for whole counts and DECIMAL(p,s) for currency, percentages, and any value requiring exact arithmetic.
Step 3
Reserve floating-point for approximations
Use FLOAT or DOUBLE only for scientific or sensor data where small binary rounding error is acceptable and speed matters.
Step 4
Size the range and scale explicitly
Set DECIMAL precision and scale, or choose INT vs BIGINT, based on the maximum expected value plus headroom for growth.
What Interviewer Expects
- Distinction between exact fixed-point and approximate floating-point storage
- A clear rule of thumb: DECIMAL for money, FLOAT/DOUBLE for science
- Awareness that binary floating-point cannot represent many decimal fractions exactly
- Consideration of overflow risk when sizing integer columns
Common Mistakes
- Storing currency values in FLOAT or DOUBLE
- Choosing an integer type too small for the expected value range
- Assuming DECIMAL and FLOAT are interchangeable for precision
- Ignoring storage cost trade-offs when over-provisioning precision
Best Answer (HR Friendly)
“I choose numeric precision by matching the type to what the data actually needs — whole counts get an integer type, and anything involving money or percentages gets a fixed-point DECIMAL so the numbers never drift from rounding. I only reach for FLOAT or DOUBLE when the value is inherently approximate, like a scientific measurement, where a tiny rounding difference does not matter.”
Code Example
CREATE TABLE Orders (
order_id BIGINT PRIMARY KEY,
item_count INT NOT NULL, -- exact whole count
unit_price DECIMAL(10, 2) NOT NULL, -- exact currency, never FLOAT
discount_pct DECIMAL(5, 2) NOT NULL, -- exact percentage, e.g. 12.50
sensor_temp_c DOUBLE -- approximate scientific reading, FLOAT ok
);
-- Demonstrates why FLOAT is unsafe for money:
SELECT 0.1::float8 + 0.2::float8; -- may not print exactly 0.3
SELECT 0.1::numeric + 0.2::numeric; -- prints exactly 0.3Follow-up Questions
- Why can floating-point types not represent every decimal fraction exactly?
- What happens if you insert a value that exceeds a column's DECIMAL precision?
- When is FLOAT or DOUBLE actually the right choice in a schema?
- How does choosing INT vs BIGINT affect index size and storage?
MCQ Practice
1. Which type should a currency column use to avoid rounding drift?
DECIMAL stores an exact fixed-point number of digits, avoiding the binary approximation error inherent to FLOAT and DOUBLE.
2. Why is 0.1 + 0.2 sometimes not exactly 0.3 in a database column?
Binary floating-point cannot represent many decimal fractions exactly, so tiny representation errors appear in arithmetic.
3. What do the two numbers in DECIMAL(10, 2) represent?
The first number is total precision (digit count) and the second is scale (digits after the decimal point).
Flash Cards
When should you use DECIMAL over FLOAT? — Whenever exact arithmetic matters, such as money, percentages, or any value requiring reproducible rounding.
What does DECIMAL(10,2) mean? — 10 total significant digits, with 2 of them after the decimal point.
Why is FLOAT risky for currency? — It stores an approximate binary representation, so repeated arithmetic can drift from the true decimal value.
When is FLOAT/DOUBLE an appropriate choice? — For approximate scientific or sensor data where small rounding error is acceptable and speed/compactness matter.