Persistence with PostgreSQL
Relational Database Thinking
আপনি একটি free preview lesson দেখছেন।
আগের lesson-এ আমরা দেখেছি কেন আমাদের application-এর durable state PostgreSQL-এ persist করতে হবে।
এখন next step হলো mindset shift।
Java-তে আমরা objects নিয়ে ভাবি:
Product
Inventory
Order
OrderItem
এবং relationships দেখি object graph হিসেবে:
Order
↓
List<OrderItem>
কিন্তু PostgreSQL একই data-কে অন্যভাবে দেখে:
tables
rows
columns
keys
constraints
relations
একজন backend engineer-এর জন্য এই দুই representation-ই বুঝতে হবে।
কারণ persistence layer effectively translate করে:
domain/application model
↔
relational model
এই lesson-এর goal:
Java object thinking থেকে relational thinking-এ shift করা, যাতে আমরা tables, rows, keys, relationships, constraints, এবং joins দিয়ে আমাদের domain data correctly model করতে পারি।
Objects and Rows Are Different Models
ধরুন Java-তে:
Product product = new Product(
productId,
"Keyboard",
new BigDecimal("100.00"),
true
);
এটি একটি object।
Relational database-এ একই concept may look like a row:
products
id | name | price | active
-------+----------+--------+-------
P-100 | Keyboard | 100.00 | true
The values look familiar, but the model is different।
Java Thinks in Object Identity
Java object may have:
ProductId
plus:
fields
methods
behaviour
For example:
product.changePrice(...);
product.deactivate();
PostgreSQL row has no Java method।
It stores state:
id
name
price
active
Application loads the state and reconstructs the behaviour-rich object।
Database Does Not Execute product.deactivate()
When we call:
product.deactivate();
domain object changes:
active = true
to:
active = false
Persistence later translates that state change into something conceptually like:
UPDATE products
SET active = false
WHERE id = ...;
The domain operation and SQL update are related, but they are not the same responsibility।
Think in Relations
The "relational" in relational database refers to relations—conceptually sets of rows with defined attributes।
In practical PostgreSQL usage, we usually interact with:
tables
For our application, likely tables include:
products
inventory
orders
order_items
Each table represents a coherent persisted concept।
A Table Is Not a Java Class Dump
A table should not simply contain every field from every related Java object।
For example, we should not create one huge table:
orders
order_id
customer_id
status
product_1_id
product_1_quantity
product_1_price
product_2_id
product_2_quantity
product_2_price
...
Why?
Because the number of OrderItems is variable।
Relational modeling gives us a better way:
orders
order_items
connected through a key।
Rows Represent Records
Example Product rows:
products
id | name | price | active
-------+-----------+--------+-------
P-100 | Keyboard | 100.00 | true
P-101 | Mouse | 50.00 | true
P-102 | Monitor | 300.00 | false
Each row represents one persisted Product state।
Columns Describe Attributes
Columns represent stored attributes।
For Product:
id
name
price
active
For Inventory:
product_id
available_quantity
For Order:
id
customer_id
status
Exact schema comes in the next lesson।
For now focus on the relational concepts।
Primary Key
A relational table needs a way to uniquely identify rows।
This is usually the:
primary key
For Product:
products.id
could be the primary key।
This means:
two Product rows cannot have the same Product ID
Domain Identity and Primary Key
This fits our domain nicely:
ProductId
identifies Product in the domain।
The database primary key identifies the persisted Product row।
These can map naturally to each other।
But remember:
We choose domain identity intentionally first; the database key supports that identity.
Order Primary Key
Similarly:
OrderId
will identify an Order।
Relational table:
orders
will need a unique key representing that identity।
Inventory Is Different
Our domain does not need a separate:
InventoryId
Inventory is identified by:
ProductId
because there is one Inventory state per Product।
Relational thinking can represent that naturally:
inventory.product_id
can itself uniquely identify the Inventory row।
We do not need to invent:
inventory.id
just because tables "usually have an id column."
Do Not Add Surrogate IDs Automatically
A common habit:
CREATE TABLE inventory (
id BIGINT PRIMARY KEY,
product_id ...,
...
);
But if business identity is already:
ProductId
and there is exactly one Inventory row per Product, the extra ID may add no value।
Schema design should follow relationships and access patterns, not boilerplate।
Foreign Keys
A:
foreign key
expresses a relationship between rows in different tables।
For example:
inventory.product_id
references:
products.id
Conceptually:
Product P-100
↑
│
Inventory P-100
The database can enforce:
Inventory cannot reference a Product row that does not exist.
Why Foreign Keys Matter
Without a foreign key, a bug could create:
inventory
product_id = P-999
while:
products
contains no P-999।
Now persisted data contains an orphan relationship।
Foreign key constraints help prevent this structural inconsistency।
Order and OrderItem Relationship
Our domain:
Order
↓
many OrderItems
Relationally:
orders
↑
│ order_id
│
order_items
Each OrderItem row references its parent Order।
Conceptually:
orders
id
------
O-1001
order_items
order_id | product_id | quantity | unit_price
---------+------------+----------+-----------
O-1001 | P-100 | 2 | 100.00
O-1001 | P-101 | 1 | 50.00
One Order row can 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 usually lives on the "many" side:
order_items.order_id
Product and Inventory Relationship
Our Product/Inventory relationship is closer to:
one Product
→ one Inventory state
Conceptually:
products.id
↑
│
inventory.product_id
A uniqueness/primary-key constraint on inventory.product_id can ensure one Inventory row per Product।
OrderItem and Product
Each OrderItem stores:
ProductId
So relationally:
order_items.product_id
can reference:
products.id
This preserves the relationship to the Product identity।
But remember:
unit_price
must still be stored in OrderItem।
We do not derive historical price by joining current Product price।
Relationships Do Not Mean We Always Join Everything
Foreign key means:
relationship exists
It does not mean every query should always join every related table।
For Order history, we may query:
orders
and perhaps only summary fields।
For Order detail, we need:
orders + order_items
Different use cases require different queries।
Joining Tables
SQL:
JOIN
allows us to combine related rows।
Conceptually:
SELECT ...
FROM orders
JOIN order_items
ON order_items.order_id = orders.id
WHERE orders.id = ...;
This lets us reconstruct:
Order
+
OrderItems
from normalized relational data।
Product Browse May Need a Join Too
Customer Product browsing means:
Product active
AND
Inventory available_quantity > 0
Relationally, that likely involves:
products
inventory
Conceptually:
SELECT ...
FROM products
JOIN inventory
ON inventory.product_id = products.id
WHERE products.active = true
AND inventory.available_quantity > 0;
This is a strong example of why relational thinking matters।
The public API sees:
GET /products
but persistence may combine multiple tables efficiently।
Logical Capability Boundary vs Relational Join
We modeled:
Product
and:
Inventory
as separate responsibilities।
That does not mean the database must avoid joining them।
Logical ownership and efficient query execution are different concerns।
In one PostgreSQL database, joining related tables is normal।
Normalization
Relational design often encourages storing each fact in the appropriate place rather than unnecessarily repeating mutable data।
For example:
Product name
current Product price
active state
belong to Product।
Inventory quantity belongs to Inventory।
This reduces inconsistent copies of the same current state।
But Historical Facts Are Different
Suppose current Product price:
120
OrderItem purchase-time price:
100
Storing both is not bad normalization।
They represent different facts:
What does Product cost now?
What price did this Order use?
Relational modeling should preserve meaning, not pursue theoretical deduplication blindly।
Store Facts, Not Accidental Derivations
Our Order total is currently defined as:
sum(
OrderItem.unitPrice × quantity
)
We previously chose total as a derived value rather than an independent mutable source of truth।
So should we store an orders.total column?
Maybe, but we should ask why।
If stored, we must ensure:
Order total
cannot drift away from:
OrderItems
Derived Data Needs Deliberation
Possible approaches:
derive total on read
store total and enforce consistency in workflow
use database-generated mechanism
Each has trade-offs।
Our accepted domain model says:
Order total is derived from OrderItems and must not become an independent mutable truth.
So persistence design should preserve that invariant।
Exact storage choice will be made during schema design rather than assumed now।
Think About Functional Relationships
A useful relational question is:
Given this key, what fact should be uniquely determined?
For Inventory:
ProductId
→ available quantity
For Product:
ProductId
→ name, current price, active state
For Order:
OrderId
→ CustomerId, status
For each OrderItem row:
parent Order
+
Product reference
→ quantity, unit price
This kind of thinking helps identify keys and uniqueness rules।
Duplicate Products in an Order
Our application contract rejects duplicate Product IDs within one Order।
Relationally, we can reinforce that structural rule।
Conceptually, this combination should be unique:
order_id + product_id
That means one Order cannot have:
O-1001 + P-100
twice।
A database uniqueness constraint may reinforce the same rule later।
Why This Is Useful
Application already checks duplicates before persistence।
But database constraint protects against:
buggy code path
race
manual/accidental invalid write
Again:
application rule
+
database integrity
work together।
Nullability
Relational columns can often allow or reject:
NULL
We should ask:
Is absence a legitimate state?
For example:
Order.customer_id
should not be absent for our v1 Orders।
Order.status
should not be absent।
Likewise:
OrderItem.quantity
should not be null।
So database schema should eventually use:
NOT NULL
where business state requires values।
Java null vs SQL NULL
They are related concepts but not identical implementation mechanisms।
In our design:
required business state
should generally be represented as required both in Java/domain and in persisted schema।
Don't allow database nullability simply because JPA defaults make it convenient।
Data Types Matter
Java types and PostgreSQL types need compatible mappings।
Examples:
String
↔ textual SQL type
boolean
↔ BOOLEAN
BigDecimal
↔ NUMERIC/DECIMAL-like type
integer quantity
↔ integer SQL type
Exact PostgreSQL type choices matter because they define:
precision
range
storage behaviour
We'll choose them intentionally in schema design।
Money and Decimal Types
For prices:
BigDecimal
should not map to binary floating-point types like:
REAL
DOUBLE PRECISION
for authoritative monetary values।
A decimal/numeric PostgreSQL type is more appropriate because it can represent decimal values exactly according to chosen precision/scale constraints।
Exact precision will be chosen deliberately in the next lessons।
Quantities Are Integers
Order quantity:
positive whole number
Inventory quantity:
non-negative whole number
These naturally map to integer types rather than decimal numeric types।
Again, storage type should reflect actual business meaning।
Booleans
Product active state:
true / false
maps naturally to:
BOOLEAN
We do not need:
0 / 1 strings
"ACTIVE" / "INACTIVE"
unless lifecycle semantics require more than two states।
Current Product lifecycle does not।
OrderStatus Is Different
Order lifecycle has:
UNPAID
PAID
CANCELLED
This is not boolean।
We need a persisted representation capable of preserving these states।
Possible relational representations include:
text/varchar
PostgreSQL enum
small numeric code
Each has trade-offs।
We will choose deliberately rather than letting JPA default accidentally decide։
Avoid Magic Numeric State Codes
Schema:
status = 1
with:
1 = UNPAID
2 = PAID
3 = CANCELLED
can save a small amount of storage but makes raw data less readable and couples meaning to undocumented numeric mappings։
For our current application, readability and maintainability matter more than micro-optimization।
Relational Schema Should Be Understandable
An engineer inspecting PostgreSQL should ideally understand:
what data means
how tables relate
what constraints protect
without decoding a maze of magic values।
A production schema is an engineering artifact too।
Referential Integrity
Foreign keys protect:
references between rows
Examples:
inventory.product_id
→ products.id
order_items.order_id
→ orders.id
Potentially:
order_items.product_id
→ products.id
These constraints make invalid relationships harder to persist।
But CustomerId Has No Local Foreign Key
Order stores:
CustomerId
from an external identity system।
There is no local:
customers
table in v1।
Therefore:
orders.customer_id
cannot have a foreign key to a local Customer table that doesn't exist।
This is correct।
Relational integrity can only reference state our database owns।
External Identity Still Needs a Stable Representation
Even without a foreign key, we still need:
customer_id NOT NULL
and a type capable of storing the external stable identifier।
The application must trust the authenticated identity boundary for validity of that external reference।
Foreign Keys Do Not Validate Business Workflow
Suppose Order is:
PAID
Foreign key constraints cannot tell us whether:
cancel()
is allowed।
That's domain behaviour।
Relational constraints excel at:
structure
relationships
basic invariant reinforcement
not arbitrary business workflow orchestration।
CHECK Constraints
PostgreSQL can enforce conditions like:
available_quantity >= 0
or:
quantity > 0
using:
CHECK constraints
This is a good fit for simple row-level structural invariants।
Example Conceptually
CHECK (available_quantity >= 0)
reinforces our Inventory invariant।
Similarly:
CHECK (quantity > 0)
reinforces OrderItem quantity validity।
Don't Put Every Business Rule Into CHECK Constraints
Could we encode:
PAID Order cannot be cancelled
inside database constraints?
Potentially with increasingly complex mechanisms।
But that would push workflow logic away from the domain/application where it is easier to understand and test।
Use DB constraints for clear persisted integrity rules, not as a replacement for the business model।
Unique Constraints
Uniqueness can express important facts।
Example:
one Inventory row per Product
can be ensured by making:
inventory.product_id
unique or primary।
Likewise:
one Product per Order
may be reinforced with a unique constraint on:
(order_id, product_id)
in order_items।
Primary Key vs Unique Constraint
Both enforce uniqueness, but primary key also represents the table's main row identity।
A table can have:
one primary key
additional unique constraints
For Inventory, ProductId may naturally be both its identity and primary key։
For OrderItem, exact row identity needs careful thought।
Does OrderItem Need Its Own ID?
Our domain model does not require:
OrderItemId
OrderItem belongs entirely to Order।
Relationally, we might identify a row using:
order_id + product_id
since duplicate Products are disallowed।
This is a natural composite key candidate।
Composite Keys
A key made from multiple columns is called a:
composite key
For example:
(order_id, product_id)
could uniquely identify an OrderItem in our v1 design։
This aligns with the business rule:
one Product line per Order
Surrogate Key for OrderItem?
Alternatively, persistence could add:
order_item_id
even though domain doesn't expose it।
That can simplify some ORM mappings, but it also introduces persistence-only identity।
Neither option should be chosen blindly।
We'll consider mapping trade-offs when designing schema/JPA relationships।
Relational Design Is About Trade-offs
There is rarely one universally correct schema shape।
We evaluate:
business identity
constraints
query patterns
ORM complexity
historical requirements
future change cost
Then choose the simplest design that protects our current requirements well।
Avoid Over-Normalization
Suppose someone creates:
product_names
product_prices
product_statuses
as three separate tables just because every concept can theoretically be normalized further।
This creates unnecessary joins and complexity।
Normalization is a tool, not a goal in isolation։
Our schema should remain coherent and practical।
Avoid Under-Normalization
The opposite problem is one giant table:
everything
containing repeated Order and Product data։
That creates:
duplication
update anomalies
awkward variable-length data
Good relational modeling finds a practical middle ground।
Update Anomaly
Suppose current Product name is repeated in 10,000 unrelated current-state rows։
Admin renames Product։
Now every copy has to update consistently։
If one copy is missed, database contains conflicting current values।
That's an:
update anomaly
Storing current Product information in one Product row avoids this।
Historical Snapshot Exception
But if an Order intentionally stores a historical snapshot field, then that copy should not update when Product changes।
For unit price:
OrderItem.unit_price
remaining unchanged is correct।
Again:
Duplication is problematic only when duplicate values are supposed to represent the same changing fact.
Delete Behaviour Matters
Foreign keys can define what happens when referenced rows are deleted।
Possible behaviours include:
restrict deletion
cascade deletion
set null
Our Product requirement already helps us here:
Product is deactivated, not physically deleted
Therefore we don't need cascading Product deletion semantics for normal business operations।
This makes historical integrity simpler।
Be Careful With ON DELETE CASCADE
Suppose:
Product deleted
and cascade automatically deletes:
Inventory
OrderItems
That would be disastrous for Order history।
Never choose cascade rules based only on ORM convenience։
Deletion semantics must match business lifecycle।
Order and OrderItem Deletion
Do we currently support:
delete Order
?
No।
Cancellation changes state; it does not erase history।
Therefore physical deletion of Orders is not a customer business operation in v1।
Again, schema lifecycle should reflect domain lifecycle।
Rows Are Shared State
Remember multiple application requests may operate on the same persisted rows।
For Inventory:
available_quantity = 1
two concurrent customers might both request that last unit।
Relational thinking must eventually include:
transaction isolation
locking
atomic updates
Domain checks alone do not solve concurrent writes।
We'll address this during workflows and persistence strategy।
"Read Then Write" Is Not Automatically Safe
Naive flow:
SELECT quantity
→ 1
check 1 >= 1
UPDATE quantity = 0
Two requests may both read:
1
before either update commits।
Without concurrency-safe persistence, both might succeed logically and oversell।
This is why relational database thinking must include concurrency, not just tables and columns।
We Are Not Choosing the Strategy Yet
Possible techniques later include:
locking
atomic conditional update
optimistic concurrency
The exact strategy is intentionally deferred until we implement Inventory persistence/workflow।
But schema and Repository design must support whichever concurrency-safe strategy we choose।
Constraints and Concurrency Work Together
A database constraint like:
available_quantity >= 0
helps prevent a negative persisted quantity।
But a concurrency strategy still needs to determine which competing Order succeeds։
A constraint alone may reject one transaction late, which can still be useful, but application behaviour should map that outcome meaningfully।
Think in Queries, Not Object Traversal
Java encourages:
order.items()
Relational database uses queries।
For example, to retrieve customer Order history:
find Orders
WHERE customer_id = ?
ORDER BY ...
LIMIT ...
OFFSET ...
Then Order detail may retrieve its items separately or through an appropriate join/query strategy।
Don't Load the Whole Database and Navigate in Java
Bad relational thinking:
load all Orders
filter CustomerId in Java
sort in Java
take first 20
Database is built to perform:
filtering
sorting
joining
pagination
efficiently close to the data।
Use it।
Push Query Work to PostgreSQL
For:
GET /orders?page=0&size=20
correct direction:
PostgreSQL
→ customer scope
→ ordering
→ page bound
→ return requested rows
not:
PostgreSQL
→ everything
→ Java filters/slices
But Don't Push Business Workflows Into SQL
There is an important balance।
Database should perform:
filtering
joins
aggregations where useful
constraints
atomic persistence
Application should still own:
Create Order workflow
Order lifecycle
authorization decisions
Payment orchestration
Using PostgreSQL effectively does not mean moving the whole backend into SQL।
Read Queries Can Be More Relational
Mutation workflows often reconstruct domain objects because we need domain behaviour।
Read operations may sometimes use direct projections।
For example:
Order history summary
could query only:
Order ID
status
total/summary data
created time
without reconstructing every detail if the endpoint doesn't need it।
This is pragmatic relational thinking।
No CQRS Ceremony Required
Having:
rich domain writes
and:
efficient relational read projection
does not require:
separate databases
event sourcing
CQRS framework
It simply means we use the database intelligently for different access patterns।
Constraints Are Documentation Too
A schema containing:
NOT NULL
FOREIGN KEY
UNIQUE
CHECK
documents important data assumptions।
For example:
inventory.available_quantity NOT NULL
CHECK >= 0
tells an engineer something concrete about valid persisted state।
A good schema communicates intent।
Naming Matters
Use table/column names that express domain meaning։
Prefer:
available_quantity
over:
qty1
Prefer:
customer_id
over:
owner_ref_value
unless a more technical name reflects a real concept।
Database naming should be boring and understandable।
SQL Naming vs Java Naming
Java commonly uses:
availableQuantity
PostgreSQL schemas commonly use:
available_quantity
That's fine।
Persistence mapping translates between conventions।
We do not need database column names to mimic Java camelCase։
Table Names
Our likely naming:
products
inventory
orders
order_items
This is simple and readable।
We previously decided:
no liveklass_ table prefix
because the database already belongs to the application context; unnecessary prefixes add noise।
Avoid Reserved/Awkward Names
Database naming should also consider SQL readability and reserved words։
For our current table names, nothing unusual is required։
Keep names explicit and stable once migrations are deployed।
Renaming production columns/tables has migration cost।
Schema Is a Public Contract Internally
Not public to HTTP clients, but schema is a contract between:
application versions
migrations
operations
data
Once production data exists, schema changes deserve care।
This is why Flyway migrations will be explicit and versioned։
A Relational View of Our Current Domain
Conceptually:
products
--------
id
name
price
active
inventory
---------
product_id
available_quantity
orders
------
id
customer_id
status
order_items
-----------
order_id
product_id
quantity
unit_price
This is not yet our final migration।
It is the relational shape we can reason about before adding concrete types, constraints, and mapping details।
Relationship Diagram
Conceptually:
products
│
│ 1
│
│ 1
inventory
and:
orders
│
│ 1
│
│ many
order_items
and:
products
│
│ 1
│
│ many
order_items
Order also contains:
customer_id
as an external identity reference, not a local foreign key।
Why This Shape Is Useful
It supports our confirmed workflows:
browse orderable Products
→ products + inventory
create Order
→ products + inventory + orders + order_items
Order history
→ orders (+ items for detail)
cancel Order
→ orders + inventory
pay Order
→ orders + external PaymentService
Schema supports application 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 values have simple constraints?
What other rows does it reference?
Should those relationships be enforced by foreign keys?
Can duplicate rows represent invalid business state?
What queries need this data?
What state changes concurrently?
Is any value historical rather than current?
These questions produce better schemas than:
What JPA annotations do I remember?
Common Mistake 1 — Every Table Gets id BIGINT
Use the real relational/business identity when appropriate।
Inventory may naturally use ProductId as its key।
Common Mistake 2 — Every Java Object Becomes a Table
DTOs, handlers, commands, and value objects do not automatically need tables।
Common Mistake 3 — One Huge Table
Variable relationships such as OrderItems belong naturally in related rows/tables।
Common Mistake 4 — No Foreign Keys
Application checks alone do not protect persisted referential integrity from every bad write।
Common Mistake 5 — Cascade Delete Everywhere
Deletion semantics must follow business lifecycle, especially historical Orders।
Common Mistake 6 — Recalculate Historical Price From Product
OrderItem needs its purchase-time unit_price persisted separately।
Common Mistake 7 — Duplicate Current Facts Everywhere
Mutable Product state should not be repeated across unrelated rows without a reason।
Common Mistake 8 — Database Stores Magic State Numbers
Readable, intentional persisted state representations are easier to operate and evolve।
Common Mistake 9 — Pagination Happens in Java
Filtering, ordering, and limiting should usually occur in PostgreSQL।
Common Mistake 10 — 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?
Relational Thinking
Asks:
What facts must be stored?
How are they related?
What keys and constraints protect them?
How will they be queried?
Persistence Mapping
Connects:
domain/application representation
↔
relational representation
PostgreSQL
Enforces:
durable relational state
keys
relationships
structural constraints
transactional writes
Engineering Principle
The core principle:
Objects model behaviour and identity 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:
Keys, foreign keys, uniqueness, and constraints are not database decoration—they encode important assumptions about valid persisted state.
And:
Use relational databases for what they are good at: filtering, joining, constraining, and atomically storing related data—while keeping business workflows explicit in the application.
Summary
In this lesson, we learned that:
- Java object models and relational database models represent the same business state in different ways.
- PostgreSQL thinks in tables, rows, columns, keys, constraints, and relationships rather than object graphs and methods.
- Primary keys uniquely identify persisted rows.
- Domain identity and relational primary keys often map naturally, but database tooling should not accidentally define domain identity.
- Inventory does not need a separate
InventoryId; ProductId can identify its row. - Foreign keys reinforce relationships such as Inventory → Product and OrderItem → Order.
- Order to OrderItem is a one-to-many relationship.
- Product to Inventory is conceptually one-to-one in our v1 model.
- OrderItems preserve purchase-time unit prices even though Products store current prices.
- Historical duplication can be correct when duplicated values represent different facts.
- Duplicate Product lines in one Order can be reinforced with relational uniqueness.
NOT NULL,CHECK,UNIQUE, and foreign-key constraints help protect persisted integrity.- CustomerId remains an external reference, so there is no local Customer foreign key in v1.
- SQL data types should reflect business meaning: decimal types for money-like values, integers for quantities, boolean for Product active state.
- OrderStatus needs an intentional persisted representation rather than an accidental magic numeric mapping.
- Schema deletion behaviour must follow business lifecycle; Product and Order history should not disappear through convenient cascades.
- PostgreSQL should perform filtering, joins, ordering, and pagination rather than loading all rows into Java.
- Read projections can be efficient without introducing CQRS architecture.
- Concurrency matters because multiple requests can modify the same relational rows.
- Database constraints help, but a concurrency-safe Inventory persistence strategy is still required later.
- Schema design should be understandable, explicit, and driven by business facts plus real query patterns.
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 columns and the constraints we can already justify from our accepted requirements.