Database design basics

In plain English

A database stores information in tables, which work like spreadsheets: each table is a grid of columns (the fields, such as a name) and rows (one record, such as a single person). A primary key is a column that uniquely identifies each row, while a foreign key is a column in another table that points back to a primary key so records can be linked. A one-to-many relationship means one row can be linked to many rows in another table (one author, many books), while a many-to-many relationship needs a joining table in the middle (one book can have many tags, and one tag can appear on many books). Normalisation means organising tables so each fact is stored in exactly one place, which reduces duplicated data and keeps updates consistent.

Step by step

1. 1List the real-world things you need to track, such as authors, books, and tags, and give each one its own table.
2. 2Add a primary key column to each table so every row has a unique id.
3. 3Add foreign key columns to link tables together, like author_id on books.
4. 4For a many-to-many link, create a joining table with foreign keys to both sides.
5. 5Split repeated information into separate tables so each fact lives in one place.

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

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

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  label TEXT NOT NULL UNIQUE
);

CREATE TABLE book_tags (
  book_id INTEGER NOT NULL REFERENCES books(id),
  tag_id INTEGER NOT NULL REFERENCES tags(id),
  PRIMARY KEY (book_id, tag_id)
);

Common mistakes

• Storing the same author name on every one of their books, so a rename must be updated in many rows.
• Forgetting to add a primary key, which makes rows impossible to identify uniquely.
• Skipping a joining table for a many-to-many relationship and cramming several values into one column.
• Creating one huge table with every piece of information instead of a few focused tables.