すべてのガイドに戻る
データベース設計··読了時間 5 分

データベーススキーマをゼロから設計する手順

リレーショナル データベース スキーマをゼロから設計します。要件からテーブル、関係、SQL による制約までのステップバイステップのウォークスルー。

要件がエンティティ、リレーションシップ、および顧客と注文のキーを持つ SQL スキーマになります。
このガイドの内容
  1. スキーマ設計が重要である理由
  2. ステップ 1 — 要件の収集
  3. ステップ 2 — エンティティの特定
  4. ステップ 3 — 属性とデータ型の定義
  5. ステップ 4 — リレーションシップの確立
  6. ステップ 5 — 制約とインデックスの適用
  7. ステップ 6 — 正規化(必要に応じて非正規化)
  8. 参考資料と詳細な読み物

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 変換用。

参考資料と詳細な読み物

もっと見る