laravelの中間テーブルの設計

前提

laravel学習中の初心者です。よろしくお願いします。
例として、ユーザーテーブル(users)とテスト(tests)テーブルがあります。
多くのユーザーが多くのテストを解きます。テストを解くと点数がつきます。
また、ユーザーは解いたテストにメモを残したい場合があるとします。
ユーザーとテストのレコード数はいづれも膨大にあるとします。

実装したいこと

ユーザーの解いたテストの点数やメモをデータベースに保存したいです。

考えてること

3つの方法を考えました。

1つは、user_testという1つの中間テーブル（点数カラム、メモカラムを追加)を作る方法です。
疑問点として、このテーブルのメモカラムはデフォルトとしてnullが入ると思うのですが、膨大なレコード数になるであろうこのテーブルに膨大なnullの情報は無駄な気がしました。

2つ目は、2つの中間テーブル(点数テーブル、メモテーブル)を作る方法です。(それぞれ点数カラム、メモカラムを追加)
疑問点として、laravelの中間テーブルの命名規則に反していると思うのですが、大丈夫なのでしょうか。attach, sync, detachなどの操作は可能ですか？

3つ目は、中間テーブルではないのですが、2つのテーブル(点数テーブル、メモテーブル)を作る方法です。(それぞれ主キーはオートインクリメントで複合ユニークキー(user_idとtest_id)を設定)
疑問点は、オートインクリメントが無駄であることと、プライマリーキーを使ったアクセスではないためアクセスが遅くなるのではないかと思いました。
通常のコントローラーを作ってデータへの操作が簡単そうだったので選択肢に入れました。

良い方法を教えていただきたいです。

補足情報（FW/ツールのバージョンなど）

必要でしたら追記します。

mix-peach

2022/01/05 05:57

質問です。ユーザーは、どのテストでも自由に解くことができるのですか？それとも、管理者などによって、ユーザが解くテストは、予め決められているのでしょうか？

popcor

2022/01/05 13:33

ご質問ありがとうございます。ユーザーは、全てのテストを自由に解くことができます。制限はありません。

行動規範の内容に同意します

回答2件

ベストアンサー

■【案２】について

2つの中間テーブル(点数テーブル、メモテーブル)を作る方法

テーブル名は、明示的に指定すればいいだけなので、
中間テーブルの命名規則を守れないこと自体は大した問題ではありません。

ただ、AとBのテーブルをつなぐ中間テーブルが2つある、というのが、あまり聞かない構成かなぁという印象です。

中間テーブルを２つ使いたいなら、Modelに中間テーブルを用いたリレーションの記述を「２つ書く」ことになります。
Modelにそのリレーションの記述さえあれば、attach, sync, detachなどの操作を行うことは可能ですが、「リレーション記述ごとに操作を行う」ので、２個あるならそれぞれを操作することになり、手間ともいえます。

それに、同じ「テーブルAとテーブルBをつなぐ似たような役割のリレーション」なのに、
経由する中間テーブルによって、リレーションが存在したりしなかったりするわけですから、
ややこしいですよね・・？

こういう仕様は、バグの温床になり得るので、私はおすすめしません。

■【案２】よりは【案１】

user_testという1つの中間テーブル（点数カラム、メモカラムを追加)を作る方法

先に述べた理由により、

・大量のnullデータという無駄を省くために、ハイリスクで複雑な構成・ロジックにするか
・nullデータはできるけれど、リスク低めの簡素な構成・ロジックにするか

の2択であれば、「後者を選びたい」ので、２よりは１の方が断然良いかなと。

■（私的には）【案３】がベスト

中間テーブルではないのですが、2つのテーブル(点数テーブル、メモテーブル)を作る方法

（※ご注意※　popcorさんの【案３】を、そのまま推奨しているのとはちょっと違いますので、悪しからず）

提示いただいた仕様で考えると

１）テストを解いて、「結果（点数）」を登録。
２）その結果に対して、「メモ」を登録（することがある）。

フロー的には、こんな感じでしょうか？

質問したように

管理者などによって、ユーザが解くテストは、予め決められている

のであれば、テスト結果がなくても関係性が必要なので、
「ユーザー」と「テスト」を直接リレーションさせたいと考えますが、

ユーザーは、全てのテストを自由に解くことができます。制限はありません。

とのことですので、
「ユーザー」と「テスト」は「テスト結果ありき」の関係性になりますよね。
それなら、
「テスト結果」というテーブルを作って、それに対し「ユーザーテーブル」「テストテーブル」の2つの親テーブルがある
というのが、一番簡潔な構成ではないかと思います。

●ユーザーテーブル
users { id, name, ...} 

●テストテーブル
tests { id, title, ...} 

●テスト結果テーブル
scores { id, user_id, test_id, score, (memo,) ...}


なお、メモを別のテーブルにするのであれば、scoresにリレーションするのでも良いかも
●テスト結果メモテーブル
memos { id, score_id, memo}

テストにメモを残すような機能があるなら、
「テストをいつ解いたのか」「メモをいつしたのか」という情報も大事なのでは？
と思うのですが、中間テーブルにしてしまうと、これらも全て追加カラムとして扱わなければならず、かなり手間です。

他にも・・・
例えば、これは仕様次第の話ですが、
問題を解くサービスであれば「繰り返し解く」といった機能が備わっていることも多いと思うのですが、結果を中間テーブルに保存する形だと、1つのテストに1つの結果しか残せないことになるので、過去の点数を履歴として残せません。
（※これはscoresのuser_id, test_idを複合ユニークキーにした場合でも、同様の問題が発生しますので、繰り返しますが、仕様次第の話です。）

それから、laravelで、標準のEloquentモデルを使って独自カスタマイズをすることなく、DBのデータを取り扱いたいのであれば、プライマリーキーに複合キーは指定できません。
フローの中に、「テスト結果」テーブルや「メモ」テーブルのデータ更新があり得るなら、autoincrementのidは絶対に必要なので、無駄ではありません。

なお、リレーションのhasManyThroughで、scoresを中間テーブルの様に使って、usersとtestsを直接つないでいるかのように見ることが可能ですので、「結果を残すテーブルが中間テーブルであること」にこだわる必要はないだろう・・という判断です。

長くなってしまいましたが、以上です。

実際に開発するにあたっては、細かいシステム要件や、
膨大なデータ量前提で譲れない点などもあるのかと思いますので、
ひとつの参考程度に読んで頂ければと思います。

投稿2022/01/06 09:55

mix-peach

総合スコア1910

popcor

2022/01/06 15:13

ご回答ありがとうございます。大変参考になります。 >>管理者などによって、ユーザが解くテストは、予め決められている >のであれば、テスト結果がなくても関係性が必要なので、 >「ユーザー」と「テスト」を直接リレーションさせたいと考えますが、 >>ユーザーは、全てのテストを自由に解くことができます。制限はありません。 >とのことですので、 >「ユーザー」と「テスト」は「テスト結果ありき」の関係性になりますよね。 >テストにメモを残すような機能があるなら、 >「テストをいつ解いたのか」「メモをいつしたのか」という情報も大事なのでは？ >と思うのですが、中間テーブルにしてしまうと、これらも全て追加カラムとして扱わなければならず、かなり手間です。ご指摘頂いた点（中間テーブルが必然な用件ではないことや構成・ロジックの簡潔さ）について理解いたしました。他にも、オートインクリメントへのご指摘やhasManyThroughの仕様など、laravelの仕様について教えていただきありがとうございます。曖昧な質問だったのにも関わらず、分かりやすいご回答を頂き感謝いたします。

行動規範の内容に同意します