Postgres in depth · Chapter 12 of 65
The spreadsheet that kept lying
By the end of this chapter you can spot the anomalies a flat table causes, split it into tables by the normal forms, and say when breaking the rules on purpose is worth it. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Designing LCO properly
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
Priya loaded LCO's old orders spreadsheet into one table, old_sheet, exactly as it was: one row per course bought, with the student, the course, the price and the instructor typed into every row. Arjun asks a simple question: what does React from Zero cost? The sheet gives two answers. Where does Aarav Shah live? Two answers again. And Pune, it says, is in Karnataka.
Nobody lied on purpose. The sheet stores the same fact many times, and every copy is a chance to get it wrong. Chapter 11 split instructors out of courses by instinct; this chapter gives the instinct its rules, so you can check any design, including the orders LCO needs next.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
the-lie.sql: how many ways to spell Neha
In old_sheet, every row of Neha Kapoor's courses has her email, neha.kapoor@lco.dev, and her name typed again. Return each different spelling of the name on those rows, once.
Return one column: instructor_name. Order by instructor_name.
- Task 2
new-email.sql: Neha changes her email
Neha's email changes to neha@lco.dev. Update old_sheet so that every row with her old email, neha.kapoor@lco.dev, has the new one. No other row changes.
- Task 3
orders.sql: orders and their items
Design LCO's orders in second normal form. Create orders: id (bigint identity by default, primary key), student_id (bigint, required, a student's id), coupon_id (bigint, optional, a coupon's id), status (text, required, default 'pending', only 'pending', 'paid', 'failed' or 'refunded'), total (numeric(10, 2), required), created_at (timestamptz, required), paid_at (timestamptz, optional).
Create order_items, one row per course in an order: order_id (bigint, required, an order's id), course_id (bigint, required, a course's id) and price (numeric(10, 2), required: what was paid for that course in that order). A course appears in an order at most once (the pair is the primary key), and deleting an order deletes its items. No course title or instructor in order_items: those are facts about the course.
- Task 4 · interview question
Interview: cities.sql, where a city's state belongs
The sheet repeats a student's state next to the city, and once says Pune is in Karnataka. In third normal form the state depends on the city, so it belongs to a table of cities.
Create cities: name (text, the primary key: a city's name is its natural key), state (text, required), lat and lon (double precision, both required: where the city is, for the meetups feature).
What you will learn
- Anomalies: what goes wrong when one fact lives in many rows.
- First normal form: one value per cell.
- Second and third normal form: every fact depends on the key, the whole key, and nothing but the key.
- Deliberate denormalisation: when a copy is the right design.
- The ER diagram: LCO's tables and their links on one page.
More in Designing LCO properly
Build The spreadsheet that kept lying yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.