石頭閒語: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) 基本資料表格

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

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

查詢句

查詢全部選手與比賽項目的關聯資料。添加 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;

你好
一直想為學校用個運動會報名網路，但能力有限，看了你的文章覺得有點起頭，但有些不懂，
1.請問大大用的sql語法是用在什麼資料庫上的 ?
2.mysql可以加入關聯表格嗎?好像沒有什麼書介紹，
因為我只會用一點的MYSQL語法。
謝謝

1.除非特別說明，否則我只用 SQL 標準語法。(ANSI SQL Standard)。也就是說，各種SQL資料庫皆適用。但 MySQL 除外，它是SQL方言，我不保證能用。

2.如果 MySQL 對 SQL 標準的支援程度夠高，那就能用。你說書上沒介紹。我猜你指的是那些 PHP+MySQL 的書籍。那些書都不是 SQL 專門書籍，自然不會提到進階的SQL語法。

ps.我對 MySQL 有些偏見，我把它當成 "SQL方言" 看待。所以我在回答 SQL 相關事項時，通常會加上 "但不包括MySQL" 的但書。

姓名 :
Email :
URL :
驗證碼 :	( 請依顯示文字填入 ) 重新產生驗證碼
	記住我的資訊顯示我的圖像 ( 登入或申請個人圖像 )
回應內容 :

石頭閒語

2006年12月28日

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

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

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

查詢句

引用URL