データベースで、親子のようで同一のリーレーションの表現の仕方

[墨汁]

データベースのリレーションを考えていて、どうしても正規化の仕方が分からない事例に直面したので質問させていただきます。



例えば、下のようにMenuというテーブルがあるとします。
着目していただきたいのは、カツ丼と天丼には大盛りが存在するが、親子丼には存在しないというところです。

Menu TABLE
id name price
1 カツ丼 700円
2 天丼 800円
3 親子丼 500円
4 大盛りカツ丼 800円
5 ビッグ天丼 1000円


ここで、検索という機能を考えます。しかし検索結果に上の5件が出るのは冗長であり、ユーザ視点からは「メニューにはカツ丼、天丼、親子丼の3品が存在し、なかには大盛り可能なものもある」と言うことふうに表示がしたいです。

検索結果
id name price has_big big_name big_price
1 カツ丼 700円 大盛りあり 大盛りカツ丼 800円
2 天丼　 800円 大盛りあり ビッグ天丼 1000円
3 親子丼 500円 大盛りなし NULL　　 NULL


ただ、検索結果はこのようになってほしいのですが、下のように更に売り上げテーブルを作りたく、その場合は最初のMenuテーブルのような形のほうが扱いが楽です。
例えば下の売上テーブルは「8/5に、カツ丼、ビッグ天丼、大盛りカツ丼の3品が売れた」ということを表します。

売上 TABLE
id menu_id selled_at
1 1 2017-08-05
2 5 2017-08-05
3 4 2017-08-05



今回お聞きしたいのはMenuテーブルの正規化方法です。
例えば、カツ丼と大盛りカツ丼って親子関係があると思うです。しかし、売上テーブルからみたらカツ丼と大盛りカツ丼は親子とか関係なく、あくまで「どっちもメニューの1つ」ということだけで扱いたいです。
単純に正規系を満たしたいだけなら、検索結果の例をそのままテーブル化してしまえばいいだけです。しかし、そうすると売上テーブルで何が売れたかをうまく管理できなくなります。（大盛りが売れたときにそのことを書き込めない）
売上テーブルで何が売れたかをうまく管理しつつ、メニューテーブルを正規化するにはどうすればよいでしょうか？
よろしくお願いします。


「Menuテーブルはリンク関係をお互いのタプルが持ったり持たなかったりする、明らかに第3正規形を満たさない（？）構造です。これをもっとも正規化された形にするにはどうすればよいでしょうか」です。

[墨汁]

投稿の最後に、下書きのときに書いていた二行が入ってしまいましたが、あまり関係なことなので流してください。
テーブルがどうにも見づらそうだったので、Excelで清書して画像化してみました。お役立てください。

[たいちう]

いろいろ方法はありますが、Menuに4つめのフィールド「big_id」を追加してはどうかと。
カツ丼の「big_id」は4、親子丼の「big_id」はnullです。
select文は判りますよね。

「big_id」を使う方法だと、今書かれている検索が簡単ですが、
将来、大盛りの他に特盛やメガ盛りを追加するならば、逆に「base_id」を
大盛りのメニューに設定するようにした方がスマートかも。

もっと複雑な要件の場合、親子関係を表現するテーブルを別に用意した方が
判りやすくもなるでしょうが、現在必要な機能のみを実現する最小限の方法がお勧めです。

[墨汁]

> たいちうさん
面倒な問題なのに読んで頂き、その上ご返信までしていただいてありがとうございます。

いろいろ方法はありますが、Menuに4つめのフィールド「big_id」を追加してはどうかと。
カツ丼の「big_id」は4、親子丼の「big_id」はnullです。
select文は判りますよね。

「big_id」を使う方法だと、今書かれている検索が簡単ですが、
将来、大盛りの他に特盛やメガ盛りを追加するならば、逆に「base_id」を
大盛りのメニューに設定するようにした方がスマートかも。

もっと複雑な要件の場合、親子関係を表現するテーブルを別に用意した方が
判りやすくもなるでしょうが、現在必要な機能のみを実現する最小限の方法がお勧めです。

これはMenuの主キーを「id + big_id」で複合主キーにするということでしょうか？たしかにそれなら検索もidだけですればよく、また売上テーブルもほとんど変更なく管理できそうです。
そうした場合について、1つ疑問が浮かんだのですが、このような場合にはどうすれば対処できますか？


・Menuテーブルに新たにinsertする場合に、主キーをどのように設定させればいいか？
Menuテーブルに、新たにうな丼をinsertする場合、複合主キーの状態だとautoincrementは効かないと思います。するとしたら最新情報のid, big_idのNULLで無い方のキーの値に+1して、それをうな丼のidにするということになるでしょうか。
方に1足したものを次の主キーにすると思いますが、そのような場合insertのクエリはどのように書けばよいでしょうか？

コード:

insert into Menu(id, big_id, name, price) values(?, ?, 'うな丼', 1000);

ORマッパでそのような制御を書いてもよいのでしょうが、トランザクションをしっかりやらないと、データに不整合（「最新情報のid, big_idのNULLで無い方のキー」を同時に取得されると、次の主キーが同じになって挿入されてしまう）が起こりそうなので、できればSQLだけで完結させたいです。

[たいちう]

> これはMenuの主キーを「id + big_id」で複合主キーにするということでしょうか？

主キーはidのみ、big_idは外部キーです。

[墨汁]

> これはMenuの主キーを「id + big_id」で複合主キーにするということでしょうか？

主キーはidのみ、big_idは外部キーです。

なるほど、勘違いしていました。それなら、挿入時に困ることもないし、「big_idで誰からも指されていないidはノーマルサイズである」と言えて検索もできそうですね。
実際にコードを書いて動かしたところうまく動いてくれました、基本の構成を変えないような形を教えていただけたので売上テーブルからの参照にも影響なさそうです。
たいちう様、どうもありがとうございました。


おまけに。
せっかくなので、将来このスレッドを見た人のためにsqliteで教えていただいたことを試してみたコードを貼っておきます。

コード:

sqlite> create table menu(id integer primary key autoincrement, name text, price integer, big_id integer);
sqlite> insert into menu(name, price, big_id) values('カツ丼', 700, 4);
sqlite> insert into menu(name, price, big_id) values('天丼', 800, 5);
sqlite> insert into menu(name, price, big_id) values('親子丼', 500, NULL);
sqlite> insert into menu(name, price, big_id) values('大盛りカツ丼', 800, NULL);
sqlite> insert into menu(name, price, big_id) values('ビッグ天丼', 1000, NULL);
sqlite> select * from menu where id not in (select big_id from menu where big_id not NULL);
1|カツ丼|700|4
2|天丼|800|5
3|親子丼|500|

[YuO]

解決していますが，別の考え方を。

「品目」とその「バリエーション」という考え方でメニューを構成する，という方法があります。
この方法だと，「特盛りカツ丼」を追加してもテーブル構造を変更する必用がありません。

ただ，品目とバリエーションの組み合わせでメニューの名前と値段が変わるので，本来であれば交差テーブルになるテーブルがメニューそのものになります。

コード:

CREATE TABLE item
(
    item_id INTEGER PRIMARY KEY,
    item_name TEXT NOT NULL
);
CREATE TABLE variation
(
    variation_id INTEGER PRIMARY KEY,
    variation_name TEXT NOT NULL
);
CREATE TABLE menu
(
    menu_id INTEGER PRIMARY KEY,
    item_id INTEGER NOT NULL,
    variation_id INTEGER NOT NULL,
    menu_name TEXT NOT NULL,
    price INTEGER NOT NULL,
    UNIQUE ( item_id, variation_id ),
    FOREIGN KEY ( item_id ) REFERENCES item ( item_id ),
    FOREIGN KEY ( variation_id ) REFERENCES variation ( variation_id )
);

INSERT INTO item ( item_id, item_name ) VALUES
    ( 1, 'カツ丼' ),
    ( 2, '天丼' ),
    ( 3, '親子丼 ');
INSERT INTO variation ( variation_id, variation_name ) VALUES
    ( 1, '普通盛り' ),
    ( 2, '大盛り' ),
    ( 3, '通常' ),
    ( 4, 'ビッグ' );
INSERT INTO menu ( menu_id, item_id, variation_id, menu_name, price ) VALUES
    ( 1, 1, 1, 'カツ丼', 700 ),
    ( 2, 2, 3, '天丼', 800 ),
    ( 3, 3, 1, '親子丼', 500 ),
    ( 4, 1, 2, '大盛りカツ丼', 800 ),
    ( 5, 2, 4, 'ビッグ天丼', 1000 );

「ビッグ天丼大盛り」のようなメニューがあり得るなら，menuから交差テーブルを分離して，variation_groupテーブルを追加し，
item_id → variation_group_id，menu_id → ( item_id, variation_id )の交差テーブルを作る形になるかと。

オフトピック

なんとなくアンチパターン臭がしている気がしますが，無視して進めます。

例えば、カツ丼と大盛りカツ丼って親子関係があると思うです。

たぶん，メニューの親子関係はないです。
概念上の「カツ丼」というのがあって，それの子として「カツ丼」と「大盛りカツ丼」があるのだと思います。