Keys, Dates, Time Zones, UUIDs and Schema Design
Introduction
Traditional Oracle applications were primarily designed for:
- Single-region deployments
- On-premise servers
- Oracle Forms applications
- Monolithic systems
Modern Java applications are different:
- Cloud deployments
- Microservices
- Multi-region users
- Mobile applications
- Global customers
- REST APIs
- Event-driven architectures
As a result, schema design decisions made today directly affect:
- Scalability
- Performance
- Maintainability
- API design
- JPA implementation
- Multi-region support
This article focuses on practical design decisions that every backend developer should understand.
Primary Key Design
One of the first questions while creating a table is:
What should be the primary key?
There are four common approaches.
| Strategy | Example | Recommendation |
|---|---|---|
| Sequence | EMP_ID NUMBER | Excellent |
| Identity | GENERATED AS IDENTITY | Good |
| UUID | 550e8400-e29b | Very Good |
| Composite Key | ORDER_ID + ITEM_ID | Situational |
Sequence-Based Keys
Traditional Oracle applications use sequences.
CREATE SEQUENCE EMP_SEQ;
Table:
EMP_ID NUMBER(19)
Advantages:
- Small indexes
- Fast joins
- Excellent performance
- Ordered values
Disadvantages:
- IDs predictable
- Database-generated
- Difficult for distributed systems
UUID-Based Keys
Modern microservices increasingly use UUIDs.
Example:
550e8400-e29b-41d4
Java:
UUID id = UUID.randomUUID();
Database:
EMP_ID VARCHAR2(36)
Advantages:
- Globally unique
- Generated by application
- Suitable for distributed systems
- No database round trip
Disadvantages:
- Larger indexes
- Larger storage
- Reduced index locality
Recommended UUID Storage
Avoid:
VARCHAR2(100)
Prefer:
VARCHAR2(36)
or:
RAW(16)
Java:
private UUID id;
When to Use Sequence vs UUID
| Scenario | Recommendation |
|---|---|
| Banking | Sequence |
| ERP | Sequence |
| Monolith | Sequence |
| Microservices | UUID |
| Distributed systems | UUID |
| Event-driven systems | UUID |
Composite Keys
Composite keys consist of multiple columns.
Example:
ORDER_ID
PRODUCT_ID
Example
PRIMARY KEY
(
ORDER_ID,
PRODUCT_ID
)
When Composite Keys Make Sense
Good examples:
- Order Items
- User Roles
- Student Courses
- Product Categories
JPA Composite Key
@Embeddable
public class OrderItemId {
private Long orderId;
private Long productId;
}
Entity:
@EmbeddedId
private OrderItemId id;
Composite Key Best Practices
Use composite keys when:
✅ Relationship table
✅ Natural uniqueness exists
Avoid when:
❌ Large tables
❌ Frequently joined tables
❌ Multiple foreign keys
Date Storage Best Practices
One of the most misunderstood areas in enterprise systems is date storage.
Typical questions:
- Should time zones be stored?
- Should UTC be used?
- Should DATE or TIMESTAMP be used?
- How do multiple countries work?
Oracle DATE
Oracle DATE stores:
- Year
- Month
- Day
- Hour
- Minute
- Second
CREATED_DATE DATE
TIMESTAMP
CREATED_TS TIMESTAMP
Adds:
- Fractional seconds
Recommended for audit columns.
TIMESTAMP WITH TIME ZONE
LOGIN_TIME
TIMESTAMP WITH TIME ZONE
Stores:
- Date
- Time
- Zone offset
Example:
2026-06-20 10:30 +05:30
Do We Need Time Zones?
The answer is:
Usually no.
Most enterprise applications can standardize on UTC.
Recommended Architecture
Database
Store everything in UTC.
Java
Use:
Instant
or:
OffsetDateTime
UI
Convert to user locale.
Example
User in India:
10:00 AM IST
Stored in database:
04:30 UTC
User in London:
05:30 AM GMT
Same instant.
Recommended Java Types
| Business Meaning | Java Type |
|---|---|
| Date only | LocalDate |
| Time only | LocalTime |
| Timestamp | LocalDateTime |
| Global instant | Instant |
| Offset required | OffsetDateTime |
| Duration | Duration |
JPA Date Mapping
Date only
private LocalDate joiningDate;
Timestamp
private LocalDateTime createdDate;
UTC Timestamp
private Instant createdTime;
Audit Columns
Every table should have:
CREATED_DATE
CREATED_BY
UPDATED_DATE
UPDATED_BY
Optional:
VERSION
for optimistic locking.
Multi-Locale Applications
Applications should not store:
20/06/2026
or:
06/20/2026
Store:
2026-06-20T10:30:00Z
Display according to locale.
Locale Examples
India:
20-Jun-2026
US:
Jun-20-2026
Germany:
20.06.2026
Database remains unchanged.
Recommended Oracle Types
| Oracle Type | Java Type | Recommendation |
|---|---|---|
| NUMBER(19) | Long | Primary keys |
| VARCHAR2(50) | String | Names |
| VARCHAR2(200) | String | |
| NUMBER(10,2) | BigDecimal | Money |
| DATE | LocalDateTime | Legacy |
| TIMESTAMP | LocalDateTime | Recommended |
| TIMESTAMP WITH TZ | OffsetDateTime | Global apps |
| CLOB | String | Large text |
| BLOB | byte[] | Files |
Monetary Values
Never use:
double salary;
Use:
BigDecimal salary;
Database:
SALARY NUMBER(12,2)
Reason:
- Precise calculations
- No floating-point errors
Recommended Table Template
EMPLOYEE
---------
EMP_ID
NAME
EMAIL
STATUS
CREATED_DATE
CREATED_BY
UPDATED_DATE
UPDATED_BY
VERSION
Modern Backend Recommendations
Monolith
- Sequence
- NUMBER(19)
- LocalDateTime
Microservices
- UUID
- Instant
- UTC timestamps
Global Applications
- UTC persistence
- Locale-based display
- OffsetDateTime if required
Golden Rules
✅ Store timestamps in UTC.
✅ Convert dates only at the UI layer.
✅ Use LocalDate for business dates.
✅ Use Instant for event timestamps.
✅ Use BigDecimal for money.
✅ Prefer sequence keys for Oracle applications.
✅ Use UUID for distributed systems.
✅ Use composite keys only for relationship tables.
✅ Always include audit columns.
✅ Never store formatted dates.
Interview Questions
Should we store timezone in the database?
Usually no. Store UTC and convert in the application.
When should UUID be used?
Distributed systems and microservices.
Why avoid composite keys?
Complex joins and JPA mappings.
Why use BigDecimal?
Accurate financial calculations.
Why use Instant?
Represents an absolute moment independent of geography.
A well-designed schema can remain stable for decades, while application frameworks may change every few years. Strong database design decisions significantly reduce technical debt and improve the long-term maintainability of enterprise applications.
