ER 図の例：オンラインストアのデータベースを一歩ずつ設計する

ER 図は、データベース設計をマイグレーションにする前に確認する場です。テーブルの一覧からは、何が存在するかがわかります。図からは、テーブル同士がどう依存しているか、つまりどの行がどの行を必要とし、何が欠けてもよく、多対多がどこに隠れているかがわかります。

このガイドでは、小さなオンラインストアのデータベースを、平易な要件から完成した ER 図まで設計し、記法は出てきた時点で説明します。ストアはあくまで例で、この方法はどんなスキーマにも使えます。

ER 図が必要になるとき

スキーマのテーブルが数個で、1 つのチームだけが扱っているなら、テーブル一覧で十分です。チーム外の人がデータを理解する必要があるとき、マイグレーションを書く前に設計をレビューするとき、あるいはスキーマが誰の頭にも収まらないほど大きくなったときに、図を描きましょう。

よく使われる記法は 2 つあります。Chen 記法は、エンティティ・リレーションシップモデルを提唱した Peter Chen の 1976 年の論文に由来し、エンティティを長方形、リレーションシップをひし形で描きます。概念モデリングに向いています。鳥の足記法（crow's foot）は、テーブルを箱で描き、カーディナリティを線の端に置きます。一般に 1976 年の Gordon Everest によるものとされ、多くのデータベース設計ツールが採用している記法です。このガイドでは鳥の足記法を使います。

ステップ 1: 要件を平易な文で書く

まずルールを文として書き出します。それぞれがテーブル、列、または線になります。

1. 顧客はメールアドレスで登録し、複数の住所を保存できます。
2. 顧客は注文を行います。各注文は、その顧客の住所のいずれかに配送されます。
3. 注文には 1 つ以上の商品が含まれ、それぞれに数量と支払った価格があります。
4. 各商品は 1 つのカテゴリに属します。
5. 注文には複数の支払い試行がありえますが、ほとんどの注文には成功した試行が 1 つあります。

ステップ 2: エンティティとキー

名詞がエンティティになります。顧客、住所、注文、注文明細、商品、カテゴリ、支払いです。それぞれに主キーを付けます。ほとんどのテーブルにはサロゲートキー（ID 列）を使い、業務上すでに一意な値があるところでは自然キーも使います。顧客のメールアドレスや商品の SKU などです。

注文明細は例外です。注文明細には独自の識別子がなく、1 つの注文と 1 つの商品を結ぶものです。主キーは (order_id, product_id) の組です。

ステップ 3: リレーションシップとカーディナリティ

外部キーはすべて線になります。両端の記号は、その側に何行存在しうるかを表します。

リレーションシップは、あるテーブルから線をたどって反対側の端の記号まで読みます。「1 人の顧客は 0 件以上の注文を行う」、逆方向には「各注文はちょうど 1 人の顧客が行う」となります。

NOT NULL の外部キーは参照される側で「ちょうど 1」を、NULL を許す外部キーは「0 または 1」を意味します。反対側の「1 以上」は業務ルールであり、外部キーが強制するものではありません。読み手の目に入るよう、図に書き込みましょう。

ステップ 4: 多対多

注文と商品は多対多です。1 つの注文には多くの商品が含まれ、1 つの商品は多くの注文に現れます。リレーショナルデータベースでは、これを中間のテーブルでモデル化します。ここでは、そのテーブルが単なるつなぎ以上の理由ですでに存在しています。order_items は購入時点の数量と単価を持ちます。これらはどちらか一方ではなく、組そのものに属する事実です。

ステップ 5: 書き残すべき判断

列の一覧からは見えず、誰も知らないとバグの原因になる選択があります。

• 価格のスナップショット。 order_items.unit_price は購入時点の商品価格をコピーします。後で価格を変えても、過去の注文が変わってはいけません。
• 住所はその場で編集しない。 注文が住所を参照したあとでその行を編集すると、過去の注文の配送先を書き換えることになります。編集すると新しい住所の行が作られます。
• 支払いは試行として記録する。 試行ごとにステータス付きで 1 行を作るので、失敗した支払いや再試行された支払いも見えたままです。
• 金額は numeric にする。 PostgreSQL のドキュメントは、正確に扱えるため、金額には numeric を推奨しています。

ステップ 6: DDL

PostgreSQL での中心となる 2 つのテーブルです。

CREATE TABLE orders (
  id                  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id         bigint NOT NULL REFERENCES customers (id),
  shipping_address_id bigint NOT NULL REFERENCES addresses (id),
  status              text NOT NULL DEFAULT 'pending'
                      CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  currency            char(3) NOT NULL,
  total               numeric(12,2) NOT NULL CHECK (total >= 0),
  placed_at           timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id   bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  product_id bigint NOT NULL REFERENCES products (id),
  quantity   integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(12,2) NOT NULL,  -- copied from products.price at checkout
  PRIMARY KEY (order_id, product_id)
);

CREATE INDEX ON orders (customer_id);
CREATE INDEX ON order_items (product_id);

PostgreSQL は外部キー列にインデックスを自動では作成しません。そのため、2 行の CREATE INDEX は結合と、参照先テーブルでの削除に効いてきます。order_items.order_id は、この列から始まる主キーのインデックスでカバーされます。ON DELETE CASCADE は、注文明細が注文より長く存在できないことを表します。

図

図の読み方は次のとおりです。

• 顧客（左上）は 0 個以上の 住所 を持ち、0 件以上の 注文 を行います。
• 注文（中央）はちょうど 1 つの住所に配送され、1 件以上の 注文明細 を含み、0 件以上の 支払い を持ちます。
• 注文明細 は各注文を 商品 に結び付け、各商品はちょうど 1 つの カテゴリ に属します。
• メモ には、ステップ 5 の判断が、影響するテーブルの横に書かれています。

読みやすい ER 図の描き方

• リレーションシップには動詞で名前を付ける。 「行う」「配送する」「支払う」といったラベルで、線が文として読めるようになります。
• 属性でキーを示す。 PK と FK のマーカーがあれば、線がどの列に対応するかがひと目でわかります。
• テーブルをドメインごとにまとめる。 顧客と住所、注文と支払い、カタログ。グループごとに近くに配置して、線を短く保ちます。
• ノイズを省く。 created_by や updated_at のような監査用の列、全テーブルの論理削除フラグ、技術的なテーブルは、理解を深めずに行数だけを増やします。
• 多対多のテーブルを描く。 注文と商品の間を直接線で結んで隠すと、数量と価格のスナップショットも隠れてしまいます。
• スキーマで表せない業務ルールを示す。 注文ごとに「1 件以上」の明細があることや「住所は編集しない」ことは、関係者が知っておくべきルールです。

スキーマと同期させる

手で描いた ER 図は、次のマイグレーションで古くなります。代わりに、最初の下書きをスキーマから生成しましょう。無料の SQL を ER 図に変換 ツールは、CREATE TABLE 文や pg_dump 全体を読み込み、テーブルごとに PK、FK、UK のマーカー付きのカードを、外部キーごとに線を描きます。PostgreSQL、Prisma、DBML 用のツールもあります。生成される鳥の足記法の端は列に従い、NOT NULL の外部キーは必須、NULL を許す外部キーは任意になります。「1 件以上」の明細のように SQL が持たないルールは、手で追加します。

Archboard で作る

1. Google または GitHub でサインインし、図を作成します。
2. スキーマから始める場合は、CREATE TABLE 文を「SQL を ER 図に変換」ツールに貼り付け、Archboard で開く を選びます。手で描く場合は、テーブルごとに ER 図パックの エンティティ を追加し、1 行に 1 つの属性を書いて PK と FK のマーカーを付けます。
3. 各外部キーを参照先のテーブルに接続し、線の端を鳥の足記法のマーカーに設定します。参照先のテーブル側はちょうど 1、外部キー側は 0 以上、業務ルールで決まっているところは 1 以上です。
4. 各線に「行う」「配送する」のような動詞のラベルを付けます。
5. 基本パックの Markdown ブロックを追加し、設計判断を書きます。

この例で使うダイアグラムノードは 8 個で、Free プランの範囲に収まります。スキーマがシステム全体のどこに収まるかは、システム設計図の例を参照してください。

よくある質問

ER 図とデータベーススキーマ図の違いは何ですか？ 実際には重なっています。ER 図は概念的なものにもなり、テーブルができる前のエンティティとリレーションシップを示せます。スキーマ図は実際のテーブル、列、キーを示します。このガイドの鳥の足記法の図は、その両方です。

すべてのテーブルにサロゲートキーを付けるべきですか？ 自然キーは変わるので、ほとんどのテーブルにはサロゲートキーが役立ちます。ただし order_items のような中間テーブルでは、外部キーの組を主キーにすることがよくあります。

多対多のリレーションシップはどう表しますか？ 中間のテーブルと、2 本の一対多の線で表します。中間テーブルには、結ぶ 2 つのテーブルの名前ではなく、注文明細のように、それが表すものの名前を付けます。

すべての列を描くべきですか？ キーと、設計を説明する列を描きます。図の主題でない限り、監査用の列や技術的な列は省きます。

Chen 記法と鳥の足記法、どちらを使うべきですか？ テーブルで考えない人との概念的な議論には Chen 記法、データベースになるものにはすべて鳥の足記法を使います。

無料で始める — 1 シート、クレジットカード不要。