Choosing the right data type is one of the most important decisions when designing a database schema. It affects storage size, performance, data integrity, and future maintainability. Here’s a practical guide to help you decide.

---

1. Start With the Nature of the Data

Ask yourself: What kind of value will this column hold?

Data Nature	Typical SQL Types
Whole numbers	SMALLINT, INT, BIGINT
Decimal / monetary	DECIMAL(p,s) / NUMERIC(p,s)
Approximate numbers	REAL, DOUBLE PRECISION, FLOAT
Short text	VARCHAR(n), CHAR(n)
Long text	TEXT, CLOB
Dates & times	DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE
Boolean flags	BOOLEAN (or BIT / TINYINT in some DBs)
Binary data	BLOB, BYTEA, VARBINARY
Identifiers (UUIDs)	UUID (Postgres), CHAR(36), BINARY(16)
Structured JSON	JSON, JSONB (Postgres)

---

2. Match the Range and Precision

Pick the smallest type that safely fits your data — but don’t over-optimize prematurely.

Integers

• SMALLINT → -32,768 to 32,767 (age, small counters)
• INT → ~±2.1 billion (most IDs, counts)
• BIGINT → for very large IDs or high-volume tables

Decimals

• Use DECIMAL(p, s) for money and anything requiring exact arithmetic.

price DECIMAL(10, 2)  -- up to 99,999,999.99

• Avoid FLOAT/DOUBLE for financial data — rounding errors will bite you.

Strings

• Use VARCHAR(n) when you know a reasonable maximum length.
• Use TEXT for free-form or unbounded text (descriptions, comments).
• Use CHAR(n) only for truly fixed-length values (e.g., ISO country codes CHAR(2)).

---

3. Prefer Semantic Types Over Generic Ones

If your database offers a specialized type, use it — it enforces integrity and enables optimizations.

• DATE instead of VARCHAR for dates
• BOOLEAN instead of CHAR(1) with 'Y'/'N'
• UUID instead of VARCHAR(36)
• INET / CIDR for IP addresses (Postgres)
• JSONB instead of TEXT for JSON payloads (Postgres)

---

4. Consider Time Zones for Timestamps

• TIMESTAMP → stores no time zone; ambiguous across regions.
• TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ in Postgres) → recommended for anything user-facing or distributed.

created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()

---

5. Think About NULL vs NOT NULL and Defaults

The type alone isn’t enough — pair it with proper constraints:

email       VARCHAR(255) NOT NULL,
status      VARCHAR(20)  NOT NULL DEFAULT 'ACTIVE',
deleted_at  TIMESTAMP WITH TIME ZONE NULL

---

6. Watch Out for Common Pitfalls

Anti-pattern	Better choice
FLOAT for money	DECIMAL(p, s)
VARCHAR for dates	DATE / TIMESTAMP
TEXT for everything	VARCHAR(n) with a sane limit
CHAR(1) 'Y'/'N'	BOOLEAN
INT for phone numbers	VARCHAR(20) (leading zeros, +, formatting)
VARCHAR(255) reflexively	Choose a length that reflects the domain

---

7. Align With Your Application Layer

Keep the SQL type consistent with the Java field type:

Java/Kotlin	Recommended SQL
Long / Int	BIGINT / INT
BigDecimal	DECIMAL(p, s)
String	VARCHAR(n) or TEXT
LocalDate	DATE
LocalDateTime	TIMESTAMP
OffsetDateTime / Instant	TIMESTAMP WITH TIME ZONE
UUID	UUID (or BINARY(16))
Boolean	BOOLEAN
enum	VARCHAR (with @Enumerated(EnumType.STRING))

---

8. A Quick Decision Checklist

Before finalizing a column type, ask:

1. What values will it hold, and what’s the realistic range?
2. Exact or approximate arithmetic required?
3. Fixed or variable length?
4. Time-zone aware or not?
5. Does the DB offer a native type (JSON, UUID, INET, etc.)?
6. Does it match the application layer type cleanly?
7. Is the column indexed / searched / joined on? Smaller types = faster indexes.
8. Will it be NULLable? What’s the default?

---

TL;DR

Pick the most specific, smallest, semantically correct type that fits the data — and align it with both your business rules and your ORM mappings.