Blog System ERD with Posts, Comments, and Tags

Designing a Blog Database Schema

A blog system is an excellent ERD exercise because it naturally involves all major relationship types: one-to-many (users to posts), many-to-many (posts to tags), one-to-one (users to profiles), and self-referencing (threaded comments).

Entity Design

Users and Profiles (1:1)

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(320) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE user_profiles (
  user_id INT PRIMARY KEY,
  display_name VARCHAR(100),
  bio TEXT,
  avatar_url VARCHAR(512),
  website_url VARCHAR(512),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Posts (1:N from users)

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  content TEXT NOT NULL,
  excerpt TEXT,
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  published_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  FOREIGN KEY (author_id) REFERENCES users(id)
);

Tags (M:N with posts)

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  slug VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE post_tags (
  post_id INT NOT NULL,
  tag_id INT NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts(id),
  FOREIGN KEY (tag_id) REFERENCES tags(id)
);

Threaded Comments (self-referencing)

CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INT NOT NULL,
  author_id INT NOT NULL,
  parent_comment_id INT,
  content TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  FOREIGN KEY (post_id) REFERENCES posts(id),
  FOREIGN KEY (author_id) REFERENCES users(id),
  FOREIGN KEY (parent_comment_id) REFERENCES comments(id)
);

Relationship Summary

From	To	Type	Key
users	user_profiles	1:1	Shared PK
users	posts	1:N	posts.author_id
users	comments	1:N	comments.author_id
posts	comments	1:N	comments.post_id
comments	comments	Self-ref	comments.parent_comment_id
posts ↔ tags	M:N	post_tags junction

Design Highlights

• Slug field on posts: Enables SEO-friendly URLs (/blog/my-first-post)
• Status enum: draft, published, archived — controls post visibility
• Threaded comments: parent_comment_id enables nested reply threads
• Shared PK for profiles: user_profiles.user_id is both PK and FK, guaranteeing 1:1