カテゴリのDB設計について

お世話になります。
商品データとカテゴリテーブルをそれぞれ分けたデータベースを作っており、JOINで該当カテゴリ名を抽出しています。

しかし商品によって複数のカテゴリに属する（カンマ区切りで「1,2,3」というように現状格納しています）場合、それらのすべてを拾ってくることは可能でしょうか？

またカテゴリひとつひとつに対して商品データ内にカラムを用意するべきでしょうか？

select `shop`.no, left outer join list_category on `shop`.category=`list_category`.no （現状）

どうぞよろしくお願い致します。

No.4 ベストアンサー 回答者： yambejp 回答日時： 2013/12/02 11:37

作り直す気があるならこんな感じ

//元データ
create table shohin(sid int,sname varchar(30));
insert into shohin values(1,'パソコン'),(2,'洗濯機'),(3,'車');
create table category (cid int,cname varchar(30));
insert into category values('1','OA機器'),('2','家電'),('3','大型');
create table category_shohin (cid int,sid int);
insert into category_shohin values(1,1),(2,1),(2,2),(3,2),(3,3);

//商品ごとにカテゴリー名を羅列
select s.sid,s.sname,sub.cnames from shohin as s
inner join (
select sid,group_concat(cname) as cnames
from category as c
inner join category_shohin as cs
on cs.cid =c.cid
group by sid
) as sub
on s.sid=sub.sid

あとは最終形をどう見せたいかによります

この回答へのお礼

中間テーブルとはこのように使うのですね！さらにサンプルコードまで書いていただきありがとう御座いました！とても勉強になりました。
テーブルをより少なくすれば単に早くなるのではと勘違いをしていました。
実行そてみたら0.0010秒ほどで驚きです！

お礼日時：2013/12/02 13:31

No.3 回答者： Ogre7077 回答日時： 2013/12/02 11:08

商品とカテゴリは多対多関係なので、素直に中間テーブルを設けることをオススメします。

○ 物理設計
商品情報 ([商品ID], 商品名, ...)
商品カテゴリ ([商品ID], [カテゴリID])
カテゴリ情報 ([カテゴリID], カテゴリ名, ...)

○ 問い合わせ例
select A.商品名, C.カテゴリ名
from 商品情報 as A
left join 商品カテゴリ as B on A.商品ID = B.商品ID
left join カテゴリ情報 as C on B.カテゴリID = C.カテゴリID

○ 問い合わせ例の結果
ドラム, null ← カテゴリ無し
ピアノ, クラシック ← カテゴリ一件
ギター, クラシック
ギター, エレキ ← カテゴリ二件

No.2 回答者： yambejp 回答日時： 2013/12/02 10:10

別質問の例をもとにすればこんな感じ

//元データ
create table shohin (sid int,sname varchar(30),cid varchar(200));
insert into shohin values(1,'パソコン','_1_2_'),(2,'洗濯機','_2_3_'),(3,'車','_3_');
create table category (cid varchar(10),cname varchar(30));
insert into category values('1','OA機器'),('2','家電'),('3','大型');

//商品ごとにカテゴリー名を羅列
select s.sid,s.sname,sub.cnames from shohin as s
inner join (
select sid,group_concat(cname) as cnames
from category as c
inner join shohin as s
on s.cid like concat('%_',c.cid,'_%')
group by sid
) as sub
on s.sid=sub.sid

No.1 回答者： yambejp 回答日時： 2013/12/02 09:31

＞カンマ区切りで「1,2,3」

カテゴリー＝商品テーブルをつくってきちんと正規化する方が
データ管理はしやすいとおもいますよ

この方式でどうしてもやりたいなら
「,1,2,3,」と先頭と末尾にもカンマをつけて
カテゴリーテーブルと組み合わせるとき
商品テーブルのカラム like concat(',',カテゴリーテーブルのカラム,',')
のようにすることです

この回答への補足

早速の解答ありがとうございます。
やはりテーブルを設けた方が良いのですね。
例えば対応言語でカテゴリ分けする場合、日本語や英語でそれぞれフラグを立てるという形でしょうか？
カテゴリを追加するごとにカラムをひとつ増やすという認識でよかったでしょうか？

補足日時：2013/12/02 10:09