SQL の例では、別のダイアレクトが指定されていない限り PostgreSQL を使用します。短いスニペットは、参照されているテーブルがすでに存在することを前提としています。適用する前に、ターゲットデータベースで DDL を検証してください。
スキーマ設計が重要である理由
適切に設計されたデータベーススキーマは、すべての信頼性の高いアプリケーションの基礎です。正しく設計すれば、クエリは高速になり、データの整合性が保たれ、将来の機能も面倒な移行なしに追加できます。間違えると、データの異常、パフォーマンスの低下、プロジェクトの存続期間中続く終わりのない回避策に悩まされることになります。
このガイドでは順を追って説明します: 6段階のプロセス ゼロからスキーマを設計するためのものであり、独自のプロジェクトに適応できる完全な実世界の例で締めくくられています。
ステップ 1 — 要件の収集
ツールを開く前に、ステークホルダーと話し合ってください。アプリケーションの目的を理解する 行う、それが管理するデータ、およびそのデータがどのように関連しているか。次のような質問をします:
- システムが追跡する主な「もの」は何ですか?(ユーザー、注文、製品...)
- 一意である必要があるデータはどれですか? (メールアドレス、SKU、スラグなど…)
- アプリケーションが最も頻繁に実行するクエリは何ですか?
- 規制上の制約はありますか? (GDPR、HIPAAなど)
- 今後 1〜3 年間で予想されるデータ量はどれくらいですか?
回答を書き出しましょう。それらが仕様となり、今後のすべての決定のガイドとなります。
ステップ 2 — エンティティの特定
1つの エンティティ システムがデータを保存する必要があるあらゆるものです。各エンティティは通常、1つのデータベーステーブルに対応します。ブログプラットフォームの場合、最初のエンティティリストは次のようになります:
- ユーザー — 著者と読者
- 投稿 — ブログに公開された記事
- カテゴリ — 投稿のグループ化 (例: "React", "SQL")
- コメント — 投稿に対する読者の反応
- タグ — きめ細かなラベル (投稿との多対多)
ホワイトボードにボックスとしてスケッチするか、 ERDビルダー カラムの型を選択する前に、概念モデルについて議論する。
ステップ 3 — 属性とデータ型の定義
すべてのエンティティについて、必要な属性をリストアップし、最も適切な SQL データ型を選択します。できる限り具体的に指定してください。優先するもの: VARCHAR(255) 〜の上に TEXT 最大長がわかっており、〜を好む場合 TIMESTAMPTZ 〜の上に TIMESTAMP タイムゾーンが重要な場合。
-- Users
id SERIAL PRIMARY KEY
username VARCHAR(50) NOT NULL UNIQUE
email VARCHAR(255) NOT NULL UNIQUE
password_hash TEXT NOT NULL -- store a password hash, never plaintext
bio TEXT
avatar_url VARCHAR(500)
created_at TIMESTAMPTZ DEFAULT now()
-- Posts
id SERIAL PRIMARY KEY
author_id INT NOT NULL -- FK → users.id
category_id INT -- FK → categories.id
title VARCHAR(200) NOT NULL
slug VARCHAR(220) NOT NULL UNIQUE
body TEXT NOT NULL
status VARCHAR(20) DEFAULT 'draft' -- draft | published | archived
published_at TIMESTAMPTZ
created_at TIMESTAMPTZ DEFAULT now()
updated_at TIMESTAMPTZ DEFAULT now()ステップ 4 — リレーションシップの確立
エンティティが互いにどのように関連しているかを決定します。3つの基本的なタイプは次のとおりです:
- 1対多 (1:N) — 1人のユーザーは多くの投稿を持ち、1つの投稿は1人のユーザーに属します。
- 多対多 (M:N) — 1つの投稿には多くのタグを付けることができ、1つのタグは多くの投稿に属することができます。これには〜が必要です 中間テーブル.
- 1対1 (1:1) — 1人のユーザーは1つのプロフィールを持ちます。プロフィールのデータが大きいか、めったにアクセスされない場合を除き、通常は1つのテーブルにマージされます。
-- Junction table for the posts ↔ tags many-to-many relationship
CREATE TABLE post_tags (
post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
tag_id INT NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);ステップ 5 — 制約とインデックスの適用
制約はデータの品質を保護します。最低限、以下を検討する必要があります:
NOT NULL値を持つ必要があるすべてのカラムに対して。UNIQUE自然識別子(メール、スラッグ、ユーザー名)に対して。CHECKドメインルールの制約(例:ステータスは 3 つの値のいずれかである必要があります)。DEFAULTタイムスタンプとステータス列の値。- 明示的な指定がある外部キー
ON DELETEアクション。
次に追加します インデックス 頻繁にフィルタリング、ソート、または結合を行うカラムに対して。外部キーカラムは主要な候補です。
-- Index foreign-key columns for faster joins
CREATE INDEX idx_posts_author ON posts (author_id);
CREATE INDEX idx_posts_category ON posts (category_id);
CREATE INDEX idx_comments_post ON comments (post_id);
-- Partial index: only published posts (PostgreSQL)
CREATE INDEX idx_posts_published
ON posts (published_at DESC)
WHERE status = 'published';ステップ 6 — 正規化(必要に応じて非正規化)
正規化 一連の「正規形」に従ってカラムとテーブルを整理することで、冗長なデータを排除します。ほとんどのスキーマが到達すべきレベルは 第3正規形 (3NF):
- 第1正規形 (1NF) — すべての列が原子値を保持し、繰り返しグループはありません。
- 第2正規形 (2NF) — 非主属性が候補キーの一部のみに依存していないこと。
- 第3正規形 (3NF) — すべての非自明な依存関係において、決定子がスーパーキーであるか、依存属性が候補キーに属しています。
正規化した後、選択的に行うことができます 非正規化 パフォーマンス用 — 例えば以下を保存するなど: comment_count 〜の上の posts コストのかかる事態を避けるためのテーブル COUNT(*) ページ読み込みのたびに。意図的に非正規化し、それを文書化してください。
完全なブログプラットフォームスキーマ
-- Full SQL for the blog platform schema
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
bio TEXT,
avatar_url VARCHAR(500),
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
slug VARCHAR(110) NOT NULL UNIQUE
);
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
author_id INT NOT NULL REFERENCES users (id),
category_id INT REFERENCES categories (id) ON DELETE SET NULL,
title VARCHAR(200) NOT NULL,
slug VARCHAR(220) NOT NULL UNIQUE,
body TEXT NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft','published','archived')),
published_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE post_tags (
post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
tag_id INT NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
user_id INT NOT NULL REFERENCES users (id),
body TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
-- Indexes
CREATE INDEX idx_posts_author ON posts (author_id);
CREATE INDEX idx_posts_category ON posts (category_id);
CREATE INDEX idx_comments_post ON comments (post_id);
CREATE INDEX idx_comments_user ON comments (user_id);完全なスキーマを次へ貼り付けます: SQL から ERD エディター そのリレーションシップを検査するために。次を使用してください: ERDビルダー 概念図およびレビュー済みの SQL 変換用。



