mySQLで配列の外部参照は可能でしょうか？

Question

実現したいこと

どうにもググってもイメージが掴めなかったので。
初歩的な質問ですみません。

以下のようなイメージなのですが、
そもそも実装が可能かどうかと、
その場合の「人」テーブルをcreateする
sql文を教えていただけないでしょうか。

「フルーツ」

fruit_id	fruit_name
1	apple
2	lemon
3	orange

「人」

human_name	human_having_fruit
taro	1,2
hanako
takashi	1,3
yoko	3
yoshie	1,2,3

・いくつずつ持っているか
・どの順番で持っているか
などは実装できなくても大丈夫です。

Comments

[maisumakun]

「外部参照」ということは、「human_having_fruitに'4'とか'2,7'を入れようとしたらエラーになって失敗する」という動作が必要、ということでしょうか。

[shirai]

一応無くても仕様通りには動かせます。その可否によって実装可否は変わって来るのでしょうか。

[maisumakun]

そうですね、何のチェックもいらないなら「単なるカンマ区切りの文字列で入れる」という選択肢も存在します。

[shirai]

なるほど、確かにそうですね。ちなみにですがチェックがいる場合はどうすればいいでしょうか。

[maisumakun]

回答しましたが、「そもそもこのような形のテーブルを立てるのが適当でない」と考えます。

Answer 1

そもそも実装が可能かどうかと、

可能ですがおすすめはできません。「human_having_fruitは入れて出せればそれでOK、ここを対象に検索などは行わない」というのでなければリレーションを正規化したほうがいいでしょう。

human_name	fruit_id
taro	1
taro	2
takashi	1
takashi	3
yoko	3
yoshie	1
yoshie	2
yoshie	3

Comments

[maisumakun]

正規化した形であれば、fruit_idに外部キーを張る、human_nameとfruit_idの組み合わせでUNIQUEインデックスをかけるなど、データベースレベルでの制約をガンガンかけられます。

[shirai]

これが一番しっくり来ました。ありがとうございました。

Answer 2

create table human(hid int primary key,hname varchar(20));
insert into human values
(101,'taro'),
(102,'hanako'),
(103,'takashi'),
(104,'yoko'),
(105,'yoshie');

create table human_fruit(hrid int primary key auto_increment,hid int,fid int);
insert into human_fruit(hid,fid) values
(101,1),
(101,2),
(103,1),
(103,3),
(104,3),
(105,1),
(105,2),
(105,3);

select * from human as t1 left join 
(select hid,group_concat(fid) as human_having_fruit from human_fruit
group by hid) as t2 on t1.hid=t2.hid
order by t1.hid

Comments

[yambejp]

hanakoがnullを拾うためにはhuman管理用のテーブルと
human-furitをリレーションする中間テーブルは
分けないといけません

[shirai]

なるほど結合を使えば完璧なテーブルができるのですね。なかなか高難易度ですね。。。ありがとうございました。