ER 設計 ── 図に描いて、別の捉え方と比べる

この章を終えると、スキーマを ER 図に描いて読み書きでき、同じ説明文から別の ER も描けること、採る ER は要件で決まり、要件が変われば変わることを説明できる。

この記事は、構成を Claude Opus が決め、本文を Gemini が書き、頁に組んだのは Claude です。

この章では、前章までに決めたスキーマを図に描いて読み書きする方法を学びます。同じ台帳の説明文からでも別の形の図を描くことができ、どの形を採るかはシステムに求められている要件によって決まります。要件が変われば図の形も変わるという設計の考え方を、具体例を比べながら確かめていきましょう。

ER 設計と ER 図

1-2「エンティティ」と 1-3「プロパティと型」では、社内の備品の貸し出し台帳を管理するために、エンティティ、主キー、外部キー、プロパティ、型を決め、データの構造であるスキーマを組み立てました。このうちエンティティとリレーションの組を ER(Entity-Relationship)と呼びます。ER の考え方と ER 図は、データベースの設計で広く使われています。そして、台帳の説明文をどのエンティティとリレーションで捉えるかを決めることを ER 設計、それを図に描いたものを ER 図と呼びます。

この Dev Tour では、ER 図を Mermaid という記法の erDiagram で、文字で書きます。Mermaid は図を文字で書くための記法で、erDiagram はその ER 図の書き方です。画像ファイルではなく文字で書くことには、大きな利点があります。文字で書いた ER 図は、そのまま AI に渡せて、AI に書かせることもできます。この頁の図はサイトを組むときにこの文字から描いたもので、図の下に元の文字を畳んで添えてあります。設計の意図を人に伝えるだけでなく、AI と共同で作業するときにもそのまま役立ちます。

ER 図。同じ内容を Mermaid の文字で書いたものが、この図の下の「元の文字」にあります。

図が画面より広いときは、横に送って読めます。

元の文字(Mermaid)を見る

erDiagram
  LOCATION["LOCATION 保管場所"] {
    id location_id PK "保管場所 ID"
    short_name name "名前"
  }
  ITEM["ITEM 備品"] {
    id item_id PK "備品 ID"
    short_name name "名前"
    item_kind kind "種類"
    date purchased_on "購入日(NULL 可)"
    id location_id FK "保管場所 ID(NULL 可、SET NULL)"
  }
  USER["USER 利用者"] {
    id user_id PK "利用者 ID"
    short_name name "氏名"
  }
  LOAN["LOAN 貸出"] {
    id loan_id PK "貸出 ID"
    date lent_on "貸した日"
    id item_id FK "備品 ID(RESTRICT)"
    id user_id FK "利用者 ID(RESTRICT)"
  }
  LOCATION |o--o{ ITEM : "置かれる"
  ITEM ||--o{ LOAN : "貸し出される"
  USER ||--o{ LOAN : "借りる"

上の図は、1-2「エンティティ」と 1-3「プロパティと型」で決めた内容を表した備品台帳の ER 図です。これに合わせて、これまでに作ってきた設計メモにも「ER 図」の節を足しました。図の中では、ITEM、LOCATION、USER、LOAN という 4 つのエンティティと、それらを結ぶリレーションが示されています。

図の中のエンティティとプロパティの名前は英語で書き、日本語の名前を横に添えています。日本語を添えることで、台帳のどの項目に対応しているのかを直感的に読み取ることができます。また、ここで決めた英語の名前は、第 2 部のプログラムでそのまま使います。

ER 図を読む

先ほど示した備品台帳の ER 図の読み方を、順番に見ていきましょう。図の中にある四角い箱の 1 つ 1 つがエンティティを表しています。箱の中の 1 行が 1 つのプロパティを表しており、型、名前、キー、説明の順に並んでいます。キーの欄にある PK は主キーを表し、FK は外部キーを表しています。このように、それぞれの箱を見れば、そのエンティティがどのような項目を持っているのかが分かります。

箱と箱を結ぶ線がリレーションを表しています。線の上に書かれている言葉は、リレーションを文で読むときの動詞です。線の両端の記号は、その側に相手がいくつ付くかを表しています。これをカーディナリティと呼びます。線の端の記号はカラスの足(crow's foot)記法と呼ばれ、広く使われる ER 図の書き方の 1 つです。|| はちょうど 1、|o と o| は 0 か 1、o{ と }o は 0 以上、|{ と }| は 1 以上を表します。線の端が枝分かれしている形がカラスの足に似ていることから、このように呼ばれています。

実際の読み方の例を見てみましょう。備品台帳の ER 図にある線の 1 つである ITEM ||--o{ LOAN は、「貸出 1 つには備品がちょうど 1 つ、備品 1 つには貸出が 0 以上」と読みます。これは 1-2「エンティティ」で学んだ 1 対多のリレーションであり、外部キーである item_id は多の側の貸出(LOAN)にあります。貸出の側から見ると備品(ITEM)は必ず 1 つ結びつくため || になり、備品の側から見るとまだ一度も貸し出されていない場合もあるため、貸出は 0 以上の o{ になります。

もう 1 つの例として、LOCATION |o--o{ ITEM は「備品の保管場所は 0 か 1(NULL 可)、保管場所 1 つに備品は 0 以上」と読みます。備品の中には保管場所がまだ決まっていないものもあるため、0 か 1 を表す |o になっています。保管場所(LOCATION)の側から見ると、その場所に置かれている備品は 0 個以上になるため o{ になります。

このように、線の両端の記号を見るだけで、互いにいくつ結びつくのかという関係を確かめることができます。なお、1-2「エンティティ」や 1-3「プロパティと型」の設計メモで決めた NULL 可、RESTRICT、SET NULL といった細かなルールは、プロパティの説明の欄に書いてあります。ER 図は、こうした設計の制約も合わせて伝えることができます。

採った ER は、要件から決まる

同じ説明文から設計を始める場合でも、描ける ER は決して 1 つだけではありません。サービスに求められていることを要件と呼びます。先ほど示した備品台帳の ER 図は、説明文に書かれていた望みである要件と、1-2「エンティティ」や 1-3「プロパティと型」で決めてきた事柄から採ったものです。

この先では、今回の備品台帳では採らなかった別の捉え方を 4 つ並べて見ていきます。これらはいずれも実際に描ける ER であり、どれも「その要件なら正しい」と言える場面が存在します。設計において、どれか 1 つの形だけが絶対に正しく、他が間違っているわけではありません。サービスの目的や置かれた状況に応じて、求められている要件が、採る ER を決めます。

それぞれの捉え方を比べるときに見る点は 3 つあります。1 つ目は、どの要件によって設計の判断が分かれるかという「どこで割れるか」です。2 つ目は、その形を採った場合に「何を失うか」です。そして 3 つ目は、「どんな要件なら採るか」です。この 3 つの視点でじっくりと比べることで、要件と ER の関係を確かめていきましょう。

こう捉えることもできる 1 ── 貸出を備品の中に持つ

1 つ目の別の捉え方は、貸出を独立したエンティティにせず、備品の中に「いま借りている利用者 ID」と「貸した日」のプロパティを持たせる形です。下の図では、説明を分かりやすくするために保管場所を省いています。

図が画面より広いときは、横に送って読めます。

元の文字(Mermaid)を見る

erDiagram
  ITEM["ITEM 備品"] {
    id item_id PK "備品 ID"
    short_name name "名前"
    item_kind kind "種類"
    date purchased_on "購入日(NULL 可)"
    id borrower_id FK "いま借りている利用者 ID(NULL 可)"
    date lent_on "貸した日(NULL 可)"
  }
  USER["USER 利用者"] {
    id user_id PK "利用者 ID"
    short_name name "氏名"
  }
  USER |o--o{ ITEM : "いま借りている"

この形を採ると、全体のエンティティが 3 つで済みます。また、「この備品はいま誰が持っているか」を確かめたいときは、備品を見るだけで分かります。備品のデータを開くだけで、現在の貸出状況がすぐに分かるため、データの構造としては非常に単純で扱いやすくなります。

この設計を採るかどうかが割れるところは、過去の貸出を後から見る要件があるかどうかです。この形では、備品が返却されると「いま借りている利用者 ID」は NULL に戻ります。次に誰かが借りたときは、その新しい利用者の ID と日付で上書きされます。そのため、誰がいつ借りたのかという過去の記録は上書きされて残りません。

つまり、この設計を採ることで失うものは、貸出の履歴です。1-2「エンティティ」で貸出を独立したエンティティにしたのは、いつ、誰が、どれを借りたかを後から見るためでした。誰が何日間借りていたかや、過去にどれくらい頻繁に使われたかといった履歴を後から確かめる要件がある場合、この形では対応できません。

この捉え方を採る要件は、いま誰が持っているかだけ分かればよく、履歴は要らない台帳です。過去の履歴を一切必要とせず、現在の持ち主さえ確認できれば足りる業務であれば、このようなシンプルな設計を採ることも十分に考えられます。

こう捉えることもできる 2 ── 保管場所を文字列のプロパティにする

2 つ目の別の捉え方は、保管場所をエンティティにせず、備品に「保管場所」という文字列のプロパティを持たせる形です。下の図を見てみましょう。

図が画面より広いときは、横に送って読めます。

元の文字(Mermaid)を見る

erDiagram
  ITEM["ITEM 備品"] {
    id item_id PK "備品 ID"
    short_name name "名前"
    item_kind kind "種類"
    date purchased_on "購入日(NULL 可)"
    string location "保管場所(文字列、NULL 可)"
  }

この形を採ると、全体のエンティティが 1 つ減り、外部キーも要りません。保管場所の名前を備品の中に直接書くだけなので、管理するエンティティの数も減り、構造はとても分かりやすくなります。

保管場所をエンティティにするかプロパティにするかが割れるところは、保管場所そのものを管理する要件があるかどうかです。たとえば、保管場所の一覧を画面に出す、保管場所の名前を変える、廃止する、といった機能が求められているかどうかで分かれます。

この設計を採ることで失うものは、表記の一致や変更の手軽さです。文字列で持たせると、「3 階の棚」と「3F 棚」のように表記の揺れが起き、システム上は別の場所になってしまいます。また、場所の名前を変えるときには、その場所にある備品のプロパティを全部直す必要があります。さらに、備品の無い場所を覚えておくこともできません。備品が 1 つも置かれていない場所は、データとして残せなくなるためです。1-2「エンティティ」で決めた SET NULL というルールも、保管場所がエンティティだから書けたものでした。

この捉え方を採る要件は、保管場所が社内に数か所しかなく、場所そのものは管理しない台帳です。場所の一覧や変更を扱う必要がない要件であれば、わざわざエンティティを分けずに文字列のプロパティとして済ませる選択肢もあります。

こう捉えることもできる 3 ── 社員と管理者を別のエンティティにする

3 つ目の別の捉え方は、利用者を 1 つにまとめず、社員と管理者を別のエンティティにする形です。下の図のように、説明文に出てくる 2 つの呼び名をそのまま別々の箱にした形と言えます。

図が画面より広いときは、横に送って読めます。

元の文字(Mermaid)を見る

erDiagram
  EMPLOYEE["EMPLOYEE 社員"] {
    id employee_id PK "社員 ID"
    short_name name "氏名"
  }
  ADMIN["ADMIN 管理者"] {
    id admin_id PK "管理者 ID"
    short_name name "氏名"
  }
  ITEM["ITEM 備品"] {
    id item_id PK "備品 ID"
  }
  LOAN["LOAN 貸出"] {
    id loan_id PK "貸出 ID"
    date lent_on "貸した日"
    id item_id FK "備品 ID"
    id employee_id FK "借りた社員 ID(NULL 可)"
    id admin_id FK "借りた管理者 ID(NULL 可)"
  }
  ITEM ||--o{ LOAN : "貸し出される"
  EMPLOYEE |o--o{ LOAN : "借りる"
  ADMIN |o--o{ LOAN : "借りる"

この設計を採るかどうかが割れるところは、同じ人が社員でも管理者でもありうるか、そして役割が途中で変わるかという点です。たとえば、管理者である総務の佐藤さんが、自分の仕事で備品を借りることも十分にありえます。もし社員と管理者を別々のエンティティにしてしまうと、佐藤さんは社員と管理者の両方に登録されることになってしまい、データが重複してしまいます。

この形を採ることで失うものは、データの単純さや扱いやすさです。社員と管理者を別々のエンティティに分けると、貸出の外部キーが 2 本になってしまいます。借りたのが社員なのか管理者なのかによって、参照する外部キーを分ける必要があるためです。しかし、「どちらか一方だけが入る」という決まりは ER 図では描けず、図の外で別に書くしかありません。また、人事異動などで役割が変わるたびに、データを別のエンティティへ移すことになります。

この捉え方は、「管理者だけが備品を登録できる」といった、何をしてよいかの違いをスキーマの形に焼き込んでしまう危うさがあります。操作の違いをエンティティの分割で解決しようとすると、スキーマが無用に複雑になってしまいます。そのため、今回の台帳では利用者を 1 つのエンティティにし、何をしてよいかの違いについては 1-7「認可」で扱うことにしました。

この捉え方を採る要件は、社員と管理者が必ず別の人で、それぞれについて覚えることも違う台帳です。両者の役割が重なることが決してなく、管理する情報もまったく別であるなら、このようにエンティティを分ける形が適しています。

こう捉えることもできる 4 ── 1 回の貸出で複数の備品を借りる

4 つ目の別の捉え方として、台帳に求められる要件が変わった場合を考えてみましょう。たとえば、「プロジェクタとスクリーンを 1 回でまとめて借り、まとめて返したい」という要望が出たとします。

すると、貸出 1 つに対して備品がいくつも付くことになります。さらに、備品 1 つに対しても過去から現在までに貸出がいくつも付きます。このように、両側に相手がいくつも付くリレーションを多対多と呼びます。1 つの貸出に複数の備品があり、1 つの備品に複数の貸出があるという関係です。

多対多は、これまでに見てきたような外部キー 1 本では書けません。多対多を表すには、間にエンティティを 1 つ置き、1 対多 2 本に分ける必要があります。この間に置くエンティティを中間エンティティと呼びます。下の図では、貸出と備品の間に「貸出明細」を表す LOAN_LINE という中間エンティティを置いています。

図が画面より広いときは、横に送って読めます。

元の文字(Mermaid)を見る

erDiagram
  USER["USER 利用者"] {
    id user_id PK "利用者 ID"
  }
  LOAN["LOAN 貸出"] {
    id loan_id PK "貸出 ID"
    date lent_on "貸した日"
    id user_id FK "利用者 ID"
  }
  LOAN_LINE["LOAN_LINE 貸出明細"] {
    id loan_line_id PK "貸出明細 ID"
    id loan_id FK "貸出 ID"
    id item_id FK "備品 ID"
  }
  ITEM["ITEM 備品"] {
    id item_id PK "備品 ID"
  }
  USER ||--o{ LOAN : "借りる"
  LOAN ||--|{ LOAN_LINE : "含む"
  ITEM ||--o{ LOAN_LINE : "含まれる"

この図では、貸出明細 1 つが、貸出 1 つと備品 1 つを指します。こうすることで、貸出と貸出明細の間が 1 対多になり、備品と貸出明細の間も 1 対多になります。1 つの貸出に対して複数の貸出明細が付くため、1 回の貸出で複数の備品をまとめて借りられるようになります。備品と貸出の結びつきを、中間エンティティが仲立ちしている形です。

しかし、今回の備品台帳の今の要件ではこの形を採りません。1-2「エンティティ」で貸出が備品を 1 つ指すと捉えたので、まとめて借りるなら貸出が 2 つになるからです。もし中間エンティティを採ると、まとめて借りた複数の備品のうち、一部だけを先に返したときにどうするか、といった新しい決めごとが増えてしまいます。要件にない柔軟さを持たせようとすると、システムが複雑になり、管理の手間も増えてしまいます。

このように、サービスに求められている要件が変われば、採る ER が変わります。最初から複雑な構造を作るのではなく、現在の要件に合わせて適切な構造を選ぶことが大切です。

この章のまとめ

この章では、スキーマを図に描いて整理する ER 設計について学びました。ER 設計は、説明文をどのエンティティとリレーションで捉えるかを決めることです。今回描いた ER 図は、設計メモの一部になりました。

見てきた 4 つの別の捉え方は、どれも実際に描ける ER でした。採る ER は要件から決まり、要件が変われば変わります。どの設計にも利点と失うものがあり、要件に合っているかどうかが判断の分かれ目になります。ER 図を描くときは、選んだ形だけでなく、採らなかった捉え方と、どこで設計の判断が割れたかも書き残しておくとよいでしょう。そうすることで、後から要件が変わったときにも柔軟に見直すことができます。

次の章である 1-5「状態」では、貸出が「貸出中」から「返却済み」へ移り変わることを、どのように設計するかを扱います。