Persistence with PostgreSQL
Relational Database Thinking
In the previous lesson, we established why our application's durable state must be persisted in PostgreSQL.
Now we need a mindset shift.
In Java, we naturally think in objects:
Product
Inventory
Order
OrderItem
and relationships often look like object graphs:
Order
↓
List<OrderItem>
PostgreSQL sees the same business data differently:
tables
rows
columns
keys
constraints
relationships
A backend engineer needs to understand both representations because the persistence layer effectively translates between:
domain/application model
↔
relational model
The goal of this lesson is:
Shift from Java object thinking to relational thinking so we can model our Domain correctly using tables, rows, keys, relationships, constraints, and joins.
Objects and Rows Are Different Models
In Java, we might have:
Product product =
Product.create(
productId,
"Keyboard",
10000
);
This is an object.
It can have:
identity
state
methods
business behaviour
For example:
product.changePrice(
12000
);
product.deactivate();
In PostgreSQL, the same Product may be represented as a row:
products
id | name | price_cents | active
-------------------------------------+----------+-------------+-------
59ba787c-6d83-4a21-9bae-19c3d0469bd5 | Keyboard | 10000 | true
The values are familiar.
The model is different.
Java Thinks in Behaviour-Rich Objects
A Product can contain:
ProductId
name
priceCents
active
plus behaviour such as:
product.changePrice(
12000
);
product.deactivate();
A PostgreSQL row does not execute:
product.deactivate();
It stores durable state.
When Domain behaviour changes:
active = true
to:
active = false
persistence eventually translates that into something conceptually like:
UPDATE products
SET active = false
WHERE id = :productId;
The Domain operation and SQL mutation are connected.
They are not the same responsibility.
Think in Relations
In relational database theory, a relation is conceptually a set of rows with defined attributes.
In practical PostgreSQL work, we usually think in:
tables
rows
columns
For our application, the main relational structures will be:
products
inventory
orders
order_items
Each table represents a coherent category of persisted business facts.
A Table Is Not a Java Class Dump
A table should not simply contain every field from every related object.
For example, this would be a poor Order representation:
orders
order_id
customer_id
status
product_1_id
product_1_quantity
product_1_price_cents
product_2_id
product_2_quantity
product_2_price_cents
product_3_id
...
Why?
Because:
number of OrderItems is variable
The relational model gives us a better structure:
orders
order_items
connected through keys.
Rows Represent Persisted Records
Example Product rows:
products
id | name | price_cents | active
-------------------------------------+-----------+-------------+-------
59ba787c-6d83-4a21-9bae-19c3d0469bd5 | Keyboard | 10000 | true
364ec915-01ab-4cf0-b19f-327ed160f42b | Mouse | 5000 | true
73c68220-b7bf-4ac0-8499-38bdc20755ae | Monitor | 30000 | false
Each row represents one persisted Product state.
Columns Represent Stored Attributes
For Product:
id
name
price_cents
active
For Inventory:
product_id
available_quantity
For Order:
id
customer_id
status
created_at
For OrderItem:
order_id
product_id
quantity
unit_price_cents
The next lesson will define the exact PostgreSQL schema.
For now, focus on the relational concepts behind it.
Primary Keys
A relational table needs a way to uniquely identify each row.
That is normally the:
PRIMARY KEY
For Product:
products.id
is the natural primary key.
That guarantees:
Two Product rows cannot have the same ProductId.
Domain Identity and Relational Identity
This fits our Domain naturally.
In Java:
ProductId
identifies Product.
In PostgreSQL:
products.id
identifies the persisted Product row.
Our canonical mapping is:
ProductId
→ java.util.UUID
→ PostgreSQL UUID
The database supports an identity we already chose in the Domain.
It does not invent that identity for us.
Order Identity
Likewise:
OrderId
identifies Order in the Domain.
The relational representation will use:
orders.id
as its primary key.
Again:
OrderId
→ UUID
is application-defined.
We do not wait for Hibernate to give us a generated numeric ID.
Inventory Is Different
Our Domain does not have:
InventoryId
Inventory is identified by:
ProductId
because v1 has one Inventory state per Product.
Relationally, that means:
inventory.product_id
can itself identify the row.
We do not need:
inventory.id
merely because many tables happen to have an id column.
Do Not Add Surrogate IDs Automatically
A common habit is:
CREATE TABLE inventory (
id BIGINT PRIMARY KEY,
product_id UUID NOT NULL,
available_quantity INTEGER NOT NULL
);
But if:
ProductId
already uniquely identifies Inventory, then a second generated identity adds no useful business meaning.
Our canonical design is simpler:
inventory.product_id
→ primary key
→ foreign key to products.id
Schema design should follow:
business identity
relationships
query patterns
rather than boilerplate.
Foreign Keys
A foreign key expresses a relationship between rows in different tables.
For example:
inventory.product_id
references:
products.id
Conceptually:
Product
↑
│
Inventory
PostgreSQL can then enforce:
Inventory cannot reference a Product that does not exist.
Why Foreign Keys Matter
Without a foreign key, a bug could persist:
inventory
product_id =
9df2d34b-1554-4103-a85c-7f7ea4869240
even though no Product with that ID exists.
Now the database contains an orphaned relationship.
A foreign key prevents that structural inconsistency.
Order and OrderItem
Our Domain says:
Order
↓
many OrderItems
Relationally:
orders
↑
│ order_id
│
order_items
An Order might look like:
orders
id
------------------------------------
d2e7b44e-463f-4bbc-b16f-b1c80bc80442
with related OrderItems:
order_items
order_id | product_id | quantity | unit_price_cents
--------------------------------------+---------------------------------------+----------+-----------------
d2e7b44e-463f-4bbc-b16f-b1c80bc80442 | 59ba787c-6d83-4a21-9bae-19c3d0469bd5 | 2 | 10000
d2e7b44e-463f-4bbc-b16f-b1c80bc80442 | 364ec915-01ab-4cf0-b19f-327ed160f42b | 1 | 5000
One Order row can therefore relate to many OrderItem rows.
One-to-Many Relationship
This is a classic:
one-to-many
relationship.
One:
Order
has many:
OrderItems
The foreign key lives on the many side:
order_items.order_id
and references:
orders.id
Product and Inventory
Our Product/Inventory relationship is effectively:
one Product
→ zero or one Inventory row
Why zero or one rather than always exactly one?
Because we have already established:
missing Inventory
≠
Inventory with quantity 0
Relationally:
products.id
↑
│
inventory.product_id
Making inventory.product_id the primary key ensures:
at most one Inventory row
per Product
without requiring every Product to have one.
OrderItem and Product
Each OrderItem stores:
ProductId
So:
order_items.product_id
can reference:
products.id
This preserves the relationship to Product identity.
But OrderItem must still persist:
unit_price_cents
We never reconstruct historical price using current:
products.price_cents
Relationships Do Not Mean Joining Everything
A foreign key means:
a relationship exists
It does not mean every query must join every related table.
For Order history, we may query primarily:
orders
and return summary fields.
For Order detail, we need:
orders
+
order_items
Different application operations require different relational queries.
Joining Tables
SQL:
JOIN
lets us combine related rows.
Conceptually:
SELECT ...
FROM orders
JOIN order_items
ON order_items.order_id = orders.id
WHERE orders.id = :orderId;
This lets the persistence layer reconstruct:
Order
+
OrderItems
from relational data.
Product Browsing Also Needs a Join
Customer Product browsing means:
Product.active = true
AND
Inventory.availableQuantity > 0
Relationally, that combines:
products
inventory
Conceptually:
SELECT
p.id,
p.name,
p.price_cents
FROM products p
JOIN inventory i
ON i.product_id = p.id
WHERE p.active = true
AND i.available_quantity > 0
ORDER BY
p.name ASC,
p.id ASC;
This is a strong example of why relational thinking matters.
The public API sees:
GET /api/v1/products
while persistence can combine multiple tables efficiently.
Logical Boundaries Do Not Ban Joins
We modeled:
Product
and:
Inventory
as separate responsibilities.
That does not mean PostgreSQL should avoid joining them.
These are different concerns:
Domain ownership
and:
query execution
In one PostgreSQL database, joining related tables is normal.
Normalization
Relational design generally tries to store each changing fact in an appropriate place.
For example:
Product name
current Product price
Product active state
belong to Product.
available quantity
belongs to Inventory.
This avoids multiple unrelated copies of the same current state.
Historical Facts Are Different
Suppose current Product price is:
12000
while an old OrderItem records:
10000
Keeping both values is correct because they answer different questions:
What does the Product cost now?
What price did this Order use?
Historical snapshots are not necessarily bad duplication.
The meaning matters.
Duplication Is About Semantics
Duplicating current Product price into many unrelated current-state rows would be dangerous because those copies are supposed to stay synchronized.
But:
Product.priceCents
and:
OrderItem.unitPriceCents
are not two copies of the same changing fact.
They represent:
current commercial price
and:
historical purchase-time price
respectively.
Their divergence is correct.
Store Facts, Not Accidental Derivations
Our canonical Order total is:
sum(
quantity × unitPriceCents
)
across its OrderItems.
We have already decided:
Order total
→ derived
→ not authoritative persisted state
Therefore our v1 schema does not need:
orders.total_cents
as another mutable source of truth.
Persistence stores:
quantity
unit_price_cents
and the application derives:
totalCents
with exact arithmetic.
This avoids possible drift between:
orders.total_cents
and:
order_items
Think About Functional Relationships
A useful relational question is:
Given this key, what facts should it determine?
For Product:
ProductId
→ name
→ priceCents
→ active
For Inventory:
ProductId
→ availableQuantity
For Order:
OrderId
→ CustomerId
→ status
→ createdAt
For OrderItem:
OrderId + ProductId
→ quantity
→ unitPriceCents
This way of thinking helps identify:
primary keys
uniqueness rules
table boundaries
Duplicate Products in an Order
Our application rejects duplicate Product IDs in one Order.
For example, one Order cannot contain:
Product A × 1
Product A × 2
as two separate lines.
Relationally, we can reinforce this by requiring:
(order_id, product_id)
to be unique.
Our canonical schema goes one step further:
PRIMARY KEY (
order_id,
product_id
)
for order_items.
Why the Composite Key Fits
The pair:
order_id
+
product_id
naturally identifies one Product line within an Order.
That aligns with our business rule:
one Product line per Order
So we do not introduce:
OrderItemId
merely for ORM convenience.
Composite Keys
A key made from multiple columns is called a:
composite key
Our canonical example is:
(order_id, product_id)
for:
order_items
This simultaneously expresses:
row identity
and:
duplicate Product prevention
for one Order.
Application Rules and Database Integrity Work Together
CreateOrderUseCase already rejects duplicate Product IDs before any Inventory mutation.
Why also protect this in PostgreSQL?
Because the database constraint protects against:
buggy persistence code
unexpected write path
migration mistake
manual invalid write
So we deliberately have:
application rule
+
database constraint
Both reinforce the same persisted invariant from different boundaries.
Nullability
Relational columns can allow or reject:
NULL
The design question is:
Is absence a valid business state?
For our persisted v1 model:
Product.id
Product.name
Product.price_cents
Product.active
Inventory.product_id
Inventory.available_quantity
Order.id
Order.customer_id
Order.status
Order.created_at
OrderItem.order_id
OrderItem.product_id
OrderItem.quantity
OrderItem.unit_price_cents
are all required.
So these columns should be:
NOT NULL
where the primary-key definition does not already imply it.
Java null and SQL NULL
Java null and SQL NULL are related concepts but belong to different systems.
Our general rule is:
required business state
→ required in Domain/application
→ required in persisted schema
We should not make a database column nullable merely because doing so makes ORM mapping convenient.
Data Types Matter
Java and PostgreSQL need compatible representations.
Our canonical mappings include:
java.util.UUID
↔
UUID
String CustomerId
↔
TEXT
boolean
↔
BOOLEAN
long cents
↔
BIGINT
int quantity
↔
INTEGER
Instant
↔
TIMESTAMPTZ
These choices communicate business meaning and define important storage characteristics.
Money Uses Integer Minor Units
Earlier versions of the material used:
BigDecimal
with decimal database types.
Our canonical course design has since standardized money as:
Java
→ long cents
PostgreSQL
→ BIGINT
For example:
10000
=
100.00
So Product uses:
price_cents BIGINT
and OrderItem uses:
unit_price_cents BIGINT
with:
value >= 0
constraints.
We do not use:
REAL
DOUBLE PRECISION
for authoritative money.
Quantities Use Integers
Order quantity is:
positive whole number
Inventory quantity is:
non-negative whole number
They naturally map to:
INTEGER
rather than decimal types.
Storage type should reflect business meaning.
Product Active State Is Boolean
Product lifecycle currently has:
active
inactive
So:
BOOLEAN
is appropriate.
We do not need:
ACTIVE
INACTIVE
as a separate status vocabulary because Product currently has only this binary state.
OrderStatus Is Different
Order lifecycle has three states:
UNPAID
PAID
CANCELLED
This cannot be represented meaningfully by a boolean.
Our canonical relational design stores readable text:
status TEXT NOT NULL
with a constraint:
CHECK (
status IN (
'UNPAID',
'PAID',
'CANCELLED'
)
)
This keeps raw data understandable while constraining accepted values.
Avoid Magic Numeric Status Codes
A persisted value such as:
status = 2
requiring engineers to remember:
1 = UNPAID
2 = PAID
3 = CANCELLED
saves little and harms readability.
For our system, clear persisted values matter more than tiny storage optimization.
A production schema should be understandable when inspected directly.
Referential Integrity
Foreign keys protect relationships between persisted records.
Our canonical relationships are:
inventory.product_id
→ products.id
order_items.order_id
→ orders.id
order_items.product_id
→ products.id
These constraints make invalid relationships harder to persist.
CustomerId Has No Local Foreign Key
Order stores:
customer_id
from our external identity boundary.
There is no local:
customers
table in v1.
Therefore:
orders.customer_id
cannot and should not reference a local Customer table that does not exist.
This is correct.
Relational integrity can reference only state our database actually owns.
External Identity Still Requires Durable Representation
Even without a foreign key:
customer_id
must be:
NOT NULL
and capable of storing the stable external identifier.
Our canonical type is:
TEXT
The authenticated identity boundary determines that CustomerId is valid.
PostgreSQL stores it durably.
Foreign Keys Do Not Enforce Workflow
Suppose an Order is:
PAID
A foreign key cannot decide whether:
order.cancel();
is allowed.
That is Domain behaviour.
Relational constraints are best at:
structure
relationships
simple persisted invariants
not arbitrary workflow orchestration.
CHECK Constraints
PostgreSQL can reinforce simple row-level invariants.
For example:
CHECK (
price_cents >= 0
)
CHECK (
available_quantity >= 0
)
CHECK (
quantity > 0
)
CHECK (
unit_price_cents >= 0
)
These constraints complement:
request validation
Domain validation
UseCase rules
Do Not Put Every Business Rule in SQL
Could PostgreSQL somehow encode:
PAID Order cannot be cancelled
through increasingly complicated mechanisms?
Possibly.
But that would move lifecycle behaviour away from:
Order.cancel()
where it is easier to understand and test.
Use database constraints for clear persisted integrity rules.
Do not turn PostgreSQL into the whole business layer.
Unique Constraints
Uniqueness expresses facts such as:
one Inventory row per Product
Our design accomplishes this through:
inventory.product_id
→ PRIMARY KEY
Likewise:
one Product line per Order
is enforced by:
PRIMARY KEY (
order_id,
product_id
)
on order_items.
Primary Key vs Unique Constraint
Both can enforce uniqueness.
But:
PRIMARY KEY
also defines the table's principal row identity.
A table has one primary key and may have additional unique constraints.
For Inventory:
product_id
is naturally the row identity.
For OrderItem:
(order_id, product_id)
is naturally the row identity.
Relational Design Is About Trade-Offs
Good schema design considers:
business identity
relationships
constraints
query patterns
historical requirements
concurrency
mapping complexity
future change cost
There is rarely one universally perfect schema.
We choose the simplest design that preserves our current requirements correctly.
Avoid Over-Normalization
We do not need separate tables such as:
product_names
product_prices
product_active_states
merely because those facts could theoretically be decomposed further.
That would create unnecessary joins and complexity.
Normalization is a tool.
It is not a contest to maximize the number of tables.
Avoid Under-Normalization
The opposite extreme is one giant table containing:
Order
OrderItems
Product current state
Inventory
with repeated data.
That produces:
duplication
awkward variable-length structures
update anomalies
poor queryability
Good relational design finds a practical middle ground.
Update Anomalies
Imagine current Product name is copied into thousands of unrelated current-state records.
An Admin renames Product.
Now every copy must change consistently.
If one is missed, the database contains contradictory versions of the same current fact.
That is an:
update anomaly
Keeping current Product information in the Product row avoids it.
Historical Snapshots Are an Exception
Historical data has different semantics.
If OrderItem stores:
unit_price_cents = 10000
and Product later changes to:
price_cents = 12000
the OrderItem should not update.
That is correct.
A useful principle is:
Duplication is problematic when multiple values are intended to represent the same changing fact.
Historical purchase-time price is a different fact.
Delete Behaviour Matters
Foreign keys can define what happens if a referenced row is physically deleted.
Common choices include:
RESTRICT
CASCADE
SET NULL
But deletion semantics must match the business lifecycle.
Our Product lifecycle says:
deactivate
not:
delete
and Order cancellation says:
change status
not:
erase history
So ordinary v1 workflows do not require physical Product or Order deletion.
Be Careful With Cascade Delete
Imagine Product deletion automatically cascades into:
Inventory
OrderItems
Deleting historical OrderItems would destroy Order history.
That would be unacceptable.
We therefore do not choose cascade rules simply because an ORM makes them convenient.
Persistence lifecycle must match business lifecycle.
Orders Are Not Deleted Either
Our Customer operation is:
cancel Order
Cancellation means:
UNPAID
→
CANCELLED
The Order remains part of history.
There is no Customer:
DELETE /api/v1/orders/{orderId}
operation in v1.
Relational lifecycle should preserve that history.
Rows Are Shared State
Multiple application requests may modify the same rows concurrently.
Suppose:
available_quantity = 1
and two Customers simultaneously request the final unit.
Both requests operate on shared durable state.
That means relational thinking must include:
transactions
row locking
atomic updates
isolation
not only tables and columns.
Read-Then-Write Is Not Automatically Safe
A naive flow is:
SELECT available_quantity
→ 1
check 1 >= 1
UPDATE available_quantity = 0
Two concurrent requests can both read:
1
before either commits.
Without a concurrency-safe strategy, both may believe they succeeded.
Our Inventory Strategy Is Already Decided
Earlier course design established ADR-005:
Use Atomic Conditional Updates for Inventory Consumption.
So Order creation uses:
UPDATE inventory
SET available_quantity =
available_quantity - :quantity
WHERE product_id = :productId
AND available_quantity >= :quantity;
Then:
affected rows = 1
→ consumption succeeded
affected rows = 0
→ insufficient Inventory
This avoids a race-prone application-level:
load
check
decrease
save
workflow.
Constraints and Concurrency Solve Different Problems
This constraint:
CHECK (
available_quantity >= 0
)
protects persisted validity.
The atomic conditional update determines which competing request is allowed to consume available stock.
So:
constraint
and:
concurrency strategy
are related but not interchangeable.
Think in Queries, Not Object Traversal
Java encourages expressions such as:
order.items();
A relational database operates through queries.
For Customer Order history, we need something conceptually like:
SELECT ...
FROM orders
WHERE customer_id = :customerId
ORDER BY
created_at DESC,
id DESC
LIMIT :size
OFFSET :offset;
The database should perform:
ownership filtering
ordering
pagination
before the results reach Java.
Do Not Load Everything Into Java
Bad:
load all Orders
↓
filter CustomerId in Java
↓
sort in Java
↓
take first 20
PostgreSQL is designed to perform:
filtering
sorting
joining
aggregation
pagination
close to the data.
Use it.
Pagination Must Reach PostgreSQL
For:
GET /api/v1/orders?page=0&size=20
the correct direction is:
PostgreSQL
↓
customer scope
↓
ORDER BY created_at DESC, id DESC
↓
LIMIT / OFFSET
↓
bounded result
not:
PostgreSQL
↓
all Orders
↓
Java filters and slices
Do Not Move the Whole Application Into SQL
There is an important balance.
PostgreSQL should perform:
filtering
joins
aggregation where useful
constraints
atomic persistence
concurrency-safe updates
The application still owns:
Create Order workflow
Order lifecycle
Customer ownership semantics
Payment orchestration
business decisions
Using PostgreSQL effectively does not mean turning SQL into the entire backend.
Reads Can Be More Relational
Mutation workflows often need Domain objects because business behaviour must execute.
Read workflows can sometimes use direct projections.
For example, Order history may need only:
OrderId
status
createdAt
totalCents
rather than every OrderItem.
A relational read query may therefore return an efficient summary projection.
This is pragmatic database usage.
No CQRS Ceremony Required
Using:
rich Domain objects for writes
and:
efficient relational projections for reads
does not require:
Event Sourcing
separate read database
separate write database
CQRS framework
We are simply choosing an appropriate query shape for each operation.
Constraints Are Documentation
A schema containing:
PRIMARY KEY
FOREIGN KEY
NOT NULL
CHECK
UNIQUE
communicates important assumptions.
For example:
available_quantity INTEGER NOT NULL
CHECK (
available_quantity >= 0
)
tells another engineer exactly what persisted Inventory state is considered valid.
A good schema communicates intent.
Naming Matters
Prefer names that express business meaning.
Good:
available_quantity
customer_id
unit_price_cents
created_at
Poor:
qty1
owner_ref_value
price2
date_value
Database naming should be boring and understandable.
Java Naming vs SQL Naming
Java commonly uses:
availableQuantity
PostgreSQL commonly uses:
available_quantity
That is fine.
Persistence mapping translates between conventions.
We do not need camelCase database columns merely because Java uses camelCase.
Table Names
Our canonical table names are:
products
inventory
orders
order_items
They are simple and readable.
There is no need for prefixes such as:
tarkev_products
because the database already belongs to this application context.
Unnecessary prefixes add noise.
Schema Is an Internal Contract
The PostgreSQL schema is not a public HTTP contract.
But it is still a contract between:
application versions
Flyway migrations
production data
operations
Once real data exists, schema changes require care.
This is why our schema will evolve through explicit Flyway migrations.
Relational View of Our Current Domain
Before writing the migration, our current shape is conceptually:
products
--------
id
name
price_cents
active
inventory
---------
product_id
available_quantity
orders
------
id
customer_id
status
created_at
order_items
-----------
order_id
product_id
quantity
unit_price_cents
This is not yet the migration file.
It is the relational shape we can reason about before adding all concrete PostgreSQL constraints.
Relationship Diagram
Product and Inventory:
products
│
│ 1
│
│ 0..1
inventory
Order and OrderItems:
orders
│
│ 1
│
│ many
order_items
Product and historical OrderItems:
products
│
│ 1
│
│ many
order_items
Order additionally contains:
customer_id
as an external identity reference rather than a foreign key to a local Customer table.
Why This Shape Fits Our Workflows
Customer Product browsing:
products
+
inventory
Create Order:
products
+
inventory
+
orders
+
order_items
Order history:
orders
with additional OrderItems for detail when needed.
Cancel Order:
orders
+
inventory
Pay Order:
orders
+
external PaymentService
The relational model supports our confirmed behaviour without inventing extra business subsystems.
Relational Thinking Checklist
When designing a table, ask:
What business fact does one row represent?
What uniquely identifies that row?
Which columns are required?
Which simple constraints protect valid values?
Which other rows does this row reference?
Should those relationships use foreign keys?
Can duplicates represent invalid business state?
What queries need this table?
What data changes concurrently?
Which values represent current state?
Which values represent historical state?
These questions produce stronger schemas than:
Which JPA annotations do I remember?
Common Mistakes
Every Table Gets id BIGINT
Use the real identity where appropriate.
Inventory naturally uses ProductId.
OrderItem naturally uses (order_id, product_id).
Every Java Object Becomes a Table
DTOs, Handlers, UseCases, Commands, and many Value Objects do not independently require tables.
Persist durable facts.
One Huge Table
Variable relationships such as OrderItems belong naturally in related rows.
No Foreign Keys
Application checks alone do not protect persisted referential integrity from every possible write.
Cascade Delete Everywhere
Deletion behaviour must follow business lifecycle and preserve Order history.
Recalculate Historical Price From Product
OrderItem must persist historical:
unit_price_cents
independently of current Product price.
Duplicate Current Facts Everywhere
Mutable Product state should have a clear authoritative location.
Magic Numeric Status Values
Readable persisted lifecycle values are easier to operate and evolve.
Pagination in Java
Filtering, ordering, joining, and pagination should happen in PostgreSQL whenever appropriate.
SQL Becomes the Whole Business Layer
Use PostgreSQL for relational integrity and efficient data operations while keeping workflows and Domain behaviour explicit in Java.
Responsibility Map
Domain Thinking
Asks:
What does this business concept mean?
What behaviour is valid?
What invariants must hold?
Relational Thinking
Asks:
What facts must be stored?
How are they related?
What identifies each row?
What constraints protect them?
How will they be queried?
What can change concurrently?
Persistence Mapping
Connects:
Domain/application representation
↔
relational representation
PostgreSQL
Protects and provides:
durable relational state
primary keys
foreign keys
constraints
joins
queries
transactional writes
concurrency-safe persistence operations
Engineering Principle
The core principle is:
Objects model identity and behaviour in the application; relational tables model durable facts and relationships in the database. Good persistence design understands both without pretending they are the same thing.
Another:
Primary keys, foreign keys, uniqueness, nullability, and CHECK constraints are not database decoration—they encode important assumptions about valid persisted state.
And:
Use PostgreSQL for what it is good at: filtering, joining, constraining, ordering, paginating, and atomically storing shared data while keeping business workflows explicit in the application.
Summary
In this lesson, we established that:
- Java object models and relational models represent the same business state differently.
- Java emphasizes objects, identity, and behaviour.
- PostgreSQL emphasizes tables, rows, columns, keys, constraints, and relationships.
- Persistence mapping translates between those representations.
- A database table should model durable facts rather than simply mirror a Java class.
- Product rows store UUID identity, name, current
price_cents, and active state. - Inventory rows store ProductId and
available_quantity. - Order rows store UUID OrderId, external CustomerId, status, and
created_at. - OrderItem rows store OrderId, ProductId, quantity, and historical
unit_price_cents. - ProductId and OrderId use PostgreSQL
UUID. - CustomerId remains an external String and maps naturally to textual storage.
- Monetary values use
BIGINTinteger cents. - Quantities use integer storage.
createdAtmaps toTIMESTAMPTZ.- Product active state maps naturally to
BOOLEAN. - OrderStatus uses readable
UNPAID,PAID, andCANCELLEDvalues. - Product identity maps naturally to
products.id. - Order identity maps naturally to
orders.id. - Inventory does not need a separate InventoryId.
inventory.product_idis both its primary key and Product foreign key.- Order-to-OrderItem is a one-to-many relationship.
- Product-to-Inventory allows at most one Inventory row per Product.
- Missing Inventory remains distinct from zero Inventory.
- OrderItem references both its parent Order and Product.
- Historical OrderItem price is persisted independently of current Product price.
Order.totalCentsremains derived and is not stored as an independent authoritative column.(order_id, product_id)is the canonical composite primary key for OrderItems.- That composite key reinforces the rule that a Product appears at most once per Order.
- Foreign keys protect referential integrity.
- CustomerId has no local foreign key because Customer lifecycle is externally owned.
- Required business state should generally use
NOT NULL. - CHECK constraints reinforce simple persisted invariants.
- Price and Inventory values must be non-negative.
- OrderItem quantity must be positive.
- Database constraints complement Domain/application validation rather than replacing it.
- Domain lifecycle rules such as cancellation remain in Java rather than being hidden in SQL.
- Normalization helps prevent accidental duplication of mutable current facts.
- Historical snapshots can intentionally duplicate values when they represent different facts.
- Physical deletion and cascade behaviour must follow business lifecycle.
- Product is deactivated rather than deleted.
- Order is cancelled rather than deleted.
- Order history must not disappear through cascade rules.
- Rows are shared mutable state across concurrent requests.
- Read-check-write Inventory consumption is unsafe under concurrency.
- ADR-005 already established atomic conditional Inventory consumption.
- Database constraints and concurrency control solve different problems.
- PostgreSQL should perform filtering, ordering, joins, and pagination close to the data.
- Customer Order history must be scoped and paginated in PostgreSQL.
- Efficient read projections do not require CQRS.
- Schema constraints also serve as documentation.
- SQL names use clear snake_case conventions.
- Our canonical table names are
products,inventory,orders, andorder_items. - The schema is an internal contract that must evolve carefully through Flyway.
- Relational design should be driven by business identity, history, constraints, concurrency, and real query patterns rather than ORM convenience.
Next lesson:
Designing the Initial Schema
There, we will turn these relational concepts into the first concrete PostgreSQL schema for products, inventory, orders, and order_items, including the exact PostgreSQL types, primary keys, foreign keys, NOT NULL rules, CHECK constraints, composite OrderItem key, and the Order-history index already justified by our application requirements.