あるテーブルの複数のレコードが別のテーブルの複数のレコードと関連付けられている場合は多対多のリレーションシップが発生します。たとえば、多対多のリレーションシップは顧客と製品の間に存在します。顧客はさまざまな製品を購入でき、製品は多数の顧客によって購入されます。
通常、リレーショナルデータベースシステムでは、2 つのテーブル間に多対多のリレーションシップを直接設定することはできません。請求書を参照する例を考えてみましょう。同じ請求書番号の請求書が多数あったとすると、顧客の誰かにその請求書番号を照会されても、どの番号のことかわかりません。請求書ごとに固有の値を割り当てるのはそのためです。
こうした問題を回避するために、結合テーブルと呼ばれる第三のテーブルを使用して、多対多のリレーションシップを 2 つの 1 対多のリレーションシップに分割することができます。結合テーブルの各レコードには、結合されている 2 つのテーブルの主キーの値を含む照合フィールドが含まれます。(結合テーブルでは、これらの照合フィールドは外部キーです。)結合されるいずれかのテーブルから結合テーブルのレコードが作成されると、これらの外部キーフィールドにデータが移入されます。
多対多のリレーションシップの代表的な例は、学生とクラスの間のリレーションシップです。学生は多数のクラスに登録でき、クラスには多数の学生が在籍できます。
次の例には、各学生のレコードを含む「学生」テーブルと、各クラスのレコードを含む「クラス」テーブルが含まれています。結合テーブル「登録」により、2 つのテーブルのそれぞれとの間に 1 つずつ、計 2 つの 1 対多のリレーションシップが作成されています。
「学生」テーブルの各学生は、主キーである学生 ID で固有に識別されます。「クラス」テーブルの各クラスは、主キーであるクラス ID で固有に識別されます。「登録」テーブルには外部キーの学生 ID とクラス ID が含まれます。
1. 上の例を使用して「登録」という名前のテーブルを作成します。これが結合テーブルになります。
2. 「登録」テーブルに「学生 ID」フィールドと「クラス ID」フィールドを作成します。
結合テーブルには通常、他のテーブルにあっても意味をなさない可能性があるフィールドが含まれます。「登録」テーブルには、クラスを開始した日を参照するための「日付」フィールドや、クラスを受講するために学生が支払った額の参照に使用する「学費」フィールドなどのフィールドを追加できます。
3. 2 つのテーブルの「学生 ID」フィールドの間にリレーションシップを作成します。次に、2 つのテーブルの「クラス ID」フィールドの間にリレーションシップを作成します。
この設計を使用すると、学生が 3 つのクラスに登録した場合は、「学生」テーブルにその学生のレコードが 1 つ作成され、「登録」テーブルには 3 つのレコード (登録したクラスごとに 1 つのレコード) が作成されます。
•結合テーブルからは、独立したリレーションシップを作成することなく、2 つのテーブルのフィールドとデータにアクセスできます。たとえば、学生が登録したすべてのクラスの一覧を表示する場合は、「学生」テーブルに基づくレイアウト上にポータルを作成します。「クラス」テーブルの関連レコードを表示するためのポータルを設計します。次に、「クラス」テーブルの該当するフィールドをポータルに追加します。「学生」レイアウトでレコードをブラウズすると、特定の学生が在籍しているすべてのクラスがポータルに表示されます。