Designing tables with SQL

Each table holds one kind of thing

Say you want to build a small app to keep track of books you have read and who wrote them. You want to use a database, and for an app like this, SQLite can be a good choice. For this learning site, I choose DuckDB because it gives helpful error messages and I wanted to learn one dialect first. Whichever database, it will need tables. A good design for this app is two tables, one for authors and one for books. Once they have some data, they might look like this:

authors

books

Each table holds one kind of thing. Each row is one author or one book, and each column is one fact about it. The author_id column in books connects each book to its author.

Start from an empty database

The playground already has both these tables, filled with the rows above. To create them yourself, you first have to delete them. In SQL, deleting a table is called dropping it:

DROP TABLE books;

DROP TABLE authors;

DROP TABLE removes the table and every row in it. On a real database, that's a reason to stop and be very certain you know what you are doing. Here it's safe: the playground loads its data from a file each time the page opens, so reloading the page brings both tables back. The order matters. Reload the playground and try authors before books. It fails, because each book points to an author through author_id, and DuckDB refuses to drop authors while books exists.

Create tables and connect them

With the old tables gone, these statements create them again, empty and ready for data:

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

CREATE TABLE books (
  id        INTEGER PRIMARY KEY,
  title     TEXT NOT NULL,
  year      INTEGER,
  author_id INTEGER NOT NULL REFERENCES authors(id)
);

Each column has a name and a data type. NOT NULL means the value is required: a book must have a title, but its year can be left empty if you don't know it.

The id column is the table's primary key, a value that identifies exactly one row. Two books can have the same title, but never the same id. The DuckDB set up in this playground doesn't fill in the id for you, so you give each row its id when you add it.

books doesn't store the author's name, only a number: author_id, the id of one of the authors. REFERENCES authors(id) tells the database about this link, so it can look after it for you. If you try to add a book by author 9, who doesn't exist, the database says no.

Rules like this one, and NOT NULL above, are called constraints. You write them once, when you create the table, and the database checks them every time the data changes. A column that points to another table, like author_id, is a foreign key. That's also why authors couldn't be dropped first: the books still point to it.

Because of the link, each author's name exists in one place, and if you misspell it, you fix it once. A useful rule of thumb: if you find yourself writing the same value over and over in a column, it probably belongs in its own table.

Fill the tables with INSERT

The new tables are empty. You can check with SELECT:

SELECT * FROM books;

The result shows the column names, id, title, year and author_id, but no rows.

INSERT INTO adds rows. You name the table and its columns, then give the values for each row in the same order as the columns. These statements fill both tables with the rows from the start of the page:

INSERT INTO authors (id, name) VALUES
  (1, 'Ursula K. Le Guin'),
  (2, 'Emily St. John Mandel'),
  (3, 'N.K. Jemisin');

INSERT INTO books (id, title, year, author_id) VALUES
  (1, 'The Left Hand of Darkness', 1969, 1),
  (2, 'The Dispossessed', 1974, 1),
  (3, 'Station Eleven', 2014, 2),
  (4, 'The Fifth Season', 2015, 3),
  (5, 'The Obelisk Gate', 2016, 3),
  (6, 'The Stone Sky', 2017, 3);

Insert the authors first. The foreign key refuses a book whose author_id doesn't match an author that's already in the table.

Run the same SELECT again, and this time it returns the six books.

Test the design by querying it

A good way to check a design is to ask the questions your app will need to answer. Which books have I read, and who wrote them?

SELECT authors.name, books.title, books.year
FROM books
JOIN authors ON authors.id = books.author_id
ORDER BY authors.name, books.year;

The JOIN follows author_id from each book to its row in authors, so the answer lists every book next to the name of its author.

The answer takes one join and no workarounds, which is a good sign for the design. If a question like this is hard to write, that's often a sign the design is missing a table or a column. Your first design won't be your last. When you need something new, you can add it later. Changing the design as you iterate on your app is a normal part of the work.

A join can leave out rows without telling you

Say you want to keep track of authors you'd like to read, before you've read any of their books. Add one:

INSERT INTO authors (id, name) VALUES (4, 'Octavia E. Butler');

Then run the query from the previous section again. The result is the same six rows as before, and Octavia E. Butler isn't in it. There's no error, so nothing tells you a row is missing.

JOIN only keeps rows that have a match in both tables. No book has author_id 4, so the new author has nothing to match, and the join drops her. This kind of join is called an inner join, and JOIN on its own means INNER JOIN.

To keep every author, start from authors and use LEFT JOIN:

SELECT authors.name, books.title, books.year
FROM authors
LEFT JOIN books ON books.author_id = authors.id
ORDER BY authors.name, books.year;

LEFT JOIN keeps every row from the table on its left, the one in FROM, even when there's no match. Where there's no matching book, the book columns are NULL:

Neither join is wrong. They answer different questions. "Which books have I read, and who wrote them?" needs the inner join, because a book list shouldn't include authors without books. "Which authors are in my app?" needs the LEFT JOIN. When you test a design, add a row that has no match, like an author without books, and check that each query still answers the question you meant.