Learn database design by building a warehouse
Start from one messy registrar export. Design the tables, keys and relationships that should have existed, on a drag-and-drop canvas or in SQL, and get every step reviewed against the tables you actually created. Real PostgreSQL, real student data, no installation.
Most database design tutorials hand you a finished diagram and explain why it is right. You nod, close the tab, and the next time you sit in front of an empty schema you have no idea where to begin. Build the Warehouse works the other way round. You are given the worst possible starting point, a single flat file with every fact about every student repeated on every row, and you fix it one table at a time.
The data is the same university records dataset that powers QueryU's SQL challenges, so the design you produce is not a toy. The registrar export is real rows from the practice schema flattened back into a spreadsheet shape, with the name and address collapsed into single cells, dates and credits stored as text, a numbered pair of major columns, and a handful of duplicate rows. Every one of those is a problem you will meet in a real data job, and every one of them is a step in the course.
What you start from
One table, raw_registrar_export, one row per student per course. Here is one row, column by column, with the fault each column carries. The first design step has you run a SELECT against it before you draw anything.
| Column | Value on this row | What is wrong with it |
|---|---|---|
| student_name | RIVERA, Dana | two facts in one cell, some rows in caps |
| student_email | [email protected] | trailing whitespace on some rows |
| home_address | 14 Elm St, Springfield, IL 62704 | street, city, state and zip in one column |
| date_of_birth | 03/14/2004 | a date stored as text |
| term_name | Spring 2026 | a label, never a code |
| course | BIOL-101 Intro to Biology | code and title together |
| credits | 3.0 | '3' on one row, '3.0' on the next |
| grade | B+ | the one fact that belongs on this row |
| instructor_name | P. Okonkwo | a section fact, not a course fact |
| major_1 | Biology | a repeating group |
| major_2 | NULL | caps a student at two majors forever |
An illustrative row. The real export also carries the student's department, advisor name and email, and a cumulative GPA that is sometimes the string "N/A" — all repeated on every row for that student.
What that row becomes after chapter 1
Four tables, each about one subject, each with a real primary key. The student's name is two columns. The date is a date. The course code and title are separated, credits are a number, and the enrollment row keeps only the grade plus three foreign keys. These are the reference solutions for the four chapter-1 steps; the review accepts any design that passes the same checks, whatever it looks like on the canvas.
CREATE TABLE students ( student_id integer, first_name text NOT NULL, last_name text NOT NULL, email text, date_of_birth date, PRIMARY KEY (student_id) );
CREATE TABLE terms ( term_code text, term_name text NOT NULL, start_date date NOT NULL, end_date date NOT NULL, PRIMARY KEY (term_code) );
CREATE TABLE courses ( course_code text, title text NOT NULL, credits numeric(4,2) NOT NULL, PRIMARY KEY (course_code) );
CREATE TABLE enrollments ( student_id integer, course_code text, term_code text, grade text, PRIMARY KEY (student_id, course_code, term_code), FOREIGN KEY (student_id) REFERENCES students (student_id), FOREIGN KEY (course_code) REFERENCES courses (course_code), FOREIGN KEY (term_code) REFERENCES terms (term_code) );
Why enrollments has no course title: the title depends on course_code alone, not on the whole three-column key. That is the second-normal-form rule, and the fourth step checks for it by name.
How a design step is reviewed
Each step gives you a rail of information items from the export and a suggested table name. Drag the items you want onto a table card, set their types, mark the key, and drag a column's link handle onto another table to draw a foreign key. Every step also plants an item you should not take, such as student_name or course_title, so the review has something to teach. If you would rather type, switch to SQL mode and write the CREATE TABLE yourself; the canvas redraws from what you wrote.
When you submit, the server builds your tables as temporary objects in a private session, replays the steps you already passed so earlier tables exist for foreign keys to point at, and runs the step's assertions against the catalog. The assertions look at what exists, not at your text: a primary key on exactly these columns, a foreign key to that table, a column whose type is a date, no two columns sharing a stem with a numeric suffix. Each check passes or fails on its own, failures carry a hint, and the whole run is rolled back either way. Pass every check and the headline reads Design approved; the accepted SQL is saved and your tables show up locked on the canvas at the next step.
Five of the checks from chapter 1, with the hint you see on a failure
student_id is the primary key of students
Add a student_id column and mark it as the key so it becomes PRIMARY KEY (student_id).
No combined student_name column
Remove student_name — the export's 'Last, First' cell is two facts; keep only first_name and last_name.
No repeating group (major_1 / major_2)
Numbered columns that differ only by a suffix are a repeating group. Leave the majors out; they get their own table later.
The primary key is (student_id, course_code, term_code)
Mark all three foreign-key columns as the key — together they identify one enrollment.
No course_title here (it depends on course_code alone)
Remove course_title. It depends on only part of the key, which breaks second normal form; it lives on courses.
Where the track goes
Chapters 1 and 2 are live, and chapters 3 to 9 are the plan the track is growing toward: from subject tables, through keys, constraints and normal forms, to a star schema with a loaded fact table and a dashboard query running on top of it. Every step adds to the same design, so the enrollments table you build in chapter 1 is the one the fact table is derived from at the end.
- Chapter 1
From Index Cards to Tables
LiveFind the subjects in the export, give each its own table with atomic columns and a real primary key, then connect them.
- Find the subject: a students table
- Terms are a subject too
- Courses: split the code from the title
- The junction: enrollments
- Chapter 2
Sections, people and the one-to-one
LiveA sections table so the instructor becomes a section fact, a shared people table with students as its one-to-one supplement, and addresses split out of the one-cell string with effective dates.
- Sections: where the instructor really lives
- People, and the thin students table
- Addresses: one cell becomes a table
- Chapter 3
Programs and advisors
PlannedThe majors repeating group becomes a junction table with a declared-on date; advisor assignments get start and end dates.
- Chapter 4
Keys and constraints
PlannedSurrogate versus natural keys, NOT NULL, and CHECK constraints that stop bad data at the door.
- Chapter 5
Refine with data
PlannedLoad the export into your tables — split the name, cast the date, dedupe — and let the row counts tell you whether the design holds.
- Chapter 6
Normal forms
PlannedPlanted 1NF, 2NF and 3NF violations to find and fix in tables you are handed, not tables you built.
- Chapter 7
The dimensional model
PlannedGrain statements, dimension and fact tables, a conformed term dimension that sorts correctly, and a type-2 slowly changing dimension.
- Chapter 8
Load and quality
PlannedStaging to mart, orphan checks, incremental loads and audit columns.
- Chapter 9
Serve
PlannedViews and a refreshable aggregate for a retention dashboard, then the analyst-track queries re-run against your own star schema.
Who it is for
Analysts moving toward data engineering
You can query a warehouse; this is where you learn to design one. The track ends at the dimensional model and the load, which is the half of the job that querying never taught you.
Students in a database course
Subject tables, keys, relationships and normal forms are the syllabus. Here each one is a step with a check that tells you exactly which rule your design broke, instead of a red mark on a diagram.
Developers who inherited a schema
If you have ever wondered why a table has course_title on every row or why a phone1 / phone2 pair keeps growing, chapter 1 and the normal-forms chapter show you the mistake being made, and then the fix.
Frequently asked questions
Do I need to know SQL before starting?
Not much. Every design step but one can be completed entirely on the drag-and-drop canvas, and the review reads the tables you created rather than the text you typed. The exception is one line in chapter 2: the canvas cannot draw a UNIQUE constraint yet, so that step has you switch to SQL mode and type ALTER TABLE students ADD UNIQUE (person_id); — the step shows you the line. If you prefer to write CREATE TABLE statements by hand there is a SQL mode, and the canvas rebuilds itself from whatever you wrote. The only SQL you need to read is a SELECT that peeks at the flat export.
Is the database design track free?
Yes, right now. Chapters 1 and 2 are live and free for anyone with a QueryU account, and chapter 3 is planned to stay free. Later chapters — keys and constraints, normal forms, the dimensional model, load and quality, and the capstone — are slated for Pro ($9 per month or $79 per year, or $5 per month / $44 per year on the student tier). The page will say so when that changes.
How is a design step graded?
By assertions that run on the server against the tables you actually created, not by comparing your DDL to a reference script. Each step registers a short list of checks — a students table exists, student_id is its primary key, date_of_birth is a date, there is no major_1 / major_2 repeating group — and each one passes or fails independently with a hint attached to the failures. Column order, formatting and whether you used the canvas or wrote SQL make no difference.
What happens to the tables I build?
They are created as temporary objects in a private session, checked, and thrown away. What persists is the accepted SQL for each step you pass, and it is replayed at the start of the next step so your earlier tables appear locked on the canvas and later tables can reference them with real foreign keys. Nothing you create is visible to anyone else, and there is nothing to reset.
Build the students table now
Chapters 1 and 2 are seven steps and free with a QueryU account. No installation: the canvas runs in your browser tab and the database behind it is already set up.