Case study - 運動會報名表單, 多對多關聯

欲建立一個運動會報名表單，一位選手可報名參加一個以上的項目 (如100公尺、200公尺等) ，資料庫表格應如何設定？

我們通常不會在一個選手表格中建立多個比賽項目的關聯欄位，像 game1, game2, game3 這種欄位設置就不太適當。第一、如果多數選手只參與一個項目，則剩下的 game2, game3, etc. 欄位就閒置了，佔用磁碟空間。第二、限定了可參加比賽項目數的上限，如果我只定義到 game3 ，則一位選手最多只能參加 3 個比賽項目。第三、只能用複雜且僵化的查詢語句，例如 SELECT * FROM "Players" INNER JOIN "Games" ON "Games".id = "Players".game1 OR "Games".id = "Players".game2 OR "Games".id = "Players".game3; ，欄位愈多則 OR 條件 串的愈長。當然，如果程式需求限定每位選手至少參加一個項目，最多參加 2 個，那麼用這種方法倒也無妨。

一般這種需求常用多對多關聯處理。首先需要兩個資料庫表格儲存選手及比賽項目的基本資料。其次，建立多對多關聯表格，記錄選手及比賽項目的關聯性。

選手 (Players) 與比賽項目 (Games) 基本資料表格

CREATE TABLE "Players" (
    id INT NOT NULL,
    name VARCHAR(32),
    PRIMARY KEY (id)
);

CREATE TABLE "Games" (
    id INT NOT NULL,
    name VARCHAR(32),
    PRIMARY KEY (id)
);

選手與比賽項目的關聯表格

CREATE TABLE "PlayerOfGame" (
    "playerId" INT NOT NULL REFERENCES "Players" (id) ON DELETE CASCADE,
    "gameId" INT NOT NULL REFERENCES "Games" (id) ON DELETE CASCADE
);

此處使用了外部鍵值條件約束，串聯選手和比賽項目的紀錄刪除動作，如此當我們自 Players 或 Games 表格中刪除紀錄時，資料庫系統就會自動刪除 PlayerOfGame 表格中的關聯紀錄。在關聯表格中，常用這種串聯動作以簡化程式碼中的 SQL 操作動作。

查詢句

SELECT "PlayerOfGame".*,
    "Players".name AS "playerName",
    "Games".name AS "gameName"
FROM "Players"
INNER JOIN ("PlayerOfGame"
    INNER JOIN "Games"
    ON "PlayerOfGame"."playerId" = "Games".id
)
ON "PlayerOfGame"."playerId" = "Players".id
-- WHERE "Players".id = @id
-- WHERE "Games".id = @id

查詢全部選手與比賽項目的關聯資料。添加 WHERE 條件句便可查詢特定選手參加的比賽項目或是比賽項目中有多少位選手報名參加。

我們還可將全部資料的查詢結果再整理成對照表 (array or map) 。例如在 PHP 中可以處理成兩個關聯陣列，如下例。

<?php
$playerOfGames = array();
$gameWithPlayers = array();
while($row = $stmt->fetch()) :
    if (!isset($playerOfGAmes[$row['playerId']])) :
        $playerOfGames[$row['playerId']]['name'] = $row['playerName'];
        $playerOfGames[$row['playerId']]['games'] = array();
    endif;
    array_push($playerOfGAmes[$row['playerId']]['games'], $row);

    if (!isset($gameWithPlayers[$row['gameId']])) :
        $gameWithPlayers[$row['gameId']]['name'] = $row['gameName'];
        $gameWithPlayers[$row['gameId']]['players'] = array();
    endif;
    array_push($gameWithPlayers[$row['gameId']]['players'], $row);
endwhile;


foreach ($playerOfGames as $playerId => $player) :
    echo '選手', $player['name'], '之報名項目有: ';
    foreach ($player['games'] as $game) :
        echo $game['gameName'], ',';
    endforeach;
    echo "<br/>\n";
endforeach;

foreach ($gameWithPlayers as $gameId => $game) :
    echo '比賽項目', $game['name'], '之報名選手有: ';
    foreach ($game['players'] as $player) :
        echo $player['playerName'], ',';
    endforeach;
    echo "<br/>\n";
endforeach;

?>