Model many-to-many relationships

A post can have many tags, and a tag can belong to many posts. Neither table has one column that can represent the complete relationship.

Add a junction table:

CREATE TABLE post_tags (
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

Each row represents one relationship. The composite primary key prevents the same tag from being attached to the same post twice.

Add a tag to a post with an ordinary insert:

INSERT INTO post_tags (post_id, tag_id)
VALUES (12, 3);

Read all tags for the post:

SELECT tags.name
FROM tags
JOIN post_tags ON post_tags.tag_id = tags.id
WHERE post_tags.post_id = 12
ORDER BY tags.name;

If post 12 has tags sql and postgres, that query returns two rows.

The junction can hold facts about the relationship itself. If tags are attached by users, add columns such as added_by and added_at to post_tags, not to posts or tags.

Without the primary key or a unique constraint, duplicate junction rows can multiply join results and counts. DISTINCT might hide the symptom, but enforcing the real rule prevents the bad state.

The cascading deletes above are deliberate. A relationship row has no meaning after its post or tag disappears. Review that choice for your own data before you copy it.

Query posts for one tag by reversing the join direction:

SELECT posts.title
FROM posts
JOIN post_tags ON post_tags.post_id = posts.id
WHERE post_tags.tag_id = 3;

The junction table is the only place that knows both sides of the link. Neither posts nor tags stores the full many-to-many relationship alone. That keeps each table focused on its own entity. Name the junction table after both sides, such as post_tags, so its role is obvious in larger schemas.

Try this on your own: connect two posts to two tags, attempt a duplicate relationship, and query both directions (tags for one post and posts for one tag).