ブログを例にテーブル設計を練習する

今回やること

この記事では、ブログアプリを例に、テーブルをどう分けるかを練習します。

テーブル設計では、画面に表示したい形ではなく、データの種類と関係で分けます。

前提条件

• SQLの CREATE TABLE を見たことがある
• 主キーと外部キーの意味をなんとなく知っている
• 本格的な設計理論ではなく、初学者向けの練習として読む

Step 1: 必要なデータを洗い出す

ブログアプリで扱うものを考えます。

この時点で、少なくとも users、posts、comments、tags が候補になります。

Step 2: usersテーブル

ユーザー情報を保存します。

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

email は重複してほしくないので UNIQUE を付けます。

Step 3: postsテーブル

記事は、投稿者であるユーザーに紐づきます。

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  published BOOLEAN NOT NULL DEFAULT false,
  created_at DATE NOT NULL
);

user_id が users.id を参照します。

Step 4: commentsテーブル

コメントは、記事とユーザーの両方に紐づきます。

CREATE TABLE comments (
  id INTEGER PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id),
  user_id INTEGER NOT NULL REFERENCES users(id),
  body TEXT NOT NULL,
  created_at DATE NOT NULL
);

この設計にすると、「どの記事へのコメントか」「誰が書いたコメントか」を追えます。

Step 5: tagsテーブル

タグは記事と多対多の関係になりやすいです。1つの記事には複数タグが付き、1つのタグは複数記事で使われます。

まずタグ本体を作ります。

CREATE TABLE tags (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

Step 6: 中間テーブルを作る

記事とタグの関係は、中間テーブルで表します。

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

post_tags は、記事IDとタグIDの組み合わせを保存します。

多対多の関係は、中間テーブルを使って表すのが基本です。

Step 7: JOINで記事と投稿者を読む

SELECT
  posts.id,
  posts.title,
  users.name AS author_name
FROM posts
INNER JOIN users ON posts.user_id = users.id;

記事テーブルには投稿者名を直接保存せず、users とJOINして表示します。

Step 8: 設計を見直す

最初から完璧な設計にする必要はありません。ただし、次の観点は確認します。

よくあるエラー

まとめ

ブログアプリでは、ユーザー、記事、コメント、タグを別テーブルとして考えます。1対多は外部キーで、多対多は中間テーブルで表します。画面に表示する形ではなく、データの種類と関係を基準に設計することが重要です。

参考リソース

• PostgreSQL Data Definition
• PostgreSQL DDL Basics
• PostgreSQL Foreign Keys

×

PR

×

PR