2つのテーブルからデータ抽出

質問

こんばんは。お世話になっております。
以前にも似たような質問を投函させて頂きましたが、知識が乏しく、適切なキーワードが思い当たらず、検索しても目的の情報に辿り着かずに投函させて頂きます。

Aテーブル
id　|　picture　
￣￣￣￣￣￣￣￣￣
1　｜　aaa.gif
2　｜　bbb.gif
3　｜　ccc.gif
4　｜　ddd.gif
5　｜　eee.gif

Bテーブル
id　｜　ma_id　｜　f_id　
￣￣￣￣￣￣￣￣￣￣￣￣￣
1　｜　　2　　｜　　3
2　｜　　3　　｜　　1
3　｜　　3　　｜　　4
4　｜　　5　　｜　　2

上記テーブルA・Bのidはindex
というようなテーブルがあり、テーブルBの、ma_idおよびf_idはテーブルAのidと同じ分類となっており、テーブルAのidを呼び出した際、
2だったら、3　ccc.gif　と、5　eee.gif
3だったら、2　bbb.gif　と、1　aaa.gif と、4　ddd.gif

のように、テーブルBにおいて、テーブルAで登録されているidが、テーブルBのma_id,f_idのどちらかに登録されていたら、その一方のidと同じ番号となるテーブルAのpictureを抽出したいと考えております。

$manage_id=$_GET["id"];
$sql= "select * FROM B INNER JOIN A ON B.ma_id = A.id OR B.f_id = A.id WHERE ma_id = '$manage_id' OR f_id = '$manage_id' order by (ma_id OR f_id) limit 0,3 ";

現在、以上のようなソースですが、アドバイスなど頂戴できたら幸いです。

尚、バージョンですが、開発時と運用時では違うバージョンになる可能性が大のため、テーブルの設定なども含め、バージョンに囚われない無難な設定法かどうか、もご指摘頂けたら幸いです。
お忙しい中恐縮ですが、宜しくお願い致します。

回答No.3

yambejp · ベストアンサー

>１・「検索をスムーズに行うためであれば、レコード数の多少は
>無視しても良い。」という考えで良いのか？

SQLの場合レコード数と言うのはそれほど重要視されません。
もちろんデータが多いほどスピードはおそくなりますが、
インデックスがうまくきいていれば２倍のレコードがあっても
スピードは１％以下の低下ですみます。
非効率にしか検索できないデータの持ち方をするくらいなら
レコード数を増やした方がましだと思います

>２・（改めて内容をお伝えしましたが、この内容の場合）上記で
>私が記載した（当初の倍になる）テーブルと、yambejp様のテーブルC
>とを比較した場合のメリット、デメリットは何処になるのか？

質問者さんのテーブルはid=1と2、3と4、（以下同）・・・が
ma_idとf_idを裏返してデータをいれていますね。
かと言って、1と2をひもづけるデータがありません。
なのでデータに不整合がおこりやすくなります。
もしこれを使うのであれば、ひもづけ用に以下のようなデータが
必要になります。
id　｜　ma_id　｜　f_id　| himo
￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣￣
1　｜　　2　　｜　　3　　|　1
2　｜　　3　　｜　　2　　|　1
3　｜　　3　　｜　　1　　|　2
4　｜　　1　　｜　　3　　|　2

しかしこうなると、Cテーブルとほぼ同じようなデータ構造になり
データ数がすくなくなるCテーブルの方が若干有利かもしれません。

さて、前回の回答への補足をみるかぎりmaとfは別に主従関係に
ないようです。（どちらかがmaである必要がないという意味）
そうなると以下のようにするとさらに効率化します。

Dテーブル
id　aid
1　　2
2　　3
3　　3
4　　5
1　　3
2　　1
3　　4
4　　2

SELECT Aテーブル.id,Aテーブル.picture
FROM Cテーブル AS C1
INNER JOIN Cテーブル AS C2 ON C1.id=C2.id
AND C1.aid != C2.aid
AND C1.aid={$manage_id}
INNER JOIN Aテーブル ON Aテーブル.id=C2.aid

この場合プライマリはid,aidにつけることになりますね。

お礼

yambejp様

お返事おそくなり申し訳ありませんでしたっ！
既にお礼と共に「解決済み」としていたつもりでしたが、只今「質問投函から2週間が経ちました」と教えてgooからメールが届き、「解決済み」はおろか「お礼」さえもしていなかった事に気が付きました。

ご親切、丁寧なyambejp様に無礼な態度をお許し下さい。
yambejp様のご指導をもとに、理解を一つ深める事ができました。
有難う御座いました。

回答No.2

yambejp

全体像がいまいち見えないのでなんともいえませんが、
例えば以下のようにするだけでも検索性はあがります。

Cテーブル
id　 aid kind
1　　2　 ma
2　　3　 ma
3　　3　 ma
4　　5　 ma
1　　3　 f
2　　1　 f
3　　4　 f
4　　2　 f
※id,kindにプライマリ属性をつけるとよいでしょう。
aidにはインデックスを振っておきます。

SELECT Aテーブル.id,Aテーブル.picture
FROM Cテーブル AS C1
INNER JOIN Cテーブル AS C2 ON C1.id=C2.id
AND C1.kind != C2.kind
AND C1.aid=2
INNER JOIN Aテーブル ON Aテーブル.id=C2.aid

※きちんと検証しているわけではないのでバグがあったら
お許しください

お礼

yambejp様

夜遅くまで有難う御座います。
アドバイスいただいた内容を基に、確認をしながら試してみたいと思います。
改めてご報告させて頂きます。まずはご挨拶まで・・有難う御座いました。

補足

yambejp様

こんにちは。あれからyambejp様からご提示していただいたテーブルCの構造を考えていました。
スキルが乏しく鵜呑みにしがちな私ですが、理解を深めたいと思い、確認も含めてお伺いしたいと思います。何処か可笑しなところがあったらご指摘下さい。

今回の内容というのも、SNSで多く使われている「友達の画像」を自分のページに表示。といった機能を、勉強のつもりで（個人レベルで会員は募っても高が知れてますし…）作っているのですが、質問にあるテーブルAを会員テーブルとして設定しており、会員間の友達関係を記憶するものとしてテーブルBを設定しております。

流れとして、会員のページを表示させたときテーブルBを参照し、このページの持ち主であるidを、ma_idおよびf_idから探し出し、同じ番号があったら、その相手の番号を取得し、それを基にテーブルAのidから同じ番号を探す。といった感じです。
（ma_idにidと同じ番号があったら、f_idと同じ番号をテーブルAのidから探し出し、そのレコードにあるpicutureというカラムの内容を表示。）

当初、このテーブルBを設置したとき、
id　｜　ma_id　｜　f_id　
￣￣￣￣￣￣￣￣￣￣￣￣￣
1　｜　　2　　｜　　3
2　｜　　3　　｜　　2
3　｜　　3　　｜　　1
4　｜　　1　　｜　　3
5　｜　　3　　｜　　4
6　｜　　4　　｜　　3
7　｜　　5　　｜　　2
8　｜　　2　　｜　　5　　

と、友達登録が成立したとき、ページの管理者であるidを全てma_idとして登録し、友達となるidをf_idとしてその都度登録するようにしていました。（このテーブルのidはindexとしてだけ設置）
すれば、ma_idからページの持ち主であるidを探し出す事で、その相手であるf_idだけを抽出しようかな・・と。

しかしこれだと会員レコード（A)数に対して、友達関係レコード（B)数が約倍の数になってしまう事に気が付き、レコードA・B共に同じレコード数を保つための処置として？今回の質問のように、
レコードBにある、ma_idまたはf_idのいずれかに該当するidがあった場合は、その一方のidをレコードAから参照する。といった流れを考えての投函でした。

些か回りくどい説明になってしまいましたが、「正規化」のご指摘を受け考え改めているところですが、
『全体像がいまいち見えないのでなんともいえませんが、』と前置きはあったものの（私説明不足なので当然です！）、yambejp様からご教授いただいたテーブルを考えていた場合、当初の設定同様、会員テーブル（A）に対して、レコード数が倍になってしまうことに（今更ですが・・汗）気付き、

１・「検索をスムーズに行うためであれば、レコード数の多少は無視しても良い。」という考えで良いのか？

２・（改めて内容をお伝えしましたが、この内容の場合）上記で私が記載した（当初の倍になる）テーブルと、yambejp様のテーブルCとを比較した場合のメリット、デメリットは何処になるのか？

を、経験豊な方からご指導（およびご指摘）を頂戴できればと思っております。

些か長文、かつ上手く説明できていないところも御座いますが、引き続きご指導いただければ幸いです。お忙しい中恐縮ですが、宜しくお願い致します。

回答No.1

yambejp

DBの設計に問題がありそうですね。
正規化について学習なさった方がよろしいかと思います。

このまま運用すると早い段階で破綻しそうな気がしますが
とりあえずこのパターンでも動かすのであれば、以下のような
ものでいけると思います。
テンポラリをつかっていいならもう少し効率的なやり方も
できるかもしれません。

$manage_id=$_GET["id"];
$sql= <<<eof
select IF(ma_id={$manage_id},f_id,ma_id) AS ID,IF(ma_id={$manage_id},A2.picture,A1.picture) AS picture
FROM Bテーブル AS B
INNER JOIN Aテーブル AS A1 ON A1.ID=B.ma_id
INNER JOIN Aテーブル AS A2 ON A2.ID=B.f_id
WHERE {$manage_id} IN (B.ma_id,B.f_id)
ORDER BY ID
eof;

お礼

yambejp様

こんばんは。いつも大変お世話になっております。

>DBの設計に問題がありそうですね。
>正規化について学習なさった方がよろしいかと思います。

ご指摘有難う御座います。こんなこと言うのも可笑しな話ですが、設計に不安もあったので、このようなご指摘に感謝いたします。

そこで、今回の正規化についてなのですが、テーブルAにあるidと関連するカラムを同じ名称に設定すれば良い・・だけの事ではありませんよね？ma_idとf_idとがあるわけですし・・・（ma_idもf_idも、共にテーブルAのidに登録された番号が登録されています。）

正規化についてきちんと学んだ方が良いとご指摘を頂戴しておりますが、その取っ掛かりとして今回のケースを例にアドバイスいただければ有難いのですが・・・（丸投げのつもりではなく、「あ、なるほど！」といった切欠が欲しいのです。）
お忙しい中、誠に恐縮ですが宜しくお願い致します。

補足

補足欄から失礼します。質問の補足として。

テーブルAはメンバー管理のテーブルとなっており、idはindexとしておりますが、同時に各メンバーのIDとしても利用しています。
以前、「複数のテーブルから…」として投函させて頂いたとき、ソースの誤りをyambejp様からご指摘頂き、その時は問題なく対処できたのですが、そのときの設定は、以下の様にテーブルBにデータを登録する際、２つのパターンを登録したものでした。

Bテーブル
id　｜　ma_id　｜　f_id　
￣￣￣￣￣￣￣￣￣￣￣￣￣
1　｜　　2　　｜　　3
2　｜　　3　　｜　　2
3　｜　　3　　｜　　4
4　｜　　4　　｜　　3

しかしながら、登録が増えるごとに倍々で増えるため、フィールド数が少なくとも如何なものか？と考えるようになり、今回のように改めなおした次第です。