あおい
多対多の関係は、関係そのものを一つのレコードにするとモデル化しやすくなります。一人の受講者は複数の講座を受講でき、一つの講座には複数の受講者が参加できます。受講者の行に講座IDの一覧を保存するのではなく、受講登録テーブルに個々の組み合わせを記録します。
データベースの実装を決める前に、ER図を作成できるワークスペースで関係を整理しましょう。ここでは受講者と講座から始めて、中間テーブルを追加し、さらに見落としやすい「同じ講座が複数回開講される」という条件を扱います。
受講登録の一行が何を表すか決める
研修事業者が「データ基礎」と「ビジュアルプランニング」という講座を提供しているとします。受講者S01は両方を受講し、S02はデータ基礎を受講します。概念上は多対多の関係です。一人の受講者が複数の講座とつながり、一つの講座が複数の受講者とつながります。
リレーショナルデータベースの設計では、中間テーブルで個々の関連を表します。Microsoftの多対多の関係に関する説明も同じ考え方で、中間テーブルの各行に、両側のレコードを参照する外部キーを持たせます。業務上の関係は多対多でも、テーブル間の関係は一対多になります。
| student_id | course_id | 意味 |
|---|---|---|
| S01 | C10 | 受講者S01がデータ基礎に登録している。 |
| S01 | C20 | 同じ受講者がビジュアルプランニングにも登録している。 |
| S02 | C10 | 別の受講者がデータ基礎に登録している。 |
この三行は、受講者レコードの重複ではありません。受講者の情報はStudent、講座の情報はCourseに置き、Enrollmentに両者の関連を記録します。受講者名の誤字を直すために、受講登録を一件ずつ修正する必要はありません。
二つの一対多の関係を描く
StudentとCourseの間にEnrollmentを置きます。最初の単純化したモデルでは、関係を次のように定義します。
- 一つのStudentには、0件以上のEnrollmentを関連づけられる。
- 各Enrollmentは、必ず一つのStudentを参照する。
- 一つのCourseには、0件以上のEnrollmentを関連づけられる。
- 各Enrollmentは、必ず一つのCourseを参照する。
「0件」を含むことが重要です。受講者は講座に登録する前から存在でき、新しく作った講座にはまだ受講者がいない場合があります。一方、受講登録は両側の対象があって初めて意味を持つため、このモデルでは二つの外部キーフィールドを必須にします。
物理テーブルの図にStudentからCourseへの直接の線も残すなら、別の意味があることを明示する必要があります。そうでなければ、中間テーブルを使う関係に加え、直接つなぐ実装も指定しているように見えます。概念図では多対多の関連を示し、テーブル単位の図ではその表現方法を説明します。
関連に固有の項目をEnrollmentに置く
その値が説明するのは、受講者か、講座か、それとも「この受講者がこの講座に参加すること」かを考えます。多くのフィールドは、この問いで置き場所が決まります。
| フィールド | 配置先 | 理由 |
|---|---|---|
| student_name | Student | 受講登録ごとに名前が変わるわけではない。 |
| course_title | Course | 名称は講座の情報であり、一人の受講者の情報ではない。 |
| enrolled_at | Enrollment | この関連がいつ始まったかを記録する。 |
| enrollment_status | Enrollment | 一人の受講者が一つの講座を辞退しても、別の講座は受講し続ける場合がある。 |
| completion_result | Enrollment | 結果はこの受講者の参加に属し、講座の全受講者に共通するものではない。 |
中間テーブルは、二つのIDだけを持つとは限りません。この例では関連自体に状態と履歴があるので、受講登録を表すEnrollmentという業務上の名前が役立ちます。StudentCourseという名前も使えますが、レコードが何を表すのかが分からなくならないようにします。
ライフサイクルも明確にします。受講を辞退した場合、状態を更新するのか、別のイベントとして記録するのかを決めます。行を削除すると、関連の履歴も失われます。適切な保存方針はアプリケーション次第です。辞退はすべて削除だと暗黙に決めず、合意したモデルを図に示しましょう。
キーと一意性のルールを分けて決める
受講者と講座の組み合わせにつき一件だけ登録できるモデルなら、(student_id, course_id)を複合主キーにできます。組み合わせで一行を識別するため、各列が単独で一意である必要はありません。
別の方法は、独立したenrollment_idを主キーとし、受講者と講座の組み合わせに一意制約を付けることです。IDを追加しただけでは、S01がC10に二回登録されるのを防げません。重複する関連が無効なら、そのルールを明示します。
PostgreSQLの制約に関する説明では、主キー、一意制約、外部キーを区別しています。この例では、外部キーは参照先の受講者と講座が存在することを求め、一意性のルールは同じ関連の繰り返しを防ぎます。主キーは行を識別します。役割は関係していますが、置き換え可能なラベルではありません。
図はルールを記述するもので、実際に強制するのはデータベースです。「1」と書いた線や「一意」という注記があっても、実装に反映されていなければ、不正なデータの書き込みは防げません。
同じ講座を再び開講する場合を扱う
データ基礎が春と秋に開講されると、単純な組み合わせでは制限が厳しすぎます。S01が再受講できるなら、(student_id, course_id)は必要な一意性のルールを表していません。しかし、一意制約をすべてなくすと、同じ開講回への誤った二重登録も許してしまいます。
そこで、講座の一回の開講を表すCourseOfferingを導入します。講座C10に、春のO101と秋のO102があると考えます。各開講回は一つのCourseを参照し、一つのCourseには複数の開講回があります。Enrollmentは、StudentとCourseOfferingをつなぐように変更します。
受講登録の組み合わせは(student_id, offering_id)になります。S01–O101とS01–O102は別の登録なので、どちらも有効にできます。一人につき同じ開講回への登録は一件だけというルールなら、二件目のS01–O101は引き続き重複です。
開始日など、開講回ごとの情報はCourseではなくCourseOfferingに置きます。講座名は繰り返し提供する講座を説明し、開講回はいつ実施するかを説明します。一つの開講回で複数回の受講や受験を認めるサービスなら、登録のキーを繰り返し緩めるのではなく、その追加概念を明示的にモデル化します。
Boardmixで具体的なレコードを使って確認する
Boardmixのキャンバスに、Student、Enrollment、CourseOffering、Courseの四つのエンティティを描きます。EnrollmentをStudentとCourseOfferingの間に置き、CourseOfferingをCourseにつなぎます。フィールド名の横に主キーと外部キーを示し、受講者と開講回の組み合わせが一意であることを別の注記に書きます。
図の下には、S01–O101、S01–O102、もう一件のS01–O101という三枚の登録例を置きます。最初の二つは異なる関連、繰り返された組み合わせは選んだルールでは重複、と示します。フィールド名の一覧だけより、モデルがどう振る舞うかを話し合いやすくなります。
Boardmixでボードを開き、自分たちの業務のレコードをいくつか使って関係を確かめましょう。新しい親レコードに関連が一件もなくてもよいか、同じ組み合わせが再び現れてよいか、関連に固有の値はどこに属するかを考えます。
答えによって図は変わります。この研修事業者にとって重要な発見は、三つ目のテーブルが必要だということだけではありません。受講登録は、受講者を抽象的な講座に永続的につなぐのではなく、特定の開講回につなぐということです。
