Part 8: Numeric Data Types: Integers, Decimals & Floats

Selecting the correct numeric data type directly impacts data accuracy and storage efficiency.

1. Integer Types

• SMALLINT: 2 bytes (-32,768 to 32,767)
• INT / INTEGER: 4 bytes (-2.14 billion to +2.14 billion)
• BIGINT: 8 bytes (essential for high-volume primary keys and event logs)

2. Exact Numeric: DECIMAL(P, S) / NUMERIC(P, S)

Golden Rule: Always use DECIMAL for monetary and financial calculations!

• P (Precision): Total count of digits.
• S (Scale): Number of digits after the decimal point.
• DECIMAL(10, 2): Up to 99,999,999.99.

3. Inexact (Floating-Point): FLOAT / REAL / DOUBLE PRECISION

Use for scientific measurements where fractional rounding discrepancies are acceptable, but NEVER for money:

-- ❌ DANGEROUS: 0.1 + 0.2 in floating point might equal 0.30000000000000004
--  CORRECT: DECIMAL(12, 4) guarantees exact arithmetic

Key Takeaways

• Use BIGINT for scalable auto-increment primary keys.
• Always use DECIMAL for currency to prevent rounding errors.

In Part 9, we explore Character Data Types: CHAR, VARCHAR, and TEXT!