Persistence with PostgreSQL

Designing the Initial Schema

ReadingPreview

আপনি একটি free preview lesson দেখছেন।

এখন আমাদের কাছে enough information আছে প্রথম concrete PostgreSQL schema design করার জন্য।

আমরা already know durable application state:

Product

Inventory

Order

OrderItem

আমরা আরও জানি:

Customer identity
→ external system owns it

Payment persistence
→ এখনো provider contract-এর উপর নির্ভর করছে

তাই initial schema-তে আমরা intentionally রাখব:

products

inventory

orders

order_items

এবং intentionally রাখব না:

customers

payments

inventory_reservations

warehouses

refunds

audit_ledgers

এই lesson-এর goal:

Accepted business requirements এবং query needs থেকে minimal কিন্তু integrity-aware PostgreSQL schema design করা—without inventing future features or letting JPA dictate the database.

Start From Durable Business Facts

Schema design শুরু করার সময় প্রথম প্রশ্ন হওয়া উচিত নয়:

Which JPA annotations do we need?

বরং:

What business facts must survive?

Our current facts:

Product

identity

name

current price

active state

Inventory

Product identity

available quantity

Order

identity

owner CustomerId

lifecycle status

creation time

OrderItem

parent Order

Product reference

quantity

purchase-time unit price

এটাই schema design-এর foundation।

Decide Identifier Strategy

আগের lessons-এ আমরা examples ব্যবহার করেছি:

P-100

O-1001

এগুলো readability-এর জন্য illustrative identifiers ছিল।

তখন আমরা intentionally exact persisted ID strategy defer করেছিলাম।

এখন schema design-এর সময় একটি concrete decision দরকার।

For this application, we'll use:

BIGINT generated identity

for:

ProductId

OrderId

Why?

Our application is:

one modular monolith

one PostgreSQL database

We currently do not need:

distributed ID generation

offline ID generation

multi-region writers

PostgreSQL-generated numeric identity is:

simple

compact

efficient to index

easy to understand

So we should not introduce UUID complexity merely because UUID is common in APIs।

Domain IDs Still Remain Typed

Choosing PostgreSQL:

BIGINT

does not mean application code should pass random long values everywhere।

We can still have:

public record ProductId(long value) {
}

and:

public record OrderId(long value) {
}

The domain gets meaningful types।

PostgreSQL gets an efficient relational representation।

Sequential IDs Are Not Authorization

Because Product and Order IDs are predictable, someone may be able to guess:

That is not a security problem if authorization is implemented correctly।

Never rely on:

"the ID is hard to guess"

as access control।

For:

GET /api/v1/orders/1002

we still verify authenticated ownership।

Authorization protects the resource, not identifier obscurity।

`products` Table

Our Product state:

ProductId

name

price

active

maps naturally to:

CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC NOT NULL,
    active BOOLEAN NOT NULL,

    CONSTRAINT products_price_non_negative
        CHECK (price >= 0)
);

Let's examine every decision।

`products.id`

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

means PostgreSQL generates Product identity।

The application creates:

new Product

without accepting ProductId from the API client।

This aligns with our earlier API rule:

ProductId
→ server controlled

Why PostgreSQL Identity Instead of Legacy `SERIAL`?

PostgreSQL provides identity columns as the modern SQL-standard mechanism for generated numeric identities।

Conceptually:

GENERATED ALWAYS AS IDENTITY

makes generation part of the column definition।

We don't need to manually manage a separate sequence in application code।

Product Name

name TEXT NOT NULL

Why TEXT?

Our requirements do not define:

maximum Product name = 100

maximum Product name = 255

Choosing:

VARCHAR(255)

would therefore introduce an arbitrary contract।

PostgreSQL TEXT lets us avoid inventing a maximum that product requirements never specified।

Does `NOT NULL` Prevent Blank Names?

No।

This row is technically allowed by the database:

name = ""

Our request validation and Product domain currently protect blank names।

We could create a more complicated database CHECK against blank/whitespace values, but that's not necessary for our initial schema।

The database reinforces the most important structural requirement:

name must exist

Domain/application owns meaningful textual validation।

Product Price

price NUMERIC NOT NULL

maps naturally to Java:

BigDecimal

We intentionally avoid:

REAL

DOUBLE PRECISION

for authoritative monetary values।

Why No `NUMERIC(10,2)`?

Because we currently do not have a requirement defining:

maximum price

required decimal scale

If we write:

NUMERIC(10, 2)

we silently introduce both।

PostgreSQL allows unconstrained:

NUMERIC

which is suitable until actual precision/scale requirements are decided।

This preserves exact decimal arithmetic without inventing an arbitrary maximum।

Product Price Constraint

We know:

price >= 0

So database can reinforce:

CHECK (price >= 0)

This aligns with:

Product.changePrice(...)

and request validation:

@PositiveOrZero

Three boundaries now agree:

HTTP request validation

Product invariant

PostgreSQL constraint

Product Active State

active BOOLEAN NOT NULL

We do not use:

deleted_at

status = ACTIVE/INACTIVE/ARCHIVED

soft_deleted

because our Product lifecycle currently needs only:

active

inactive

A boolean accurately represents the requirement।

Why No Database Default for `active`?

We could write:

active BOOLEAN NOT NULL DEFAULT TRUE

But then Product initial lifecycle behaviour partly lives invisibly in the database।

Our application/domain creation flow already decides Product's initial state।

So initially:

active BOOLEAN NOT NULL

keeps that choice explicit in application behaviour।

Product Is Never Physically Deleted by the Business

Our API provides:

POST /api/v1/products/{productId}/deactivate

not:

DELETE /api/v1/products/{productId}

Therefore historical references to Product remain valid।

Schema design should preserve that lifecycle।

`inventory` Table

Inventory state is:

ProductId

available quantity

and there is exactly:

one Inventory state per Product

A natural schema:

CREATE TABLE inventory (
    product_id BIGINT PRIMARY KEY,
    available_quantity INTEGER NOT NULL,

    CONSTRAINT inventory_product_fk
        FOREIGN KEY (product_id)
        REFERENCES products(id),

    CONSTRAINT inventory_quantity_non_negative
        CHECK (available_quantity >= 0)
);

No `inventory.id`

Notice:

inventory

does not have:

id BIGINT

because we already have the correct identity:

ProductId

This avoids a meaningless persistence-only identifier।

`product_id` Is Both Identity and Relationship

product_id BIGINT PRIMARY KEY

means:

one Inventory row per Product

And:

FOREIGN KEY (product_id)
REFERENCES products(id)

means:

Inventory cannot reference a Product that does not exist

This models our domain relation cleanly।

Inventory Quantity

available_quantity INTEGER NOT NULL

fits our business meaning:

whole number

non-negative

Database reinforces:

CHECK (available_quantity >= 0)

Why Not Store Inventory on Product?

We could theoretically add:

products.available_quantity

But our domain design deliberately separated:

Product

from:

Inventory

because they have different responsibilities and change patterns।

Keeping separate tables preserves that distinction।

It also makes Inventory-specific concurrency and query behaviour easier to reason about later।

What Happens When Product Is Created?

Do we automatically create:

Inventory quantity = 0

at the database level?

Our requirements have not established that policy explicitly।

So we do not add:

trigger

automatic row creation

default Inventory record

as hidden database behaviour।

Product and Inventory workflow will deliberately create/manage the required Inventory state according to application behaviour।

`orders` Table

An Order must preserve:

OrderId

CustomerId

OrderStatus

creation time

Initial schema:

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id TEXT NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT orders_status_valid
        CHECK (
            status IN (
                'UNPAID',
                'PAID',
                'CANCELLED'
            )
        )
);

Order ID

Like Product:

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

makes Order identity server-controlled।

The client never chooses:

OrderId

during:

POST /api/v1/orders

Customer ID

customer_id TEXT NOT NULL

stores the stable external identity used for Order ownership।

There is intentionally no:

FOREIGN KEY (customer_id)
REFERENCES customers(id)

because:

customers

is not an application-owned table in v1।

Why `TEXT` for CustomerId?

Exact external identity representation belongs to the external identity contract।

Our application only requires:

stable identifier

Using TEXT allows us to preserve that identifier without inventing:

UUID

numeric

specific length

requirements that may not match the external system।

Order Status

Current lifecycle:

UNPAID

PAID

CANCELLED

We need to store exactly these states।

We choose:

status TEXT NOT NULL

plus:

CHECK (
    status IN (
        'UNPAID',
        'PAID',
        'CANCELLED'
    )
)

Why Not PostgreSQL ENUM?

PostgreSQL has native enum types।

We could create:

CREATE TYPE order_status AS ENUM (...);

That can work।

But application lifecycle values may evolve।

A TEXT + CHECK representation keeps:

schema readable

mapping simple

future migration straightforward

without introducing a database enum type that must itself be evolved separately।

For this application, that's a pragmatic choice।

Why Not Numeric Status Codes?

Avoid:

0 = UNPAID

1 = PAID

2 = CANCELLED

because raw persisted data becomes less understandable।

Readable database values are useful during:

debugging

incidents

manual inspection

Status Constraint Does Not Protect Transitions

This:

CHECK (
    status IN (
        'UNPAID',
        'PAID',
        'CANCELLED'
    )
)

prevents:

status = "UNKNOWN"

But it does not prevent:

PAID → UNPAID

That is an important distinction।

Valid values are a row-level integrity rule।

Valid transitions are domain behaviour:

order.markPaid();

order.cancel();

`created_at`

Order history needs deterministic chronology।

Therefore:

created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP

is justified persisted state।

This gives us a stable temporal fact:

When was this Order created?

Later Order history can use:

created_at DESC

for newest-first browsing।

Why `TIMESTAMPTZ`?

For backend persistence, absolute time should not depend on whichever timezone happens to be configured on the application machine।

PostgreSQL:

TIMESTAMPTZ

is a natural choice for absolute timestamps।

Application code can map this to a type such as:

Instant

later।

Why Database Default for `created_at`?

Unlike Product lifecycle state, Order creation timestamp is naturally tied to persistence creation।

Using:

DEFAULT CURRENT_TIMESTAMP

gives every inserted Order a creation time even if application code forgets to explicitly provide one।

This is a useful persistence-level default rather than hidden business workflow।

`order_items` Table

OrderItem must preserve:

parent Order

Product reference

quantity

purchase-time unit price

Schema:

CREATE TABLE order_items (
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price NUMERIC NOT NULL,

    CONSTRAINT order_items_pk
        PRIMARY KEY (
            order_id,
            product_id
        ),

    CONSTRAINT order_items_order_fk
        FOREIGN KEY (order_id)
        REFERENCES orders(id),

    CONSTRAINT order_items_product_fk
        FOREIGN KEY (product_id)
        REFERENCES products(id),

    CONSTRAINT order_items_quantity_positive
        CHECK (quantity > 0),

    CONSTRAINT order_items_unit_price_non_negative
        CHECK (unit_price >= 0)
);

Why No `order_item_id`?

Our domain has no:

OrderItemId

An OrderItem belongs entirely to an Order।

We already have a natural relational identity:

order_id
+
product_id

because our business contract rejects duplicate Products inside one Order।

So:

PRIMARY KEY (
    order_id,
    product_id
)

fits the domain well।

Composite Primary Key Reinforces Duplicate Prevention

Our application already rejects:

{
  "items": [
    {
      "productId": "100",
      "quantity": 1
    },
    {
      "productId": "100",
      "quantity": 2
    }
  ]
}

Database additionally prevents two rows with the same:

order_id + product_id

This gives us defense in depth।

OrderItem Quantity

quantity INTEGER NOT NULL

plus:

CHECK (quantity > 0)

aligns with:

CreateOrderItemRequest
→ quantity positive

and:

OrderItem
→ quantity positive

OrderItem Unit Price

unit_price NUMERIC NOT NULL

stores:

purchase-time Product price

This is not a reference to current Product price।

It is historical Order data।

Constraint:

CHECK (unit_price >= 0)

reinforces the accepted domain rule।

Why No Product Name Snapshot?

We intentionally decided to preserve:

purchase-time price

but not Product name snapshot।

So OrderItem stores:

product_id

quantity

unit_price

not:

product_name

unless future product requirements explicitly require historical naming snapshots।

Don't turn a reasonable possibility into current scope।

Why No OrderItem Total Column?

OrderItem total is:

unit_price × quantity

So storing:

item_total

would create another value that must always remain consistent with those two columns।

Current domain already defines subtotal as derived।

Therefore we don't persist it।

Why No `orders.total` Column?

Similarly:

Order.total

is derived from:

SUM(
    OrderItem.unitPrice × quantity
)

Our accepted design says total must not be an independent mutable source of truth।

So the initial schema does not store:

orders.total

We derive it from OrderItems।

Is Calculating Total on Read Expensive?

For an Order, the number of items is normally bounded by realistic request size even though we haven't invented a hard maximum।

Calculating:

quantity × unit_price

and summing those lines is inexpensive relative to the benefit of having one authoritative pricing source।

If real performance data later justifies denormalizing totals, we can revisit it deliberately।

The Complete Initial Schema

Putting the current design together:

CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC NOT NULL,
    active BOOLEAN NOT NULL,

    CONSTRAINT products_price_non_negative
        CHECK (price >= 0)
);

CREATE TABLE inventory (
    product_id BIGINT PRIMARY KEY,
    available_quantity INTEGER NOT NULL,

    CONSTRAINT inventory_product_fk
        FOREIGN KEY (product_id)
        REFERENCES products(id),

    CONSTRAINT inventory_quantity_non_negative
        CHECK (available_quantity >= 0)
);

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id TEXT NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT orders_status_valid
        CHECK (
            status IN (
                'UNPAID',
                'PAID',
                'CANCELLED'
            )
        )
);

CREATE TABLE order_items (
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price NUMERIC NOT NULL,

    CONSTRAINT order_items_pk
        PRIMARY KEY (
            order_id,
            product_id
        ),

    CONSTRAINT order_items_order_fk
        FOREIGN KEY (order_id)
        REFERENCES orders(id),

    CONSTRAINT order_items_product_fk
        FOREIGN KEY (product_id)
        REFERENCES products(id),

    CONSTRAINT order_items_quantity_positive
        CHECK (quantity > 0),

    CONSTRAINT order_items_unit_price_non_negative
        CHECK (unit_price >= 0)
);

This is our first concrete schema direction।

Why No Explicit `ON DELETE CASCADE`?

We intentionally avoid:

ON DELETE CASCADE

for these historical relationships।

For example, we do not want deleting Product to silently delete:

OrderItems

and destroy history।

Our business operations don't physically delete Product or Order anyway।

Leaving deletion restricted by relationships is the safer default for the current lifecycle।

What If Someone Tries to Delete an Order?

Because OrderItems reference it, PostgreSQL should reject deleting the Order while those rows exist unless persistence code explicitly removes them first।

That's acceptable because:

delete Order

is not a supported v1 business operation।

Cancellation is:

state transition

not deletion।

Constraint Naming

We deliberately name important constraints:

products_price_non_negative

inventory_product_fk

inventory_quantity_non_negative

orders_status_valid

order_items_quantity_positive

Why?

If PostgreSQL later reports a constraint violation, a meaningful name is easier to understand than a generated name।

This becomes useful in:

debugging

logs

migrations

incidents

Constraint Names Are Internal

However, do not return:

inventory_quantity_non_negative

directly to API clients।

Our API error vocabulary remains:

INSUFFICIENT_INVENTORY

VALIDATION_ERROR

or another meaningful application code।

Database constraint names are infrastructure details।

Rules the Schema Can Protect Well

Our initial database can strongly reinforce:

Product ID uniqueness

Order ID uniqueness

Product price >= 0

one Inventory row per Product

Inventory quantity >= 0

Inventory references existing Product

Order has CustomerId

Order status contains a recognized value

OrderItem references an Order

OrderItem references a Product

OrderItem quantity > 0

OrderItem unit price >= 0

one Product line per Order

These are excellent relational integrity rules।

Rules the Schema Does Not Try to Own

The schema does not directly enforce:

Order must contain at least one item

Product must be active when Order is created

Inventory must be sufficient before Order creation

PAID Order cannot be cancelled

CANCELLED Order cannot be paid

Order belongs to authenticated caller

cancellation restores Inventory

Order creation decreases Inventory

payment provider success marks Order PAID

These are application/domain workflow rules।

Why Not Enforce "Order Must Have at Least One Item" in SQL?

A simple row-level:

CHECK

on orders cannot inspect another table in the way we'd need।

We could introduce:

triggers

deferred constraints

complex database logic

But our application already creates Order + OrderItems as one transaction and Domain prevents empty Orders।

Moving this rule into complex database machinery would add more complexity than value।

Transaction Protects Multi-Table Integrity

Suppose CreateOrder writes:

orders row

but crashes before:

order_items rows

If those writes occur inside one transaction:

entire transaction rolls back

So we avoid persisting an incomplete Order।

This is where:

transaction

protects cross-row workflow integrity that simple constraints cannot fully express।

Schema Is Not Enough for Inventory Concurrency

We have:

CHECK (available_quantity >= 0)

which is useful।

But concurrency problem remains:

two customers
→ same last inventory unit

We still need a concurrency-safe persistence strategy।

The initial schema supports that because:

Inventory is represented by one row per Product

which gives us a clear row to update/lock atomically।

The exact strategy remains a later implementation decision।

Product Browse Query

Our customer browse contract:

active Product

AND

available quantity > 0

maps naturally to this schema:

SELECT
    p.id,
    p.name,
    p.price
FROM products p
JOIN inventory i
    ON i.product_id = p.id
WHERE p.active = TRUE
  AND i.available_quantity > 0;

Pagination and deterministic ordering will be added by Repository query implementation।

Order History Query

Customer Order history:

authenticated CustomerId

bounded page

newest first

maps naturally to:

SELECT
    id,
    customer_id,
    status,
    created_at
FROM orders
WHERE customer_id = ?
ORDER BY created_at DESC, id DESC;

The:

id DESC

acts as a deterministic tie-breaker if two Orders share the same creation timestamp।

Why `created_at, id`?

If two rows have:

same created_at

ordering only by timestamp can leave their relative order unspecified।

Adding:

id DESC

makes ordering deterministic।

This matters for pagination।

Order Detail Query

Loading full Order likely needs:

orders

plus:

order_items

Conceptually:

SELECT ...
FROM orders o
JOIN order_items oi
    ON oi.order_id = o.id
WHERE o.id = ?;

Persistence mapping can reconstruct:

Order
    └── List<OrderItem>

from these rows।

Why Keep Product Foreign Key on OrderItem?

Could OrderItem simply store a historical Product ID without a foreign key?

Possible।

But our normal business lifecycle keeps Products rather than physically deleting them।

So enforcing:

OrderItem → Product

is useful and compatible with our deactivation model।

It protects against accidentally creating an Order line referencing a Product that never existed locally।

Current Product Name Can Still Change

Because OrderItem stores:

product_id

a detailed history response could potentially resolve current Product information later।

But purchase-time price remains preserved independently।

Whether customer history should show current Product name or historical name is a product decision we have not made।

The schema does not invent a snapshot requirement।

Why No Timestamps Everywhere?

A common schema template adds:

created_at

updated_at

to every table automatically।

We don't do that blindly।

Current requirements justify:

orders.created_at

because Order history needs meaningful stable chronology।

We do not currently need:

inventory.created_at

inventory.updated_at

order_items.updated_at

So we leave them out।

Why No `version` Column Yet?

Optimistic locking may eventually use:

version

columns।

But we have not selected our Inventory concurrency strategy yet।

Adding:

version BIGINT

now would prematurely commit to one solution।

Schema evolves when the concurrency design is selected।

Why No Audit Columns?

We are not implementing:

created_by

updated_by

change_reason

audit_log

because those are not current requirements।

Admin Inventory v1 simply supports:

set available quantity

not a stock movement/audit ledger।

Why No Payment Columns on Order?

It may be tempting to add:

payment_provider

payment_reference

payment_attempt_count

to orders now।

But the external Payment Provider contract is intentionally deferred।

We only know Order lifecycle includes:

UNPAID

PAID

That is enough for the initial Order schema।

Provider-specific persistence will be introduced only when we know what the integration needs।

Why No Customer Table?

Again:

CustomerId

does not imply:

customers

Our application does not own:

customer registration

password

profile lifecycle

So initial schema should not pretend that it does।

Why No Cart Table?

Order is not Cart।

Current workflow creates Order directly from requested items।

We have no:

persistent cart

saved basket

checkout session

requirement।

No carts table।

Why No Reservation Table?

Our accepted inventory semantics:

successful Order creation
→ consumes/decreases available Inventory

Cancellation restores it।

We do not have:

temporary reservation

reservation expiry

hold timeout

So no:

inventory_reservations

table।

Why No Warehouse Table?

Inventory currently means:

one available quantity per Product

There is no:

warehouse

location

stock batch

concept।

Schema should remain aligned with actual scope।

This Is a Minimal Schema, Not a Toy Schema

Minimal does not mean careless।

We already have:

primary keys

foreign keys

NOT NULL

CHECK constraints

historical pricing

deterministic Order chronology

duplicate OrderItem prevention

What we don't have is speculative complexity।

That's the distinction।

Schema Design vs Migration

The SQL in this lesson represents our accepted initial schema design।

In production code we won't manually paste this SQL into a random running database।

Later:

Database Migrations with Flyway

will put schema changes into version-controlled migration files।

That makes schema creation:

repeatable

reviewable

CI-friendly

deployable

Schema Design vs JPA

Similarly, we don't start by writing:

@Entity

and letting Hibernate decide everything।

Direction remains:

Business requirements
    ↓
Domain
    ↓
Relational schema
    ↓
JPA mapping

not:

JPA defaults
    ↓
whatever schema appears

Initial Schema Review

Let's check it against requirements।

Product

Need:

identity
name
price
active

Covered।

Inventory

Need:

one quantity per Product
non-negative

Covered।

Order

Need:

identity
external Customer ownership
UNPAID / PAID / CANCELLED
history

Covered।

OrderItem

Need:

Product reference
positive quantity
historical unit price
no duplicate Product lines

Covered।

Missing by Design

Not required:

local Customer account

Payment provider persistence

Inventory reservation

refund state

shipping

tax

discount

warehouse

Cart

Correctly absent।

Common Mistake 1 — Arbitrary `VARCHAR(255)` Everywhere

Use explicit limits only when the contract requires them।

Common Mistake 2 — `DOUBLE` for Money

Use exact decimal storage for authoritative prices।

Common Mistake 3 — `id BIGINT` on Every Table

Inventory and OrderItem already have meaningful relational identities।

Common Mistake 4 — Store Order Total Independently

Current total is derived from authoritative OrderItem prices and quantities।

Common Mistake 5 — Current Product Price Used for Historical Order

OrderItem stores purchase-time unit_price।

Common Mistake 6 — PostgreSQL ENUM Chosen Automatically

State representation should consider migration and application evolution trade-offs।

Common Mistake 7 — `ON DELETE CASCADE` Everywhere

Historical Orders and Product references must not disappear accidentally।

Common Mistake 8 — Customer Table Added Because `customer_id` Exists

External identity ownership does not imply local Customer persistence।

Common Mistake 9 — Payment Columns Added Before Integration Design

Persist provider-specific state only when the actual Payment workflow needs it।

Common Mistake 10 — Timestamps and Audit Fields Added by Template

Every column should have a reason to exist।

Current Relational Model

Our initial persisted model is now:

products
--------
id PK
name
price
active

inventory
---------
product_id PK/FK → products.id
available_quantity

orders
------
id PK
customer_id
status
created_at

order_items
-----------
order_id PK/FK → orders.id
product_id PK/FK → products.id
quantity
unit_price

Relationship view:

products
   │
   ├──── 1 : 1 ──── inventory
   │
   └──── 1 : N ──── order_items
                         │
                         │ N : 1
                         ↓
                       orders

Customer ownership remains an external identifier:

orders.customer_id

without a local Customer table।

Engineering Principle

The core principle:

A good initial schema stores the facts we actually own, reinforces the invariants PostgreSQL can protect well, and avoids encoding speculative future features.

Another:

Schema constraints should reinforce the domain, while application workflows remain responsible for behaviour that spans state, authorization, and multiple rows.

And:

Every table and column should have a reason to exist. “We might need it someday” is not enough.

Summary

In this lesson, we:

Chose PostgreSQL-generated BIGINT identities for Product and Order.
Kept domain IDs strongly typed even though their persisted representation is numeric.
Used TEXT for Product names because no arbitrary maximum length is currently justified.
Used PostgreSQL NUMERIC for prices without inventing a precision/scale limit.
Added database checks preventing negative Product prices.
Kept Product active state as a simple required boolean.
Designed Inventory as one row per Product using product_id as its primary key.
Added Inventory referential integrity back to Product.
Reinforced non-negative Inventory quantity with a CHECK.
Stored external CustomerId directly on Order without creating a local Customer table.
Persisted OrderStatus as readable text with an allowed-value constraint.
Added created_at to Order to support stable chronological history.
Used TIMESTAMPTZ for absolute creation time.
Designed OrderItem using the composite key (order_id, product_id).
Used that composite key to reinforce the no-duplicate-Product-lines rule.
Persisted OrderItem quantity and purchase-time unit price.
Avoided storing OrderItem subtotal because it is derived.
Avoided storing Order total because the authoritative total remains derived from OrderItems.
Avoided physical-delete cascades that could destroy historical Order data.
Identified which business rules belong in database constraints and which remain domain/UseCase responsibilities.
Deliberately excluded Customer, Payment, Cart, Reservation, Warehouse, refund, and audit tables because current requirements do not justify them.
Kept indexes, JPA mappings, migration mechanics, and Inventory concurrency strategy for their dedicated lessons.

Next lesson:

Primary Keys and Foreign Keys

There we will go deeper into identity and referential integrity—how primary keys, composite keys, foreign keys, uniqueness, and delete behaviour protect our Product, Inventory, Order, and OrderItem data, and what guarantees they still cannot provide on their own.

Start From Durable Business Facts

Product

Inventory

Order

OrderItem

Decide Identifier Strategy

Domain IDs Still Remain Typed

Sequential IDs Are Not Authorization

products Table

products.id

Why PostgreSQL Identity Instead of Legacy SERIAL?

Product Name

Does NOT NULL Prevent Blank Names?

Product Price

Why No NUMERIC(10,2)?

Product Price Constraint

Product Active State

Why No Database Default for active?

Product Is Never Physically Deleted by the Business

inventory Table

No inventory.id

product_id Is Both Identity and Relationship

Inventory Quantity

Why Not Store Inventory on Product?

What Happens When Product Is Created?

orders Table

Order ID

Customer ID

Why TEXT for CustomerId?

Order Status

Why Not PostgreSQL ENUM?

Why Not Numeric Status Codes?

Status Constraint Does Not Protect Transitions

created_at

Why TIMESTAMPTZ?

Why Database Default for created_at?

order_items Table

Why No order_item_id?

Composite Primary Key Reinforces Duplicate Prevention

OrderItem Quantity

OrderItem Unit Price

Why No Product Name Snapshot?

Why No OrderItem Total Column?

Why No orders.total Column?

Is Calculating Total on Read Expensive?

The Complete Initial Schema

Why No Explicit ON DELETE CASCADE?

What If Someone Tries to Delete an Order?

Constraint Naming

Constraint Names Are Internal

Rules the Schema Can Protect Well

Rules the Schema Does Not Try to Own

Why Not Enforce "Order Must Have at Least One Item" in SQL?

Transaction Protects Multi-Table Integrity

Schema Is Not Enough for Inventory Concurrency

Product Browse Query

Order History Query

Why created_at, id?

Order Detail Query

Why Keep Product Foreign Key on OrderItem?

Current Product Name Can Still Change

Why No Timestamps Everywhere?

Why No version Column Yet?

Why No Audit Columns?

Why No Payment Columns on Order?

Why No Customer Table?

Why No Cart Table?

Why No Reservation Table?

Why No Warehouse Table?

This Is a Minimal Schema, Not a Toy Schema

Schema Design vs Migration

Schema Design vs JPA

Initial Schema Review

Product

Inventory

Order

OrderItem

Missing by Design

Common Mistake 1 — Arbitrary VARCHAR(255) Everywhere

Common Mistake 2 — DOUBLE for Money

`products` Table

`products.id`

Why PostgreSQL Identity Instead of Legacy `SERIAL`?

Does `NOT NULL` Prevent Blank Names?

Why No `NUMERIC(10,2)`?

Why No Database Default for `active`?

`inventory` Table

No `inventory.id`

`product_id` Is Both Identity and Relationship

`orders` Table

Why `TEXT` for CustomerId?

`created_at`

Why `TIMESTAMPTZ`?

Why Database Default for `created_at`?

`order_items` Table

Why No `order_item_id`?

Why No `orders.total` Column?

Why No Explicit `ON DELETE CASCADE`?

Why `created_at, id`?

Why No `version` Column Yet?

Common Mistake 1 — Arbitrary `VARCHAR(255)` Everywhere

Common Mistake 2 — `DOUBLE` for Money

Common Mistake 3 — `id BIGINT` on Every Table

Common Mistake 7 — `ON DELETE CASCADE` Everywhere

Common Mistake 8 — Customer Table Added Because `customer_id` Exists