Tables, Keys and Indexes

Before you write queries, you decide what tables exist and how they connect. Those decisions are hard to change once real data is in them, and a poor design shows up later as bugs that no amount of application code fixes cleanly. This lesson designs the tables for a small blogging app with users and posts, using PostgreSQL syntax.

One table per kind of thing

Start from the nouns in your app: users, posts, comments. Each becomes a table, and each column holds one fact about that thing. A user has an email and a name; a post has a title, a body and a creation time.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL
);

NOT NULL makes a column required. UNIQUE makes the database itself refuse a second user with the same email, which your code cannot guarantee on its own: two signup requests arriving at the same moment can both pass a "does this email exist?" check before either one saves.

Primary keys

A primary key identifies exactly one row, forever. It must be unique, never empty, and should never change. That is why most tables use a generated number or random id instead of something meaningful like an email. People change their email address; if the email were the key, every other table that points at that user would need updating too, and anything you missed would break.

SERIAL makes Postgres hand out 1, 2, 3 and so on automatically.

Foreign keys and one-to-many

One user writes many posts, and each post has exactly one author. This is a one-to-many relationship, and the link goes on the "many" side: each post stores its author's id.

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

REFERENCES users(id) makes user_id a foreign key. The database now refuses a post whose user_id points at a user that does not exist, and by default it refuses to delete a user who still has posts, so you never get posts with a missing author. If you do want a user's posts removed with them, you say so with ON DELETE CASCADE.

Do not do it the other way round, with a list of post ids stored in each user row. A list in one column cannot be joined or protected by a foreign key, and every new post means rewriting the user.

When both sides can have many, such as posts and tags, add a third table (post_tags) with one row per pair: a post_id and a tag_id.

Store each fact once

It is tempting to copy the author's name into every post so you can skip a join. Then Ana renames herself to Anna: the users table says Anna, and her 200 old posts still say Ana. Now the same fact has two answers and your app shows whichever it happens to read.

Store the name once, in users, and join when you need it:

SELECT posts.title, users.name
FROM posts
JOIN users ON users.id = posts.user_id;

Joins on keys are fast. Copying data is a deliberate trade-off for later, when you have measured a real performance problem, not a default.

Indexes: fast reads, slower writes

Without an index, finding all posts by user 42 means the database reads every row in posts. With a million posts, that is slow. An index is a sorted lookup structure, like the index at the back of a book, that jumps straight to the matching rows:

CREATE INDEX posts_user_id_idx ON posts (user_id);

Indexes help WHERE, JOIN and ORDER BY on the indexed columns. Postgres creates one automatically for primary keys and UNIQUE columns, but not for foreign keys, so add those yourself when you query by them.

Indexes are not free. Every insert, update and delete must also update each index, and each one takes disk space. Index the columns your real queries filter, join or sort on, not every column just in case.

A common mistake

Designing tables around screens instead of data. A "profile page" table that mixes user details with their latest three posts falls apart the moment a second page needs the same data differently. Try it: design the tables for comments on this blog. Which table holds the foreign keys, and which columns would you index?