Data Modeling

Your Spring code will be rewritten — different framework, different language, maybe within two years. The schema and the data inside it will outlive all of that by a decade, and every app that comes later inherits its decisions. This module is where you learn to make those decisions on purpose — it is the single most AI-proof skill in this track, because a model is a claim about what is true in your domain, and only someone who understands the domain can make that claim.

The Goal

By the end of this module you can:

• Name the three cardinalities and how each becomes tables — including why M:N has exactly one honest answer
• Design the full SplitEase schema from plain requirements, defending every decision out loud
• Spot update anomalies in a denormalized table and state the “one fact, one place” rule without jargon
• Choose surrogate vs natural keys, and RESTRICT vs CASCADE, knowing what each costs
• Say which rules a schema can enforce and which ones the service layer must own
• Map every table in this schema to the JPA entities you already wrote

The Lesson

A schema is a list of claims about the world

Before any CREATE TABLE, a model is sentences: a friend has a name; friends form groups; an expense happens inside a group, paid by one friend; an expense is split among specific friends, each owing a specific share. Modeling is deciding which sentences are TRUE in your domain and encoding them so the database itself refuses lies. Get a sentence wrong — “an expense has one sharer” — and no amount of clean Java later un-wrongs it.

Every sentence connects two entities (the nouns that get tables) with a cardinality:

That last row is the one people fight. There is no “list column” in the relational model — and the workarounds (a CSV string of ids, an array column) all die the same death: you can’t JOIN to a string, can’t FK-check it (a typo’d id is silent corruption), can’t index into it, can’t SUM across it. Every query you learned in modules 02–04 stops working. M:N means a junction table. Every time.

Designing SplitEase, decision by decision

The requirements, in plain sentences:

1. People use the app; each person has a name.
2. People form groups (“Goa Trip”, “Flat 402”). A person can be in many groups; a group holds many people.
3. An expense happens inside one group: one payer, one amount, a description, a timestamp.
4. An expense is split among specific people, each owing a specific share.

Requirement 1 → friend. One entity, one table. But what’s the key? name is unique — so why this:

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

Because keys must never change, and names do — Asha gets married next section and you’ll watch why. A surrogate key (BIGSERIAL — an auto-assigned number with no real-world meaning) stays stable forever while every foreign key in the system points at it. The natural key (name) still gets its UNIQUE constraint — the business rule is enforced — it just doesn’t carry the relationships. This is also exactly what @GeneratedValue was doing in Spring Boot 04: Hibernate assumed a surrogate key because experienced modelers almost always want one.

Requirement 2, first attempt → a syntax error. Groups are an entity:

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

ERROR:  syntax error at or near "group"
LINE 1: CREATE TABLE group (id BIGSERIAL PRIMARY KEY, name TEXT NOT...
                     ^

GROUP is a reserved word — you used it in GROUP BY all through module 04. You could write "group" in double quotes and then be condemned to quoting it in every query for the rest of the schema’s (long) life. The honest fix is a better name:

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

(No UNIQUE on name this time — two flats can both create “Goa Trip”. A modeling decision, not an oversight: uniqueness is a claim about the world, and this one would be false.)

Requirement 2, the M:N part → group_member.

CREATE TABLE group_member (
  group_id  BIGINT NOT NULL REFERENCES expense_group(id),
  friend_id BIGINT NOT NULL REFERENCES friend(id),
  PRIMARY KEY (group_id, friend_id)
);

Each row is one membership fact: “friend 3 is in group 1.” Notice what’s missing: no id column. The pair is the identity — a junction table IS its two foreign keys — and making the pair the primary key buys a real rule for free: the same friend cannot be added to the same group twice. A surrogate id here would actually weaken the table (duplicate memberships would become legal) unless you added a unique constraint on the pair anyway. When the key writes a business rule, use it.

Requirement 3 → expense.

CREATE TABLE expense (
  id BIGSERIAL PRIMARY KEY,
  group_id BIGINT NOT NULL REFERENCES expense_group(id),
  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()
);

Three decisions worth defending. group_id and paid_by are the two 1:N sentences (“inside one group”, “one payer”) — each a plain FK on the many side. amount_paise BIGINT is the track’s money rule made structural: paise as integers, never floating point, and CHECK (amount_paise > 0) makes a zero or negative expense unrepresentable — the database rejects it before any Java validation gets a vote. TIMESTAMPTZ (not TIMESTAMP) stores an absolute instant; the zone-less version is a famous source of “every time moved by 5:30” bugs.

Requirement 4 → expense_share, the decision that separates modelers from typists. The tempting shortcut is a column on expense: shares TEXT holding 'Asha:100000,Rohit:100000,Darshan:100000'. One column, no extra table, done — and module 04’s settlement report becomes impossible: you cannot SUM(share_paise) GROUP BY friend_id over a string, cannot FK-check that “Rohti” isn’t a typo, cannot index who owes what. The relationship between an expense and a sharer carries data (the share amount) — and a relationship with its own data is a table:

CREATE TABLE expense_share (
  expense_id BIGINT NOT NULL REFERENCES expense(id),
  friend_id  BIGINT NOT NULL REFERENCES friend(id),
  share_paise BIGINT NOT NULL,
  PRIMARY KEY (expense_id, friend_id)
);

Same junction pattern as group_member, plus the payload column. This table is also a JPA lesson you’ve already half-met: the capstone’s @ManyToMany Set<Friend> sharers generated a bare join table — two FK columns, nowhere to put share_paise. The moment an M:N relationship grows its own data, JPA forces you to promote the join table to a full entity. The schema knew first.

The finished model:

flowchart TD
    F["friend  id PK  name UNIQUE"]
    EG["expense_group  id PK  name"]
    GM["group_member  PK is group_id plus friend_id"]
    E["expense  id PK  amount_paise CHECK  created_at"]
    ES["expense_share  PK is expense_id plus friend_id  share_paise"]
    EG ---|"1 to N"| GM
    F ---|"1 to N"| GM
    EG ---|"1 to N"| E
    F ---|"1 to N as paid_by"| E
    E ---|"1 to N"| ES
    F ---|"1 to N"| ES

Five tables, two of them junctions. Every sentence from the requirements is now a structure the database enforces — mostly. (One sentence slipped through; Build This catches it.)

Normalization without the jargon: one fact, one place

Why all these joins instead of one wide table? Here’s the strawman a beginner (or a hurried senior) builds:

CREATE TABLE expense_flat (
  id BIGSERIAL PRIMARY KEY,
  group_name TEXT NOT NULL,
  description TEXT NOT NULL,
  amount_paise BIGINT NOT NULL,
  payer_name TEXT NOT NULL          -- the friend's name, copied into every row
);

No joins needed, every query is a single-table SELECT. Now Asha gets married and becomes Asha Rao. In the real schema that’s:

UPDATE friend SET name = 'Asha Rao' WHERE id = 1;   -- UPDATE 1

One row. Every join everywhere instantly sees the new name, because the fact “friend 1 is called Asha Rao” lives in exactly one place. In expense_flat, her name was copied into every expense she ever paid — two years of Goa trips and flat bills, thousands of rows — and you must update all of them, in every table that copied it, from every code path that writes names. Miss one (the old CSV-export job still inserting 'Asha') and your database now tells two different truths about the same person, with no error and no way to know which is right. That’s an update anomaly, and its siblings come free: you can’t store Meera until she pays for something (insert anomaly), and deleting a group’s last expense erases the group’s existence (delete anomaly).

The cure is the rule you just watched work: every fact is stored exactly once, and everything else points at it. That instinct has formal names — this design is roughly third normal form — but interviews and code reviews care about the instinct, not the recitation.

Denormalizing on purpose is still a real tool: a read-heavy dashboard might add expense_group.total_paise so the group screen skips a SUM over ten thousand shares. Fine — but say the price out loud: that column is a copy of a derivable fact, and you now own keeping it in sync on every insert, update, and delete of expense, forever, including the code paths written by people who’ve never heard of your column. Deliberate denormalization is a measured trade made after profiling. Accidental denormalization is just the strawman with better marketing.

Referential actions: what happens to the pointers

Every REFERENCES above used the default action — delete a friend who has expenses and PostgreSQL refuses (NO ACTION, which for everyday purposes behaves like RESTRICT). You could choose otherwise:

CASCADE on money tables should make your skin crawl: one DELETE FROM friend WHERE … in a cleanup script and expense history vanishes — no error, no log line, settlement totals silently wrong, auditors asking where the records went. The default’s annoying error message is the feature: it forces a human to look at the dependency before destroying it.

Schemas change — that’s a discipline, not an event

This module designs the schema once, but real schemas evolve for years: add settled_at, split a column, backfill data. The grown-up mechanism is migrations — versioned SQL files (V7__add_settled_at.sql), reviewed like code, applied in order, never edited after they ship. You’ve already met the full story in migrations: basics to full, and you’ve seen the amateur alternative: Spring’s ddl-auto=update, which only ever adds and never renames, drops, or migrates — fine for this track, a fireable offence against production data. Design carefully and expect change: both are true.

One more connection to lock in: this schema is your JPA layer’s ground truth. friend is the @Entity Friend; group_member is what @ManyToMany between Group and Friend generated in the capstone; expense.paid_by is @ManyToOne Friend payer. When Spring Boot 04 showed you Hibernate’s DDL in the log, this is the artifact it was generating. Now you can judge its output instead of trusting it.

Check The Concept

How This Shows Up At Work

• The schema review is the most expensive review you’ll ever attend. A bad function ships Friday and is reverted Monday; a bad column ships and is still being worked around in five years, because renaming it under 50 million rows needs a multi-step migration with backfill. Teams put their most senior people on schema PRs for exactly this reason.
• The CSV column you will inherit. Somewhere in your future is a legacy table with tag_ids TEXT holding '3,17,42' — and every feature touching it costs triple because nothing joins, nothing validates, nothing indexes. You’ll be the one writing the junction-table migration to fix it.
• The CASCADE incident. A support engineer deletes a “test account”; CASCADE quietly takes the account’s orders with it; finance notices at month-end reconciliation. Postmortems for this exact pattern exist at many companies — the fix is always the same: RESTRICT on anything money-adjacent.
• The interview that is literally this module. “Design Splitwise” is a real, frequently-asked design question in Indian product-company interviews. The expected whiteboard answer is this schema: entities, the two junction tables, paise as integers, and why at every step. You now arrive with it pre-fought.
• The JPA debugging session. A teammate can’t see why @ManyToMany won’t hold the share amount. You explain it in one sentence — a relationship with data is an entity — because you designed the table underneath it.

Build This

You’re rebuilding splitease_sql from nothing with the full canonical schema, seeding the whole cast, proving the constraints work — and then finding the one rule the schema cannot enforce. Type everything.

1. Connect to the maintenance database and recreate from zero (you can’t drop a database you’re inside):

psql -U postgres
DROP DATABASE IF EXISTS splitease_sql;
CREATE DATABASE splitease_sql;
\c splitease_sql

2. Type the full canonical DDL — every module after this one uses these exact tables:

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

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

CREATE TABLE group_member (
  group_id  BIGINT NOT NULL REFERENCES expense_group(id),
  friend_id BIGINT NOT NULL REFERENCES friend(id),
  PRIMARY KEY (group_id, friend_id)
);

CREATE TABLE expense (
  id BIGSERIAL PRIMARY KEY,
  group_id BIGINT NOT NULL REFERENCES expense_group(id),
  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()
);

CREATE TABLE expense_share (
  expense_id BIGINT NOT NULL REFERENCES expense(id),
  friend_id  BIGINT NOT NULL REFERENCES friend(id),
  share_paise BIGINT NOT NULL,
  PRIMARY KEY (expense_id, friend_id)
);

3. Seed the whole cast. Fresh database, so the BIGSERIAL ids are predictable — friends 1–4, groups 1–2 in insert order (in a live system you’d never hardcode ids; here it keeps the FKs readable):

INSERT INTO friend (name) VALUES ('Asha'), ('Rohit'), ('Darshan'), ('Meera');
INSERT INTO expense_group (name) VALUES ('Goa Trip'), ('Flat 402');

INSERT INTO group_member VALUES (1, 1), (1, 2), (1, 3),   -- Goa Trip: Asha, Rohit, Darshan
                                (2, 3), (2, 4);           -- Flat 402: Darshan, Meera

INSERT INTO expense (group_id, description, amount_paise, paid_by, created_at) VALUES
  (1, 'dinner',    300000, 1, '2026-06-01'),
  (1, 'cab',        60000, 2, '2026-06-02'),
  (1, 'homestay',  840000, 1, '2026-06-03'),
  (2, 'groceries', 120000, 4, '2026-06-04'),
  (2, 'wifi',       80000, 3, '2026-06-05');

INSERT INTO expense_share VALUES
  (1, 1, 100000), (1, 2, 100000), (1, 3, 100000),
  (2, 1,  30000), (2, 2,  30000),
  (3, 1, 280000), (3, 2, 280000), (3, 3, 280000),
  (4, 3,  60000), (4, 4,  60000),
  (5, 3,  40000), (5, 4,  40000);

4. Sanity query 1 — the rosters. Two junction hops, reading memberships back as names:

SELECT g.name AS group_name, f.name AS member
FROM expense_group g
JOIN group_member gm ON gm.group_id = g.id
JOIN friend f ON f.id = gm.friend_id
ORDER BY g.name, f.name;

 group_name |  member
------------+---------
 Flat 402   | Darshan
 Flat 402   | Meera
 Goa Trip   | Asha
 Goa Trip   | Darshan
 Goa Trip   | Rohit

5. Sanity query 2 — every split adds up. The shares of each expense must sum to its amount (module 04’s fold, now doing integrity duty):

SELECT e.description, e.amount_paise, SUM(s.share_paise) AS shares_paise
FROM expense e
JOIN expense_share s ON s.expense_id = e.id
GROUP BY e.id, e.description, e.amount_paise
ORDER BY e.id;

All five rows must show amount_paise = shares_paise. If any don’t, your seed has a typo — find it with this query, not by re-reading the INSERTs.

6. Sanity query 3 — spend per group. Goa Trip 1200000, Flat 402 200000. (Notice the query is safe from module 04’s fan-out trap — one join, one 1:N. Say why before moving on.)

7. Break it on purpose — RESTRICT saves the money. Asha leaves the app:

DELETE FROM friend WHERE name = 'Asha';

ERROR:  update or delete on table "friend" violates foreign key constraint
        "expense_paid_by_fkey" on table "expense"
DETAIL:  Key (id)=(1) is still referenced from table "expense".

Read it the way you read stack traces: which table refused, which constraint, which key. (Asha is referenced from three tables; PostgreSQL names whichever it checked first.) This error is the schema doing its job — her payment history is load-bearing, and “deleting a user” in a money app is a product decision (anonymize? deactivate flag?), not a DELETE. Be glad it’s hard.

8. Break it on purpose — the rule the schema cannot see. Expense 5 (wifi) belongs to Flat 402. Asha is not in Flat 402. Give her a share of it anyway:

INSERT INTO expense_share (expense_id, friend_id, share_paise) VALUES (5, 1, 99999);

INSERT 0 1

It worked. Both FKs are individually valid — expense 5 exists, friend 1 exists — but the sentence “a sharer must be a member of the expense’s group” spans three tables, and plain constraints can’t see that far. (A trigger could, but business logic hidden in triggers is where debugging careers go to die — most teams refuse.) This is the honest boundary of the schema: constraints make structural lies unrepresentable; business rules that cross tables belong to the service layer — exactly the validation-inside-a-transaction work from Spring Boot 05. Know which guard owns which rule. Clean up: DELETE FROM expense_share WHERE expense_id = 5 AND friend_id = 1;

9. Break it on purpose — the pair-key earns its keep. Add Asha to Goa Trip a second time: INSERT INTO group_member VALUES (1, 1); → duplicate key value violates unique constraint "group_member_pkey". That’s the composite primary key from the lesson, enforcing a business rule with zero extra code. Leave the database exactly as seeded — modules 06 onward use it.

Where to Practice

Check Yourself

1. The three cardinalities — one example sentence and the storage pattern for each.
2. Why is a CSV/array column a dishonest answer to M:N? Name two concrete queries it kills.
3. friend.name is UNIQUE — defend the surrogate id anyway, using Asha’s wedding.
4. What does PRIMARY KEY (group_id, friend_id) enforce that a surrogate id wouldn’t?
5. Define an update anomaly using the payer_name-copied-everywhere strawman.
6. When is denormalizing legitimate, and what exactly do you sign up to own?
7. Why is ON DELETE CASCADE terrifying on expense.paid_by, and what does the default do instead?
8. Name one true business rule this schema cannot enforce, and say who enforces it.

Explain it out loud: Whiteboard (or empty chair) the full SplitEase schema from the four requirement sentences — every table, every key, every FK — defending each decision: why expense_group isn’t group, why group_member has no id, why shares aren’t a column on expense, why paid_by won’t cascade. That talk-through IS the “design Splitwise” interview. If any decision comes out as “that’s just how it’s done,” reread its section — “why” is the entire skill.

Still Unclear?

Give me four plain-sentence requirements for a hostel mess-bill-splitting app
(different domain, same shape as SplitEase). I will design the schema myself.
Then challenge every decision I made — keys, cardinalities, constraints — and
make me defend or fix each one. Do not show me your schema until I commit to mine.

Argue both sides of natural vs surrogate keys for a table of Indian bank IFSC
codes, then for a users table. Where does each argument break? Then quiz me on
three more tables and grade my key choices.

Here is a deliberately denormalized table: [describe one wide table from your
own past project]. Walk me through finding its update, insert, and delete
anomalies, then make ME write the normalized version and the migration path
from one to the other.

Why AI Can’t Do This For You

Ask AI for “a schema for an expense-splitting app” and you’ll get something plausible in four seconds — and plausible is exactly the danger, because schemas fail slowly. The CSV column works fine in the demo; the missing CHECK lets one negative amount in during month three; the CASCADE eats its first expense history in year two. By the time a modeling mistake hurts, it’s load-bearing under millions of rows, and the fix is a migration project, not a prompt. The judgment being trained here — which sentences are true in this domain, and what does each shortcut cost later — requires knowing the domain, the read patterns, and the team. AI has none of those.

And this is the skill hierarchy inverting: AI now types DDL faster than you ever will, which makes the person who can decide what the DDL should say more valuable, not less. Code is becoming cheap; correct models of reality are not. The database outlives the app — and the modeler outlives the typist.

Module done? Add it to today’s tracker