組み合わせのマッチィングを過去のものと重複させない

質問

ある研究の素材マッチングで過去に比較したものを重複させないでテストする組み合せを表示するプログラムを作りたいと思っています。

DB table tests

id material test1 test2 test3

1 | A | 1 | 1 | 0
2 | B | 0 | 1 | 0
3 | D | 0 | 0 | 1
4 | E | 1 | 0 | 0
5 | F | 0 | 0 | 0
6 | G | 0 | 1 | 1

このテーブルによると素材Aはtest1とtest2に参加しておりそのどちらかに参加している素材B,素材Gとは再び同じtestに参加する必要がなく
test1とtest2に参加していない素材D,素材Fはtest4で素材Aとマッチさせることができるという事を表示できるものを作りたいです。

考えましたのは、
$sql='SELECT material,test1,test2,test3 FROM tests WHERE id=?';
$stmt=$dbh->prepare($sql);
$stmt->execute(array($id));
$rec=$stmt->fetch(PDO::FETCH_ASSOC);

$material=$rec[material];
$test1=$rec[test1];
$test2=$rec[test2];
$test3=$rec[test3];

これでどの素材がどのテストに参加したかを求めその次のtest4でいままでマッチしていない素材のグループを作るには

$material=$_POST[material];

$sql="SELECT material FROM tests WHERE $material !=? and test1 !=? and test2 !=? and test3 !=?";
$stmt=$dbh->prepare($sql);
$stmt->execute(array($material,$test1,$test2,$test3));
while( $rec=$stmt->fetch(PDO::FETCH_ASSOC) );
$material=$rec[material];
{

echo $material;
echo '<br>';

}

と言う形で書けるかと思うのですが、この方法ですとtest（testと素材の数は次々増えていきます）が増えるたびにデータベースのカラムを足しPHPのsql文を書き足さないといけなくなります。

カラムを増やしていく事があまりいい事ではない様に解釈しています。また都度PHPを書き足すのもどうかという事で設計上改善が必要かと思っていますがその改善案にたどり着くことができません。

なにかより良い設計（PHPの書き方、およびDB設計）がありましたらどうぞどなたかお知恵をお貸しください。
どうぞよろしくお願いいたします。

回答No.2

aa_akiya · ベストアンサー

やはり勘違いしていたようですね申し訳ないです。

NO.1のお礼コメントにあるデータをもとに考えます。

1.主体となる素材に紐づくテストデータをまずは抽出する
2.「1」の中に存在しない素材を抽出する
3.主体となる素材は抽出対象としない
後述するSQLの考え方は上記のようなものです。

以下のようなSQLで求めるものを抽出できると思いますがいかがでしょうか。

■素材Aを主体としてすべてのテストについて被りのないものを抽出したいとき

SELECT material.name
FROM material
WHERE NOT EXISTS (SELECT * FROM material_test WHERE material.id = material_test.material_id AND test_id IN (SELECT test_id FROM material_test WHERE material_id = 1))
AND material.id <> 1

実行結果：D F H(3行)

■素材Aを主体としてtest1について被りのないものを抽出したいとき

SELECT material.name
FROM material
WHERE NOT EXISTS (SELECT * FROM material_test WHERE material.id = material_test.material_id AND test_id IN (SELECT test_id FROM material_test WHERE material_id = 1 AND test_id IN (1)))
AND material.id <> 1

実行結果：B D F G H(5行)

■素材Dを主体としてtest1～test3について被りのないものを抽出したいとき

SELECT material.name
FROM material
WHERE NOT EXISTS (SELECT * FROM material_test WHERE material.id = material_test.material_id AND test_id IN (SELECT test_id FROM material_test WHERE material_id = 3 AND test_id IN (1, 2, 3)))
AND material.id <> 3

実行結果：A B E F(4行)

お礼

aa_akiya様

おお！
有難うございます。
これぞ求めていた内容です。

今後勉強しないといけない項目もわかり本当に助かりました。

大変感謝いたします。
どうも有難うございました。

回答No.1

aa_akiya

お気づきの通り、データが増えるにしたがってテーブルのカラムが増える設計は、プログラムの改修が煩雑になるので控えた方が良いと思います。
より良い設計のために「データベースの正規化」について学習することをお勧めします。

■materialテーブル
id | name
1 | A
2 | B
3 | D
4 | E
5 | F
6 | G

■testテーブル
id | name
1 | test1
2 | test2
3 | test3
4 | test4

■material_testテーブル
material_id | test_id
1 | 1
1 | 2
2 | 2
3 | 3
4 | 1
6 | 2
6 | 3

■test1、test2に参加していない素材を抽出するSQL
SELECT material.name
FROM material
WHERE NOT EXISTS (SELECT * FROM material_test WHERE material.id = material_test.material_id AND test_id IN(1, 2))

実行結果：D F(2行)

test2、test3に参加していない素材を抽出する場合は「IN(1, 2)」の部分を「IN(2, 3)」とします。
test3以前に参加していない素材を抽出する場合は「IN(1, 2)」の部分を「<= 3」とします。

仕様の解釈が間違っていたらごめんなさい。

お礼

aa_akiya様

ご回答どうも有り難うございます。

「データベースの正規化」勉強します。

仕様についてですが、お答えいただいたのはtestを主体にに考えtest1,test2のどちらでもマッチングされていない素材をtest3で集める仕様かと解釈します（ちゃんと把握していなければ申し訳ありません）。
ここで求めたかったのは一つの素材を主体として考えその素材がマッチングしてないものを導き出したいというものです。質問事態が要領を得ていなくて申し訳ありません。

頂いた回答を基に素材Hを足し、Hはtest3に参加したとしますと

■materialテーブル

id | name
1 | A
2 | B
3 | D
4 | E
5 | F
6 | G
7 | H

■testテーブル

id | name
1 | test1
2 | test2
3 | test3
4 | test4

■material_testテーブル

material_id | test_id

1 | 1
1 | 2
2 | 2
3 | 3
4 | 1
6 | 2
6 | 3
7 | 3

Aはtest1,test2に参加している。
Dはtest3に参加している。
Hはtest3に参加している。

AとDは同時に参加しているtestがないのでtest5以降のtestでマッチングできる。

AとHは同時に参加しているtestがないのでtest5以降のtestでマッチングできる。

DとHはtest3で同時に参加しているのでDやHを主体として考えるとtest3以降DとHのマッチングはできないが、Aを主体として考えた場合たとえDとHがマッチング済みでバッティングすることになってもAとD,AとHのマッチングがtest5できるとすると考えるものです。

頂いた回答をもとに自分自身でも考えてみましたが、今一つピンと来ていません。

もしさらにご助言いただけたらとてもうれしいです。

最後にご返答を頂いてから内容を理解するのに時間を要しお礼をお送りするのが遅れて申し訳ありません。
有難うございました。