Harvard's CS50 SQL: seven weeks on table design before you touch customer data

Harvard offers a free course that teaches exactly one language. The landing page for CS50’s Introduction to Databases with SQL, usually shortened to CS50 SQL, says plainly that the course is devoted entirely to SQL.

You do not need to be a Harvard student to take it. All the lectures are on OpenCourseWare, and a certificate through edX is a separate option if you want one.

For aspiring FDEs, this is not a course to take out of curiosity. Projects at a client site often begin with a database you did not write and a spreadsheet nobody remembers creating. If the table design is wrong, everything built on top of it, from dashboards to agents, is wrong too.

Two instructors, seven weeks, one language

The course is taught by David J. Malan and Carter Zenke. Harvard Online also lists Zenke as a Preceptor at Harvard Extension School. It runs for seven weeks, numbered Week 0 to Week 6: Querying, Relating, Designing, Writing, Viewing, Optimizing and Scaling.

The order makes sense for learners. You need to be able to read data and understand how tables connect before you can design your own, write data, automate and optimise.

The course uses SQLite first because it is compact and portable, and introduces PostgreSQL and MySQL only at the end. Exercises use real data rather than toy tables.

CS50 SQL is also different from CS50x. CS50x covers computer science broadly, with C, Python, SQL and JavaScript, whereas CS50 SQL goes deep on SQL alone. You can take both at the same time or just CS50 SQL.

One company counted as two: the lesson of Week 2

The most valuable material is in Week 2, Designing. The course teaches you to model real-world entities and relationships as tables, choose appropriate data types, and normalise to remove duplication and reduce the chance of errors. The lecture notes call this splitting of data normalizing, and use ER diagrams to draw the relationships.

Imagine a logistics client sends you an orders file in which each row records the name of the buying company. Across 1,000 rows, the same customer might appear as “Công ty ABC” in one place and “Cty ABC” in another (both mean “ABC Company” in Vietnamese). A revenue-by-customer report then splits one company into two, and no query can rescue it.

Normalisation fixes exactly this. You create a customers table with id as the primary key, and the orders table stores only customer_id. The Week 2 lecture sets out two constraints worth remembering: a primary key must be unique, and a foreign key value must exist in the primary key column of the related table.

There is one step you cannot skip: a foreign key does not merge “Công ty ABC” and “Cty ABC” on its own. Before loading the data, you still have to clean up the name variants, consolidate them into a single row in customers, and map each existing order to that id.

After the clean-up, a minimal version in SQLite might look like this:

PRAGMA foreign_keys = ON;

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  created_at TEXT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO customers (id, name) VALUES (1, 'Công ty ABC');
INSERT INTO orders (customer_id, created_at) VALUES (1, '2026-10-01');  -- hợp lệ
INSERT INTO orders (customer_id, created_at) VALUES (99, '2026-10-01'); -- bị từ chối

The first order insert is valid; the last line points to customer 99, which does not exist, so the database rejects it instead of letting the error reach a report. The company name now lives in one place, and correcting it once fixes every order.

When you start a new project, the first thing to do is draw an ER diagram of the client’s database. If you find a table that holds both customer names and order details, that is where you need to ask questions before writing a line of code.

Views handle business logic, indexes handle speed

Weeks 4 and 5, Viewing and Optimizing, teach two tools that newcomers often confuse. Views automate queries you run repeatedly; indexes make queries run faster.

At a client site, the difference determines what you need to build. When the operations team asks every day “which orders are more than three days late?”, write a view so everyone reuses the same definition. When that same question runs slowly on a large table, you need an index.

A view keeps the whole team on one definition; an index only makes the query return faster. The course also covers connecting SQL to Python and Java, which is how application code works with a database in practice.

Two mistakes newcomers make

The first is forgetting PRAGMA foreign_keys = ON when working with SQLite. Without it, SQLite does not enforce foreign keys, so the INSERT pointing to customer 99 above would slip through silently. The advice: turn it on for every connection, then deliberately insert a bad row to confirm the constraint is actually working.

The second is seeing that an index speeds up a query and then indexing every column. Each index has to be updated on every write and takes up extra storage. The safer approach is to index only the columns that genuinely appear in the WHERE or JOIN clauses of slow queries, and to measure before and after.

From SQLite to a database server, and the SQL injection trap

Week 6, Scaling, makes a point that many demos skip: SQLite is an embedded database, while MySQL and PostgreSQL are database servers. That week’s lecture also covers replication, which you will run into as soon as a client’s system has many users.

Also in Week 6, the course teaches prepared statements in MySQL to block SQL injection. For FDEs, this is a mandatory skill. If you build a tool that takes user input and concatenates it straight into SQL that runs against a client’s real data, one malicious input is enough to expose an entire table.

Instead of concatenating strings, use ? as a placeholder in the statement and pass the value in afterwards:

PREPARE find_orders FROM
  'SELECT id, created_at FROM orders WHERE customer_id = ?';
SET @cid = 1;
EXECUTE find_orders USING @cid;
DEALLOCATE PREPARE find_orders;

The value of @cid is treated only as data and never becomes part of the statement. That is the first thing to check when reviewing any code that touches a client’s database.

What order to study in, and what to put on your CV

If you have been writing SQL for a few years, you can skim Weeks 0 and 1 and spend most of your time on Weeks 2 and 6, the two closest to work at a client site: designing tables and taking a database into a real environment.

If you have only ever used an ORM, take all seven weeks in order, because each week builds on the one before.

When you finish, put the results on your CV rather than simply writing “knows SQL”. Add a line describing how you normalised a real dataset, which constraints you set and which indexes you added, and link the repo.

When reading FDE job descriptions, if you see phrases such as “data modeling” or “customer data”, have a normalisation example and an ER diagram you drew yourself ready to bring to the interview. That is the most concrete way to show you have done the work, not just studied it.

At a client site, the first question is usually not “which model is best” but “can this table be trusted”. CS50 SQL helps you answer that before the client thinks to ask.