Model one-to-many relationships

One author can write many posts, while each post belongs to one author. This is a one-to-many relationship.

The foreign key belongs on the many side:

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES authors(id),
  title VARCHAR(300) NOT NULL
);

Each post stores one author_id. Several post rows can store the same value.

Find all posts by author 7:

SELECT id, title
FROM posts
WHERE author_id = 7
ORDER BY id;

If author 7 wrote three posts, you get three rows back.

Or join the author name into the result:

SELECT authors.name, posts.title
FROM authors
JOIN posts ON posts.author_id = authors.id
WHERE authors.id = 7;

Do not store a comma-separated list such as '4,8,15' on the author row. The database cannot enforce each listed identifier as a foreign key. Ordinary joins cannot treat the pieces as rows. Updates become painful too.

The relationship also contains a business decision. If a draft post may exist before an author is assigned, author_id can allow NULL. If every post must always have an author, use NOT NULL.

Copying the author name into every post row creates update problems. When the author renames themselves, you would need to touch every post. The foreign key keeps one source of truth.

The one side of the relationship does not store a list of child IDs. You find children by querying the many side with the parent’s key.

This pattern appears everywhere: users and orders, categories and products, projects and tasks. The foreign key always lives on the many side. Once you spot the pattern, schema design gets faster.

Try this on your own: insert one author and three posts that reference it. Query the posts by foreign key, then explain why the same author data should not be copied into every post row.