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 look like object graphs: an Order holds a List<OrderItem>. PostgreSQL sees the same business data as tables, rows, columns, keys, constraints and relationships.

A backend engineer needs to understand both representations, because the persistence layer translates between them. 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, a Product is an object with identity, state, methods and business behaviour:

Product product = Product.create(productId, "Keyboard", 10000);

product.changePrice(12000);
product.deactivate();

In PostgreSQL, the same Product is a row:

id                                   | name     | price_cents | active
-------------------------------------+----------+-------------+--------
59ba787c-6d83-4a21-9bae-19c3d0469bd5 | Keyboard |       10000 | t

The values are familiar; the model is different. A row does not execute product.deactivate(); it stores durable state. When domain behaviour changes active from true to false, persistence eventually translates that into something conceptually like:

UPDATE products
SET active = false
WHERE id = :productId;

The domain operation and the SQL mutation are connected, but they are not the same responsibility.

Think in Relations

In relational theory, a relation is a set of rows (tuples) that share the same attributes. In practical PostgreSQL work, we think in tables, rows and columns. Our main tables will be products, inventory, orders and order_items, and each represents a coherent category of persisted business facts: one row is one persisted record, and the columns are its stored attributes.

A table is not a Java class dump: it should not simply contain every field from every related object. This would be a poor Order table:

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, ...

The number of OrderItems is variable, so no fixed set of columns fits. The relational model gives us a better structure: an orders table and an order_items table, connected through keys.

Primary Keys

A table needs a way to uniquely identify each row: its primary key. A primary key is always unique and never NULL. For Product, products.id is the natural primary key, and the mapping is ProductId → java.util.UUID → PostgreSQL UUID; Order works the same way with orders.id. The database supports identities we already chose in the domain; it does not invent them for us.

A key does not have to be a dedicated id column. Inventory is identified by its ProductId, because v1 has one Inventory state per Product, so inventory.product_id can itself be the primary key. Lesson 6.04 looks at why we do not add surrogate IDs out of habit.

Foreign Keys and Relationships

A foreign key expresses a relationship between rows in different tables. For example, inventory.product_id references products.id, and PostgreSQL then enforces that Inventory cannot reference a Product that does not exist. Without it, a bug could persist an inventory row for a product_id that no Product has: an orphaned relationship.

Our domain says one Order has many OrderItems. Relationally, this is a classic one-to-many relationship, and the foreign key lives on the "many" side: order_items.order_id references orders.id. One Order row can relate to many OrderItem rows:

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

The Product–Inventory relationship is one Product to zero or one Inventory row, because missing Inventory is not the same as Inventory with quantity 0. Making inventory.product_id the primary key ensures at most one Inventory row per Product, without requiring every Product to have one.

Each OrderItem also references its Product through order_items.product_id. That preserves Product identity, but OrderItem still persists its own unit_price_cents: we never reconstruct a historical price from the current products.price_cents.

Our three foreign keys are therefore inventory.product_id → products.id, order_items.order_id → orders.id and order_items.product_id → products.id. orders.customer_id has none, because there is no local customers table to reference; lesson 6.04 explains what protects it instead.

Joining Tables

A foreign key means a relationship exists. It does not mean every query must join every related table. Order history may query primarily orders and return summary fields, while Order detail needs orders plus order_items. SQL's JOIN combines related rows, which lets the persistence layer reconstruct an Order with its OrderItems:

SELECT ...
FROM orders
JOIN order_items
    ON order_items.order_id = orders.id
WHERE orders.id = :orderId;

Customer Product browsing, active = true and available_quantity > 0, combines products and inventory:

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;

The public API sees GET /api/v1/products, while persistence combines two tables efficiently. The inner join also drops Products without an Inventory row, which matches our rule that missing Inventory is not orderable. And Product and Inventory being separate responsibilities does not mean PostgreSQL should avoid joining them: Domain ownership and query execution are different concerns.

Normalization

Relational design tries to store each changing fact in one appropriate place. This is normalization. The Product name, current price and active state belong to Product; the available quantity belongs to Inventory.

An update anomaly shows why. Imagine the current Product name were copied into thousands of current-state records, and an Admin renames the Product. Every copy must now change consistently; if one is missed, the database contains contradictory versions of the same current fact.

Historical facts are different. The current products.price_cents may be 12000 while an old OrderItem records 10000, and both are correct: they are the current commercial price and the purchase-time price, not two copies of one changing fact. Duplication is about semantics:

Duplication is problematic when multiple values are intended to represent the same changing fact.

Normalization also means storing facts, not accidental derivations. The Order total is derived from quantity × unit_price_cents, so an orders.total_cents column would be another mutable source of truth that could drift from the order_items rows.

Normalization is a tool, not a contest to maximize the number of tables. We do not need product_names, product_prices and product_active_states merely because those facts could be decomposed further; that would create unnecessary joins. The opposite extreme, one giant table holding Orders, OrderItems, Product state and Inventory, produces duplication, awkward variable-length structures and update anomalies. Good relational design finds a practical middle ground.

Think About Functional Relationships

A useful relational question is: given this key, which facts should it determine?

This helps identify primary keys, uniqueness rules and table boundaries. The last row is a composite key, a key made of several columns: because one Order cannot contain the same Product twice, the pair (order_id, product_id) identifies one Product line within an Order, and as the primary key of order_items it expresses both row identity and duplicate-Product prevention. Lesson 6.04 looks at this key in detail.

Nullability

Columns can allow or reject NULL; the question is is absence a valid business state? In our v1 model, every persisted column is required, so every column is NOT NULL where the primary key does not already imply it. Java null and SQL NULL are related concepts in different systems, and our rule spans both: required business state is required in the domain and in the schema. We do not make a column nullable merely because it makes ORM mapping convenient.

Data Types Matter

Java and PostgreSQL need compatible representations. Our canonical mappings are:

TIMESTAMPTZ stores an absolute point in time (PostgreSQL normalizes it to UTC), which matches Instant and does not depend on a server's local timezone. In PostgreSQL, TEXT performs the same as VARCHAR, so we only add a length limit when the business has one. Money stays in integer minor units, as in the domain (4.06): never REAL or DOUBLE PRECISION for authoritative money. Quantities are whole numbers, so INTEGER, not a decimal type.

Product's lifecycle has two states, so BOOLEAN fits. The Order lifecycle has three, UNPAID, PAID and CANCELLED, which we store as readable text rather than magic numbers such as 2 meaning PAID; the next lesson defines the column and its constraint.

CHECK Constraints

PostgreSQL can reinforce simple row-level invariants with CHECK constraints:

CHECK (price_cents >= 0)
CHECK (available_quantity >= 0)
CHECK (quantity > 0)
CHECK (unit_price_cents >= 0)

These constraints complement request validation, domain validation and use case rules; they do not replace them. They are also documentation: a column defined as available_quantity INTEGER NOT NULL CHECK (available_quantity >= 0) tells another engineer exactly which persisted Inventory state is valid.

Constraints are best at structure, relationships and simple persisted invariants, not workflow. No foreign key or CHECK can decide whether order.cancel() is allowed for a PAID Order. PostgreSQL could perhaps encode that through increasingly complicated mechanisms, but it would move lifecycle behaviour away from Order.cancel(), where it is easier to understand and test.

Unique Constraints and Primary Keys

Both a primary key and a UNIQUE constraint enforce uniqueness, but a primary key also defines the table's principal row identity and rejects NULL. A table has exactly one primary key and may have additional unique constraints. A UNIQUE constraint alone permits NULLs, and by default several rows may have one, because PostgreSQL treats NULLs as distinct. Our uniqueness facts, "one Inventory row per Product" and "one Product line per Order", are already expressed through primary keys.

Delete Behaviour Follows the Lifecycle

A foreign key can also define what happens when a referenced row is deleted, for example cascading the delete. Deletion semantics must match the business lifecycle, and ours keeps history: Products are deactivated and Orders cancelled, never deleted. Lesson 6.04 compares PostgreSQL's options.

Rows Are Shared State

Multiple requests may modify the same rows concurrently. Suppose available_quantity = 1 and two customers request the final unit at the same moment. A naive flow selects the quantity, checks 1 >= 1 in Java and updates it to 0; both requests can read 1 before either commits, and both may believe they succeeded. A CHECK constraint protects persisted validity, but it does not decide which competing request gets the last unit. So relational thinking includes transactions, row locking and atomic updates, not only tables and columns; ADR-005's conditional UPDATE is implemented in lesson 7.08.

Think in Queries, Not Object Traversal

Java encourages expressions such as order.items(). A relational database works through queries. For GET /api/v1/orders?page=0&size=20, the ownership filter (WHERE customer_id = :customerId), the ordering (ORDER BY created_at DESC, id DESC) and the pagination (LIMIT/OFFSET) all happen in PostgreSQL, which returns only a bounded result. The bad alternative loads all Orders, filters by CustomerId in Java, sorts in Java and takes the first 20. PostgreSQL is designed to filter, sort, join, aggregate and paginate close to the data. Use it, while the Order lifecycle, ownership semantics and payment orchestration stay in the application.

Naming Matters

Prefer names that express business meaning: available_quantity, customer_id, unit_price_cents and created_at are good; qty1, owner_ref_value, price2 and date_value are poor.

Java uses camelCase (availableQuantity), while PostgreSQL conventionally uses snake_case (available_quantity). Persistence mapping translates between the conventions, and PostgreSQL folds unquoted identifiers to lower case, so camelCase columns would also force quoting everywhere. Our table names, products, inventory, orders and order_items, need no prefixes such as tarkev_products, because the database already belongs to this application.

The schema is not a public HTTP contract, but it is an internal contract between application versions, migrations, production data and operations, which is why it will evolve through explicit Flyway migrations.

Why This Shape Fits Our Workflows

The model supports our confirmed behaviour without inventing extra subsystems.

Relational Thinking Checklist

When designing a table, ask:

• What business fact does one row represent, and what uniquely identifies that row?
• Which columns are required, and which simple constraints protect valid values?
• Which other rows does this row reference, and should those relationships use foreign keys?
• Can duplicates represent invalid business state?
• What queries need this table, and what data changes concurrently?
• Which values represent current state, and which represent historical state?

They produce stronger schemas than "Which JPA annotations do I remember?"

Practice Exercise 1: Ask PostgreSQL

Solutions: lesson 6.14.

Open psql in the Compose database (3.08). Predict each result, then run the statements. CREATE TEMP TABLE creates a table that disappears when the session ends, so the database stays empty for the schema we design next.

SELECT 0.1::double precision + 0.2::double precision = 0.3::double precision
           AS double_equal,
       10::bigint + 20::bigint = 30::bigint AS cents_equal;

SELECT NULL = NULL AS null_equals_null;

CREATE TEMP TABLE practice_codes (code TEXT UNIQUE);
INSERT INTO practice_codes VALUES (NULL), (NULL);
INSERT INTO practice_codes VALUES ('A'), ('A');

SET TIME ZONE 'Europe/Istanbul';
SELECT '2026-10-22T09:15:00Z'::timestamptz AS created_at;
SET TIME ZONE 'UTC';
SELECT '2026-10-22T09:15:00Z'::timestamptz AS created_at;
SELECT '2026-10-22T12:15:00+03:00'::timestamptz
           = '2026-10-22T09:15:00Z'::timestamptz AS same_instant;

Which result explains our money type, which one our NOT NULL rule, and which one TIMESTAMPTZ for createdAt?

Practice Exercise 2: Split the Spreadsheet

A support team keeps Orders in a spreadsheet with one row per ordered line and these columns: order_id, customer_id, status, created_at, product_id, product_name, current_price_cents, unit_price_cents, quantity, available_quantity, line_total_cents.

1. For each column, name the key that determines it (see "Think About Functional Relationships").
2. Split the columns into our four tables, and name the columns you drop and why.
3. Describe one update anomaly the spreadsheet already has.

Practice Exercise 3: Which Products Can a Customer See?

In the same psql session, create two temporary tables and four Products:

CREATE TEMP TABLE products (
    id TEXT PRIMARY KEY, name TEXT NOT NULL, active BOOLEAN NOT NULL);
CREATE TEMP TABLE inventory (
    product_id TEXT PRIMARY KEY REFERENCES products(id),
    available_quantity INTEGER NOT NULL);

INSERT INTO products VALUES ('p1', 'Desk Lamp', TRUE), ('p2', 'Keyboard', TRUE),
       ('p3', 'Monitor', TRUE), ('p4', 'Mouse', FALSE);
INSERT INTO inventory VALUES ('p1', 12), ('p2', 0), ('p4', 5);

Predict which names the browse query from "Joining Tables" returns, then run it. Run it again with LEFT JOIN and without the WHERE clause, and explain each Product that was missing from the first result.

Knowledge Check

Question 1

Why does the foreign key of the Order–OrderItem relationship live in order_items, not in orders?

Question 2

order_items.unit_price_cents repeats a price that products also holds. Why is that not the duplication normalization warns about, while an orders.total_cents column would be?

Question 3

Why can a CHECK (available_quantity >= 0) constraint not stop two Customers from both buying the last unit?

Question 4

A UNIQUE column accepts several NULLs. Why does that not weaken our uniqueness rules?

Question 5

Why should Order history be filtered, sorted and paged in PostgreSQL rather than in Java?

Knowledge Check Answers

Answer 1

One Order has a variable number of OrderItems, and a row can hold only one value per column. Each OrderItem row points to its one Order, so the "many" side carries the reference.

Answer 2

unit_price_cents is a different fact: the price at purchase time, which must not follow later price changes. A stored total would be a second copy of a value derived from the item rows, and the two could drift apart.

Answer 3

The constraint only checks that each written value is valid. Both requests can read 1 and both write 0, which is valid; deciding who gets the unit needs an atomic update or a lock (7.08).

Answer 4

Our uniqueness facts are primary keys, which never allow NULL: inventory.product_id and (order_id, product_id).

Answer 5

Only a bounded page leaves the database, and the ownership filter is part of the query, so other Customers' Orders never reach the application. Loading everything and filtering in Java costs memory and time for every request.

Engineering Principle

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.

Keys, nullability and CHECK constraints encode assumptions about valid persisted state; business workflows stay explicit in the application.

Summary

• Objects carry identity and behaviour, tables store durable facts and relationships, and persistence mapping translates between them.
• Our tables are products, inventory, orders and order_items, with UUID keys for Products and Orders, inventory.product_id as primary and foreign key, and the composite key (order_id, product_id) for OrderItems.
• Order to OrderItem is one-to-many; Product to Inventory is zero or one; customer_id has no local foreign key.
• Types carry meaning: BIGINT cents, INTEGER quantities, TIMESTAMPTZ for createdAt, BOOLEAN for active and readable text statuses.
• NOT NULL, CHECK, unique and foreign-key constraints reinforce domain rules without replacing them; lifecycle rules stay in Java.
• Normalization keeps one home for each current fact; historical snapshots such as unit_price_cents are intentional, and the Order total stays derived.
• Filtering, joining and pagination happen in PostgreSQL; concurrent writes to shared rows need more than constraints.

In the next lesson, we turn these concepts into the first concrete PostgreSQL schema: exact types, keys, NOT NULL rules, CHECK constraints, the composite OrderItem key and the Order-history index our requirements already justify.