Blog Database ER Diagram

A blog needs users who write posts, posts grouped in categories, readers who comment, and free-form tags. Tags and posts form a many-to-many relationship, materialized here with a post_tags join table so a post can carry any number of tags.

Publishing state lives in two columns on posts rather than in a separate drafts table: published is the boolean flag, and published_at is the timestamp, null until publication. A draft is published = false; a scheduled post is published = true with published_at in the future, rendered by a worker once the clock passes. Keeping both states in one row means edits never have to be merged between a draft copy and a live copy — the main reason this pattern beats a drafts table for small sites.

In the diagram, users to posts is a plain one-to-many through author_id, and categories to posts is one-to-many too, with category_id nullable so a post can be uncategorized. Tags are the many-to-many: post_tags joins posts and tags, and its composite primary key (post_id, tag_id) makes the pair unique — the same tag cannot be attached twice. posts.slug is UNIQUE independently of the title, because slugs go in URLs and must never collide or change, even when titles are edited later.

The cascades are one-directional on purpose. Deleting a post cascades into post_tags and comments, cleaning up its dependents in one statement. Deleting a tag cascades only the join rows — the posts keep standing. But deleting a user with posts fails: posts.author_id has no ON DELETE rule, so the schema forces you to reassign authorship first. That is the right friction for a content site, where authorship is part of the record. Categories behave the same way — posts.category_id refuses to let you delete a category that still holds posts.

Two additions cover most requests that follow. post_revisions (post_id, content, saved_at, author_id) gives writers an undo path, which a single content column cannot. A media table (uploaded_by, url, alt_text) gives images a home instead of loose URLs buried in post bodies. And if the category structure ever needs nesting, categories gains a parent_id self-reference exactly like the e-commerce schema — the same pattern, because the underlying question, how to represent a hierarchy, does not change with the domain.

The diagram

Tables (6)

Relationships (5)

• 1:Nusers.id→posts.author_id
• 1:Ncategories.id→posts.category_id
• 1:Nposts.id→comments.post_idON DELETE CASCADE
• 1:Nusers.id→comments.author_id
• N:Mposts.id→tags.id

The SQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  display_name VARCHAR(80) NOT NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'author',
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE,
  slug VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES users(id),
  category_id INTEGER REFERENCES categories(id),
  title VARCHAR(200) NOT NULL,
  slug VARCHAR(220) NOT NULL UNIQUE,
  content TEXT NOT NULL,
  published BOOLEAN NOT NULL DEFAULT false,
  published_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

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

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)
);

CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  author_id INTEGER NOT NULL REFERENCES users(id),
  body TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

Keep learning

• What Is an ER Diagram?
• How to Create an ER Diagram
• SQL to ER Diagram: The Complete Guide
• Primary Key vs Foreign Key
• All guides