A Practical Guide for Java Backend Developers

One of the biggest mistakes made during schema design is choosing inappropriate data types. A poor datatype decision can impact:

• Storage requirements
• Query performance
• Index size
• JPA mappings
• Application memory usage
• Future scalability

This guide provides:

✅ Complete Oracle datatype reference
✅ Minimum and maximum sizes
✅ Java mappings
✅ When to use each datatype
✅ Primary key recommendations
✅ Date and timezone best practices
✅ Column sizing recommendations
✅ Constraints and schema design guidelines

---

Oracle Character Data Types

Oracle Type	Min Size	Max Size	Java Type	Example	Recommended Usage
CHAR(n)	1 byte	2000 bytes	String	CHAR(2)	Fixed length values
VARCHAR2(n)	1 byte	4000 bytes	String	VARCHAR2(100)	Most text columns
NCHAR(n)	1 char	2000 chars	String	NCHAR(20)	Unicode fixed text
NVARCHAR2(n)	1 char	4000 chars	String	NVARCHAR2(200)	Unicode variable text
LONG	1 byte	2 GB	String	LONG	Deprecated

---

CHAR

COUNTRY_CODE CHAR(2)

Good examples:

IN
US
UK

Use when:

• ISO country codes
• Status flags
• Fixed-length values

Avoid for:

• Names
• Addresses
• Descriptions

---

VARCHAR2

FIRST_NAME VARCHAR2(100)

Recommended for:

• Names
• Email
• Address
• Status
• URLs

---

Oracle Numeric Data Types

Oracle Type	Precision	Scale	Java Type	Example	Usage
NUMBER	38 digits	Variable	BigDecimal	NUMBER	Generic numeric
NUMBER(p)	1-38	0	Integer/Long	NUMBER(10)	IDs
NUMBER(p,s)	1-38	0-127	BigDecimal	NUMBER(10,2)	Money
BINARY_FLOAT	32-bit	Floating	Float	BINARY_FLOAT	Scientific values
BINARY_DOUBLE	64-bit	Floating	Double	BINARY_DOUBLE	Calculations

---

NUMBER Recommendations

Usage	Oracle Type	Java Type
Age	NUMBER(3)	Integer
Count	NUMBER(10)	Integer
ID	NUMBER(19)	Long
Salary	NUMBER(12,2)	BigDecimal
Percentage	NUMBER(5,2)	BigDecimal

---

Monetary Values

Bad:

double salary;

Good:

BigDecimal salary;

Database:

SALARY NUMBER(12,2)

Reason:

• Avoid floating-point errors.
• Required for financial systems.

---

Oracle Date and Time Types

Oracle Type	Storage	Java Type	Usage
DATE	7 bytes	LocalDateTime	Legacy timestamp
TIMESTAMP	11 bytes	LocalDateTime	Recommended
TIMESTAMP(6)	11 bytes	LocalDateTime	Microseconds
TIMESTAMP WITH TIME ZONE	13 bytes	OffsetDateTime	Global applications
TIMESTAMP WITH LOCAL TIME ZONE	11 bytes	OffsetDateTime	Session timezone
INTERVAL YEAR TO MONTH	Variable	Period	Durations
INTERVAL DAY TO SECOND	Variable	Duration	Time intervals

---

Recommended Java Date Mapping

Business Requirement	Java Type	Database Type
Birth date	LocalDate	DATE
Joining date	LocalDate	DATE
Audit timestamp	LocalDateTime	TIMESTAMP
Event time	Instant	TIMESTAMP
Global event	OffsetDateTime	TIMESTAMP WITH TIME ZONE

---

Should Time Zones Be Stored?

Most enterprise applications should standardize on UTC.

Recommended architecture:

Database

2026-06-20T10:30:00Z

Java

Instant createdTime;

UI

Convert to local timezone.

Benefits:

• Single storage format.
• Easy comparisons.
• Multi-region support.
• No daylight saving issues.

---

Large Object Types

Oracle Type	Maximum Size	Java Type	Usage
CLOB	128 TB	String	Articles, XML
NCLOB	128 TB	String	Unicode text
BLOB	128 TB	byte[]	Images, PDFs
BFILE	External	File	External files

---

CLOB Example

DESCRIPTION CLOB

Java:

@Lob
private String description;

---

BLOB Example

DOCUMENT BLOB

Java:

@Lob
private byte[] document;

---

RAW Data Types

Oracle Type	Size	Java Type	Usage
RAW	1–2000 bytes	byte[]	Binary data
LONG RAW	2 GB	byte[]	Deprecated
RAW(16)	16 bytes	UUID	UUID storage

---

UUID Storage

EMP_ID RAW(16)

Java:

private UUID id;

Advantages:

• Smaller than VARCHAR2(36).
• Faster indexes.

---

Row Identifier Types

Type	Size	Usage
ROWID	10 bytes	Physical row location
UROWID	Variable	Universal row ID

Used mainly by DBAs.

---

Recommended Column Sizes

Column	Recommended Size
First Name	VARCHAR2(50)
Last Name	VARCHAR2(50)
Email	VARCHAR2(200)
Phone	VARCHAR2(20)
Country Code	CHAR(2)
Status	VARCHAR2(20)
Address	VARCHAR2(500)
URL	VARCHAR2(500)
Description	CLOB
Currency Code	CHAR(3)

---

VARCHAR Best Practices

Bad:

NAME VARCHAR2(4000)

Problems:

• Larger indexes.
• Wasted storage.
• Poor performance.

Good:

NAME VARCHAR2(100)

---

File Metadata Storage

Store metadata in database.

FILE_NAME VARCHAR2(255)
CONTENT_TYPE VARCHAR2(100)
FILE_SIZE NUMBER(12)
FILE_PATH VARCHAR2(500)

Store actual file:

• S3
• NAS
• Object storage

Avoid storing large files in BLOB unless required.

---

Primary Key Strategies

Type	Oracle	Java	Recommended
Sequence	NUMBER(19)	Long	Yes
Identity	NUMBER	Long	Yes
UUID String	VARCHAR2(36)	UUID	Good
UUID Binary	RAW(16)	UUID	Excellent
Composite	Multiple columns	EmbeddedId	Situational

---

Sequence Example

EMP_ID NUMBER(19)

Java:

Long id;

Best for:

• Oracle applications
• Monoliths
• Banking systems

---

UUID Example

EMP_ID RAW(16)

Java:

UUID id;

Best for:

• Microservices
• Distributed systems

---

Composite Keys

Example:

PRIMARY KEY
(
    ORDER_ID,
    PRODUCT_ID
)

Use when:

• Relationship tables
• Junction tables

Avoid for:

• Large entities
• Frequently joined tables

---

Recommended Constraints

Primary Key

CONSTRAINT PK_EMP
PRIMARY KEY (EMP_ID)

---

Foreign Key

CONSTRAINT FK_DEPT
FOREIGN KEY(DEPT_ID)
REFERENCES DEPARTMENT(DEPT_ID)

---

Unique

EMAIL VARCHAR2(200)
UNIQUE

---

Check

CHECK(SALARY > 0)

---

Not Null

NAME VARCHAR2(100)
NOT NULL

---

Recommended Audit Columns

Every enterprise table:

CREATED_DATE TIMESTAMP
CREATED_BY VARCHAR2(50)

UPDATED_DATE TIMESTAMP
UPDATED_BY VARCHAR2(50)

VERSION NUMBER

Java:

private LocalDateTime createdDate;
private LocalDateTime updatedDate;
private Integer version;

---

Standard Enterprise Table

EMPLOYEE
---------
EMP_ID NUMBER(19)
FIRST_NAME VARCHAR2(50)
LAST_NAME VARCHAR2(50)
EMAIL VARCHAR2(200)
STATUS VARCHAR2(20)
SALARY NUMBER(12,2)

CREATED_DATE TIMESTAMP
CREATED_BY VARCHAR2(50)

UPDATED_DATE TIMESTAMP
UPDATED_BY VARCHAR2(50)

VERSION NUMBER

---

Golden Rules

✅ Use VARCHAR2 instead of CHAR.

✅ Specify appropriate lengths.

✅ Use NUMBER(19) for IDs.

✅ Use BigDecimal for money.

✅ Use TIMESTAMP for audit columns.

✅ Standardize timestamps in UTC.

✅ Use LocalDate for business dates.

✅ Use UUID only when necessary.

✅ Use composite keys only for junction tables.

✅ Add NOT NULL wherever possible.

✅ Index primary and foreign keys.

✅ Always include audit columns.

---

Final Recommendation Matrix

Requirement	Oracle Type	Java Type
Primary Key	NUMBER(19)	Long
UUID Key	RAW(16)	UUID
Name	VARCHAR2(100)	String
Email	VARCHAR2(200)	String
Amount	NUMBER(12,2)	BigDecimal
Date Only	DATE	LocalDate
Timestamp	TIMESTAMP	LocalDateTime
Global Timestamp	TIMESTAMP WITH TZ	OffsetDateTime
Large Text	CLOB	String
File	BLOB	byte[]

A properly designed schema can remain stable for decades, while applications, frameworks, and APIs evolve over time. The database design decisions made today directly influence performance, maintainability, and scalability for years to come.