Data Types
A shop stores prices in a column of approximate floating-point numbers. One day the accountant adds up a thousand small payments and the total is off by a fraction of a paisa — enough to fail an audit. Another shop stores phone numbers as integers and loses every leading zero. Both mistakes were made the moment someone picked a column type.
The rule
Choose the narrowest type that honestly fits every value.
Every column has a data type. The type decides which values are allowed, how they sort and compare, and how much space they take. The database rejects values that do not fit, which is your first line of defence against bad data.
The everyday types, with names that work in most databases:
| Type | Holds | Shop example |
|---|---|---|
INTEGER / BIGINT |
whole numbers | quantity, customer_id |
DECIMAL(p, s) |
exact decimals: p digits, s after the point |
price DECIMAL(10, 2) |
REAL / FLOAT |
approximate decimals | a sensor reading |
VARCHAR(n) |
text up to n characters |
name VARCHAR(100) |
CHAR(n) |
text of exactly n characters, padded |
a 2-letter country code |
TEXT |
long text (not in every database's standard set) | a product description |
DATE, TIME, TIMESTAMP |
a day, a clock time, both together | joined_on DATE |
BOOLEAN |
true or false | is_active |
DECIMAL(10, 2) holds up to 10 digits, 2 of them after the point, so its largest value is 99999999.99. Floating-point types store numbers in binary and cannot represent many decimals exactly, which is why they are wrong for money.
Missing values are not a type: any column can hold NULL (meaning "unknown") unless you forbid it.
The shortcut
Ask one question of the column: what is it? Then map the answer.
- A count or an id →
INTEGER - Money →
DECIMAL - A measurement where tiny rounding is fine →
REAL - Words, codes, phone numbers →
VARCHAR - A day or moment →
DATEorTIMESTAMP - Yes or no →
BOOLEAN
product_id
An id you count with.
INTEGER.
The trap is "looks like a number". A phone number or PIN code is made of digits, but you never add two of them, and leading zeros matter. It is text.
Drills
Answer fast — trust the rule.
Which type should hold a product price like 1299.50?
A customer's phone number is 09876543210. Which type keeps it intact?
What is the largest value a DECIMAL(5, 2) column can hold?
The exact date and time an order was placed should be stored as…
Remember
- Choose the narrowest type that honestly fits every value.
- Ask "what is it?": count, money, measurement, words, moment, yes/no.
- Digits are not always numbers — phone numbers and codes are text; money is never FLOAT.