投稿・コメント・タグを持つブログシステムのERD

ブログデータベーススキーマの設計

ブログシステムは、すべての主要なリレーションシップタイプを自然に含むため、優れたERD演習です：1対多（ユーザーから投稿）、多対多（投稿からタグ）、1対1（ユーザーからプロフィール）、自己参照（スレッドコメント）。

エンティティ設計

ユーザーとプロフィール（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)
);

投稿（usersからの1:N）

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

タグ（postsとM:N）

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

スレッドコメント（自己参照）

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

リレーションシップの概要

From	To	タイプ	キー
users	user_profiles	1:1	共有PK
users	posts	1:N	posts.author_id
users	comments	1:N	comments.author_id
posts	comments	1:N	comments.post_id
comments	comments	自己参照	comments.parent_comment_id
posts ↔ tags	M:N	post_tags結合テーブル

設計のハイライト

• 投稿のslugフィールド: SEOフレンドリーなURL（/blog/my-first-post）を可能に
• statusの列挙: draft、published、archived — 投稿の公開状態を制御
• スレッドコメント: parent_comment_idによりネストされた返信スレッドを実現
• プロフィールの共有PK: user_profiles.user_idがPKとFKの両方であり、1:1を保証