最近更新: 2006-12-28

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;

?>
樂多舊網址: http://blog.roodo.com/rocksaying/archives/2606029.html

樂多舊回應
t37@mail.jrjh.ptc.edu.tw(teacher) (#comment-16294421)
Fri, 25 Apr 2008 18:21:16 +0800
你好
一直想為學校用個運動會報名網路,但能力有限,看了你的文章覺得有點起頭,但有些不懂,
1.請問大大用的sql語法是用在什麼資料庫上的 ?
2.mysql可以加入關聯表格嗎?好像沒有什麼書介紹,
因為我只會用一點的MYSQL語法。
謝謝
未留名 (#comment-16312167)
Mon, 28 Apr 2008 16:45:51 +0800
1.除非特別說明,否則我只用 SQL 標準語法。(ANSI SQL Standard)。也就是說,各種SQL資料庫皆適用。但 MySQL 除外,它是SQL方言,我不保證能用。

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

ps.我對 MySQL 有些偏見,我把它當成 "SQL方言" 看待。所以我在回答 SQL 相關事項時,通常會加上 "但不包括MySQL" 的但書。
t37@mail.jrjh.ptc.edu.tw(teacher) (#comment-17588581)
Sat, 04 Oct 2008 17:21:39 +0800
你好
上一次請教後,仍有不解,其中最後一段程式對照表 (array or map),
1.是不是還有一些對資料庫的存取沒有列出?2.$playerOfGames;$gameWithPlayers代表什麼呢?
3.還有$row = $stmt->fetch()是用在那一種資料庫的語法呢?
最後謝謝你上一次的回答。
未留名 (#comment-17644119)
Sun, 12 Oct 2008 09:24:59 +0800
那是一段範例碼,不是全文。至於 stmt->fetch() ,我沒明說,其實那是 PHP5 的 PDO 類別內容。它是抽象層次的資料庫處理類別,支援多種資料庫。