The Relational Model

In Spring Boot 04 you typed @Entity and a table appeared in the log. Tonight you build the tables yourself, read every rule the database enforces, and then violate each rule on purpose — because the error messages are the curriculum. By the end, the DDL Hibernate generated for you stops being output and becomes something you can judge.

The Goal

By the end of this module you can:

• Explain what a table actually is, and how it differs from a Java class and from a JSON file
• Choose column types like an engineer — and say why money is paise in BIGINT, never FLOAT
• State what NULL means (and doesn’t mean) without hand-waving
• Define PRIMARY KEY and FOREIGN KEY in terms of what the database guarantees, not what they’re “for”
• Write NOT NULL, UNIQUE, and CHECK constraints and trigger each one’s real error message
• Create the simplified SplitEase schema in psql with your own hands

The Lesson

A table is not a class, and not a JSON file

A table is a set of rows, every one of which conforms to one declared shape. You declare the shape once — these columns, these types, these rules — and from that moment the database refuses any row that doesn’t fit. Not warns. Refuses.

That sounds like a Java class, but the differences are the whole point:

That last row matters more than it looks. SELECT * FROM expense with no ORDER BY returns rows in whatever order PostgreSQL feels like today — insertion order is not promised, and code that assumes it breaks the day the table grows. Order is something you ask for, never something you get.

And the Java comparison cuts the other way too: your Expense class enforced its shape only inside your JVM, only while your app ran. The table enforces it against the Spring app, the reporting script someone writes next year, and the intern poking around in psql at 6pm. One gate, everyone goes through it.

Column types that matter in practice

PostgreSQL has dozens of types. These five carry 95% of real backend work:

Two famous traps live in this table:

Trap 1 — money in FLOAT. REAL and FLOAT8 are binary floating point: SELECT 0.1::FLOAT8 + 0.2::FLOAT8; returns 0.30000000000000004. Same disease as double in Java, now living in your data for ten years. The track-wide rule from the Core Java capstone holds at the database layer too: money is paise as BIGINT. ₹3,000 is 300000. Exact addition, exact comparison, rupees only at display time. (NUMERIC is also exact and fine for money — but integer paise is simpler, faster, and matches your Java code. One rule, both layers.)

Trap 2 — TIMESTAMP without TZ. Plain TIMESTAMP stores a wall-clock reading with no timezone — “19:30 on June 12” with no idea where. The moment your server runs in UTC and your users live in IST, every plain timestamp is a lie off by 5:30. TIMESTAMPTZ stores an actual instant (internally UTC) and converts on display. The rule is one word long: always TIMESTAMPTZ.

One non-trap to unlearn from MySQL folklore: in PostgreSQL, TEXT has no performance penalty versus VARCHAR(n). Hibernate’s varchar(255) habit is a portability default, not wisdom. Use TEXT unless you have a real reason to cap length.

NULL means unknown — not zero, not empty

Every column can hold NULL unless you forbid it, and NULL does not mean 0 or "". It means unknown — no value was provided. That definition has a consequence that breaks most beginners:

SELECT NULL = NULL;

The answer is not true. It’s NULL. Is one unknown thing equal to another unknown thing? Unknown. SQL runs on three-valued logic — true, false, and unknown — and WHERE clauses only keep rows where the condition is true. The full pain (and the IS NULL cure) arrives in module 02; for today, install the definition: NULL is a question mark, and question marks compare to nothing — not even to each other.

PRIMARY KEY — the row’s identity

id BIGSERIAL PRIMARY KEY

PRIMARY KEY declares this column the row’s identity: it is automatically NOT NULL, automatically UNIQUE, and it is how every other table — and every future UPDATE and DELETE — will point at exactly this row.

BIGSERIAL is not really a type. It’s PostgreSQL shorthand for three things: a BIGINT column, a sequence (a database object whose only job is handing out the next number), and a default that pulls from it. Run \d friend after creating the table and you’ll see the truth:

id | bigint | not null default nextval('friend_id_seq'::regclass)

That sequence is what @GeneratedValue was leaning on all through the Spring track — “the database assigns the id” meant this. One property of sequences to burn in now: they hand out numbers and never take them back. A failed insert still consumes its number, so ids go 1, 2, 4 and that’s healthy, not corruption. Code (or auditors) expecting gapless ids are wrong about how databases work.

FOREIGN KEY — a guarantee, not a suggestion

paid_by BIGINT NOT NULL REFERENCES friend(id)

This is the most underrated line in the schema. It is not a hint, not documentation, not an index. It is the database promising: every value in paid_by refers to a friend row that exists — checked on every write, forever.

Try to insert an expense paid by friend 99 when no friend 99 exists, and PostgreSQL refuses:

ERROR:  insert or update on table "expense" violates foreign key constraint "expense_paid_by_fkey"
DETAIL:  Key (paid_by)=(99) is not present in table "friend".

Read it like a stack trace: what — an FK violation; which rule — expense_paid_by_fkey; the exact bad value — (paid_by)=(99). The guarantee cuts both ways, too: try to DELETE a friend who has expenses pointing at them, and the database refuses that — no orphans, in either direction.

“But my Java service already validates that the friend exists.” It does — in one app, on one code path, this year. The database gate covers every future app, every migration script, every batch job, and every intern in psql — including all the code that hasn’t been written yet by people who’ve never read your service layer. App-level checks are courtesy. The constraint is law.

flowchart LR
    A["Spring app"] --> G["The constraint gate"]
    B["Reporting script"] --> G
    C["One off psql session"] --> G
    D["Next years rewrite"] --> G
    G --> T["expense table"]

Constraints — the cheapest tests you will ever write

A unit test runs when CI runs. A constraint runs on every write, from everyone, forever, costs roughly nothing, and never rots. Three to know cold:

That CHECK deserves a pause: amount_paise > 0 is a business rule, and you just installed it at the layer that outlives the business logic. In the Spring track you’d have written a validation annotation for this; module 05 of that track still applies, but now there’s a second, deeper gate that nobody can route around.

DDL, DML, DQL — three jobs, one language

SQL statements fall into three families. Interviewers love this; more importantly, permissions and migrations are organized around it:

This module is mostly DDL plus a little INSERT. Modules 02–04 live in DQL. Module 05 returns to DDL with design judgment.

The simplified SplitEase schema — built live

Two tables today: friend and expense. No groups, no shares yet — the full five-table schema is designed, not just typed, in module 05. Here’s what you’ll create in Build This:

CREATE TABLE friend (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE expense (
  id BIGSERIAL PRIMARY KEY,
  description TEXT NOT NULL,
  amount_paise BIGINT NOT NULL CHECK (amount_paise > 0),
  paid_by BIGINT NOT NULL REFERENCES friend(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Every line is now readable: identities from sequences, exact money, an FK guarantee, three constraint-tests, and a timezone-aware timestamp that defaults to the moment of insert. Order matters, by the way — friend must exist before expense can reference it.

This is what @Entity was making

Back in Spring Boot 04, your annotations produced this in the log:

Hibernate: create table friend (id bigint generated by default as identity,
           name varchar(255) not null, primary key (id))

Same animal as your hand-written DDL, with two dialect choices you can now judge: generated by default as identity is the modern standard-SQL cousin of BIGSERIAL (both mean “a sequence feeds this column”), and varchar(255) is Hibernate playing it safe where TEXT would do. That’s the shift this track is buying: generated DDL used to be output you trusted. Now it’s a diff you review.

Check The Concept

How This Shows Up At Work

• The schema review that saves crores. A PR adds amount FLOAT to a payments table. The reviewer who’s internalized this module blocks it in one comment; the team that merges it spends a quarter reconciling one-paisa drifts and explaining them to auditors.
• The orphaned-row incident. A team relied on app-level checks instead of FKs. A cleanup script deleted inactive users; three weeks later, reports started crashing on expenses whose payer no longer existed. The fix took a day. Finding which rows were garbage took two weeks. An FK would have refused the delete in milliseconds.
• The 5:30 bug. App developed against a laptop in IST, deployed to a UTC server, every TIMESTAMP column now displays times five and a half hours off — but only for some code paths. Whole class of incident, deleted by the one-word rule: TIMESTAMPTZ.
• The interview staples. “Difference between PRIMARY KEY and UNIQUE?” (PK is identity: exactly one per table, never NULL; UNIQUE columns can be nullable and plural.) “Why not store money as float?” After Build This you answer both from scars, not flashcards.
• The code-review comment that signals seniority. “This column can’t be NULL in any valid state — add NOT NULL, don’t just validate in the service.” Constraints-as-tests is a judgment habit, and people notice who has it.

Build This

Everything below is typed in a terminal, then in psql. No Java anywhere — that’s the point.

1. Create the track database and connect (skip createdb if you did it on the overview page):

createdb -U postgres splitease_sql
psql -U postgres splitease_sql

2. Create friend first — expense will reference it, so order matters:

CREATE TABLE friend (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

3. Now expense, with every constraint from the lesson:

CREATE TABLE expense (
  id BIGSERIAL PRIMARY KEY,
  description TEXT NOT NULL,
  amount_paise BIGINT NOT NULL CHECK (amount_paise > 0),
  paid_by BIGINT NOT NULL REFERENCES friend(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

4. Inspect what you made. Run \dt (two tables), then \d expense — find the sequence default on id, the check constraint, and the FK at the bottom of the output. This readout is the database’s own description of the rules it will now enforce.

5. Seed the cast:

INSERT INTO friend (name) VALUES ('Asha'), ('Rohit'), ('Darshan'), ('Meera');

6. Add expenses — note you write paise, and you reference friends by id (Asha is 1, Rohit is 2, Meera is 4):

INSERT INTO expense (description, amount_paise, paid_by) VALUES
  ('dinner',   300000, 1),
  ('cab',       60000, 2),
  ('homestay', 840000, 1),
  ('snacks',    18000, 4);

7. Read it back:

SELECT id, description, amount_paise, paid_by, created_at FROM expense;

 id | description | amount_paise | paid_by |          created_at
----+-------------+--------------+---------+-------------------------------
  1 | dinner      |       300000 |       1 | 2026-06-12 21:14:03.118+05:30
  2 | cab         |        60000 |       2 | 2026-06-12 21:14:03.118+05:30
  3 | homestay    |       840000 |       1 | 2026-06-12 21:14:03.118+05:30
  4 | snacks      |        18000 |       4 | 2026-06-12 21:14:03.118+05:30
(4 rows)

Notice created_at arrived on its own (the DEFAULT now() fired) and carries +05:30 — that’s TIMESTAMPTZ rendering the stored instant in your session’s timezone.

Now break every rule on purpose. One at a time, read each error fully before moving on.

8. Break the UNIQUE. A second Asha:

INSERT INTO friend (name) VALUES ('Asha');

ERROR:  duplicate key value violates unique constraint "friend_name_key"
DETAIL:  Key (name)=(Asha) already exists.

The constraint has a name — friend_name_key, auto-generated as table_column_key — and the DETAIL line names the exact offending value. This is the same constraint your Spring app tripped in module 04’s break-it step, seen from below.

9. Break the CHECK. A negative expense:

INSERT INTO expense (description, amount_paise, paid_by) VALUES ('refund?', -5000, 2);

ERROR:  new row for relation "expense" violates check constraint "expense_amount_paise_check"
DETAIL:  Failing row contains (5, refund?, -5000, 2, 2026-06-12 21:16:40.55+05:30).

Your business rule, enforced by the storage layer. The DETAIL shows the whole refused row — including the id 5 it would have had. Remember that number.

10. Break the FOREIGN KEY. An expense paid by nobody:

INSERT INTO expense (description, amount_paise, paid_by) VALUES ('ghost dinner', 100000, 99);

ERROR:  insert or update on table "expense" violates foreign key constraint "expense_paid_by_fkey"
DETAIL:  Key (paid_by)=(99) is not present in table "friend".

There is no friend 99, so there is no expense paid by friend 99 — not from psql, not from Java, not from anyone.

11. Break the NOT NULL. An expense with no description:

INSERT INTO expense (amount_paise, paid_by) VALUES (25000, 3);

ERROR:  null value in column "description" of relation "expense" violates not-null constraint
DETAIL:  Failing row contains (7, null, 25000, 3, 2026-06-12 21:18:02.31+05:30).

12. Now fix it — insert the valid version and look closely at the id it gets:

INSERT INTO expense (description, amount_paise, paid_by) VALUES ('chai', 25000, 3)
RETURNING id, description;

The id is **8**, not 5. Three failed inserts each consumed a sequence number. Run `SELECT count(*) FROM expense;` — still 5 rows, ids 1–4 and 8. Gaps are the scar tissue of refused writes, and your data is intact: four breaks attempted, zero garbage stored. That's the constraints doing the job your unit tests can't — at the gate everyone passes through.

Where to Practice

Check Yourself

1. In one sentence each: how does a table differ from a Java class, and from a JSON file?
2. Why paise in BIGINT instead of rupees in FLOAT8 — and what’s the matching rule from Core Java?
3. TIMESTAMP vs TIMESTAMPTZ: which do you use, and what goes wrong with the other?
4. What does NULL mean, and what does NULL = NULL evaluate to?
5. BIGSERIAL is shorthand for what three things?
6. State the foreign key guarantee in one sentence — including who it applies to.
7. Your service layer validates that amount > 0. Why add CHECK (amount_paise > 0) anyway?
8. Sort into DDL / DML / DQL: ALTER, INSERT, SELECT, DROP, UPDATE.

Explain it out loud: Explain to an empty chair why the database refused your negative expense and your ghost dinner — naming the constraint type each time — and why those two refusals would still happen even if SplitEase were rewritten in Go next year. If you can’t make the “every writer passes one gate” argument land, reread the foreign key section.

Still Unclear?

I created a PostgreSQL table with BIGSERIAL PRIMARY KEY and my ids have gaps:
1, 2, 4, 8. Explain how sequences work, every way a gap can appear, and why
gapless ids are the wrong thing to want. Then quiz me: give me three scenarios
and ask whether each produces a gap.

Explain the difference between enforcing a rule like amount > 0 in my Spring
service versus a CHECK constraint in PostgreSQL. When do I need both, when is
one enough, and what failure modes does each one miss? Use a concrete story
for each failure mode, not abstractions.

I know Java well. Map relational concepts onto Java for me — table, row,
column type, primary key, foreign key — then tell me where the mapping LIES
and a Java instinct would mislead me about databases. Quiz me on the lies.

Why AI Can’t Do This For You

AI will generate flawless CREATE TABLE statements all day — typing DDL was never the skill. The skill is the judgment baked into each line: knowing FLOAT money is a quarter of reconciliation pain, that a missing FK is an orphaned-row incident waiting for a cleanup script, that plain TIMESTAMP is a 5:30 bug shipped to production. AI proposes; the reviewer who’s been refused by these constraints personally is the one who catches what’s wrong for this schema, this data, this decade of life it will live.

And when the error fires at work — violates foreign key constraint in a log at 11pm — AI doesn’t know your tables or which script just ran. You’ve now read all four refusals slowly, on purpose, with nothing at stake. That’s exactly the rehearsal that makes the real one a two-minute diagnosis instead of a panic.

Module done? Add it to today’s tracker