効率的な方法を考えているのですが・・・

質問

php,mysqlの連携で中古車サイトを作っています。
まだテーブル校正の段階なのですが早速つまづいてしまいました><;

テーブルcar
id,name,price,maker_id,icon_flg(実際にフィールド数は30あります)
1,車名1,129.8,1,'アイコン'
2,車名2,109.8,2,'アイコン'
3,車名3,98.9,3,'アイコン'
---略---

テーブルmaker
id,name
1,メーカー1
2,メーカー2
3,メーカー3
---略---

テーブルicon(実際には20のデータがあります)
id,name
1,4WD
---略---
20,ETC

テーブルicon_image(実際には20のデータがあります)
id,path
1,img/01.gif
---略---
20,img/20.gif

テーブル校正は上記のような形式です。悩んでいる箇所はテーブルcarのicon_flgの値の書き方です。

考えた方法としては
1：『100100111』のようにして1であれば成立0であれば不成立
2：icon1～icon20までのフィールドを用意し1であれば成立0であれば不成立

この2パターンくらいしか思いつかなかったのですがどちらが一般的な方法なのでしょうか？

1の方法は5個程度の少ないアイコンであれば登録する際に判断も容易なのですが20個と多いので
後々苦労もありそうな気がし2の方法はアイコンのために20個のフィールドを増やすというのに抵抗が・・・

また、両者の場合ともINNER JOINでidを参照するという方法もどう判断していいものかが
判断できない(特に1の場合)のですがどのようなsql文を書けばいいのでしょうか？

php,mysqlどちらも5です。他の方法でも構いませんので詳しい方がいらっしゃいましたら
アドバイスをお願い致します！

回答No.6

nda23 · ベストアンサー

carとmakerは１：１の関係と思いますが、carとiconは１：ｎの関係
だと思います。「GROUP BY A.id」これはcar１台にまとめるという
意味なので、ｎ個のiconのどれか１個しか結び付きません。

E.icon_flag AS icon_flg
MAX(E.name) AS icon
FROM
((((car A INNER JOIN maker B ON A.maker_id = B.id)
INNER JOIN mission C ON A.mission_id = C.id)
INNER JOIN repair D ON A.repair_id = D.id)
INNER JOIN car_icon E ON A.id = E.car_id)
INNER JOIN icon F ON E.icon_flag = F.id
GROUP BY A.id,E.icon_flag

上記のように修正してください。
尚、GROUP BY で指定した項目以外は集計関数を使う必要があります。
makerやmissionも正しくはMAX(maker)などのように記述すべきです。
文法にウルサイSQLエンジンでは掲題のSQLは構文エラーです。

お礼

お返事ありがとうございます。
>GROUP BY A.idこれはcar１台にまとめるという・・・
確かに言われてみるとその通りで当然の結果でしたね^^;いろいろと試行錯誤はしましたが
おかげ様で思うような取得ができるようになりました。
参考書は読んでいるのですがこういった複雑な例の場合の記述がなく困り果てておりましたが
本当に助かり知識も深める事ができました！

回答No.5

nda23

>1や2であればテーブルiconのidに基づき取得するという流れで・・・
合っています。

お礼

何度もすみません^^;なかなか前に進む事ができずにいたので本当に感謝です。
早速試しているのですが思うような結果が取得できません。現在のsqlです。

$sql = 'SELECT
A.id,
A.maker_id,
A.name,
～略～
B.name AS maker,
C.name AS mission,
D.name AS repair,
E.icon_flg AS icon_flg
FROM
(car A INNER JOIN maker B ON A.maker_id = B.id)
INNER JOIN mission C ON A.mission_id = C.id
INNER JOIN repair D ON A.repair_id = D.id
INNER JOIN car_icon E ON A.id = E.car_id
GROUP BY A.id
ORDER BY A.create_date';

A.maker_idやA.mission_idなどについてはAの値をそれぞれのテーブルidに紐付け
思うような値で取得できているのですが現状のsqlで取得されるicon_flgの値は
最初のicon_flg値のみ取得されます。

car_iconは具体的に以下のテーブル校正です。

id,car_id,icon_flg
(1, 1, 1),
(2, 1, 3),
(3, 1, 4),
(4, 2, 7),
(5, 3, 8);

この場合上記のsqlではA.idが1の場合のicon_flgが1,3,4ではなく1のみとなりA.idが
1,2,3(アイコン設定しているもの)以外のデータは取得してくれません。

icon_flgで値を正確に取らなければiconやicon_imageと紐づく関係ももてないのですが
どのような記述をすればいいのでしょうか？

icon_flgについては配列のような形式で取得しなければ思うような事も実行できないと
いう事は想定できるのですが実現する方法がわからずです。。。

回答No.4

kalkichi

誤解を招く書き方をしてすみません。
マッピングテーブルとは特殊な機能ではなく、データとデータをマッピング（紐付ける）役割のテーブルという意味合いで使いました。
内容はnda23さんの回答と同じ意味です。

回答No.3

nda23

>1：『100100111』のようにして1であれば成立0であれば不成立
>2：icon1～icon20までのフィールドを用意し1であれば成立0であれば不成立
どちらも拙い方法です。前者はテーブルの結合が難しいし、後者は
結合式を20個（使用していないものも含む）全て記述しなければ
なりません。また、何より仕様変更で個数が変わった時の手当てが
大変です。
テーブルcarは固定項目だけにします。（maker_idまで）
テーブルcar_icon（id,car_id,icon_flag）を別途、作成します。
car_id で元になる車を示します。これで、１：ｎの関係が構築
でき、車１台につき、必要な数だけiconを持てます。
結合式も１個だけで済みます。

お礼

>テーブルcarは固定項目だけにします。（maker_idまで）
>テーブルcar_icon（id,car_id,icon_flag）を別途、作成します。
なるほど。その方法がよさそうですね^^振り出しに戻ってしまうのですがicon_flagの値は
1や2であればテーブルiconのidに基づき取得するという流れであってますでしょうか？

テーブルのイメージとしては以下のような形になるかと・・・
id,car_id,icon_flag
1,1,1
2,1,2
3,1,3
4,2,8
5,3,5
6,3,6

回答No.2

kalkichi

car_id,icon_idのマッピングテーブルを別に用意してはいかがでしょうか。
そうすればiconの種類は無限に増やせますし4WDでETCの車を抽出する。などの条件検索も簡単だと思います。

お礼

お返事ありがとうございます。
マッピングテーブルというのは初めて聞きました^^;早速『mysql マッピング テーブル』で
調べてみたのですが有力な情報がみつからずいまいち理解できていない状態です。

現状は考えていませんが装備による検索もありあえる機能ですのでこの方法で試してみたいのですが
どのようなsql文になるのか教えて頂けないでしょうか？

宜しくお願い致します。

回答No.1

qbr2

「アイコン」のためのフィールドを用意する必要があるのでしょうか？
「4WD」「ETC搭載」といった情報は、
元よりデータベースに格納されていると思うのですが、
そこからアイコン有無は判別できませんか？

既に持っている情報で判別できるにもかかわらず、
他に処理用のフラグを持つのは無駄ですし、データの不整合も起きてしまう可能性があります。