SQL テーブルを作るか？カラムに入れるか？

Question

###知りたいこと
ユーザーの閲覧履歴をどこに保存すべきかで悩んでいます。

・履歴は10件まで
・当人だけが見れるページに表示するときにしか取得しない

という設計なので、全ユーザーの履歴を保存したテーブルを作るべきか、それとも当人のレコードの１つのカラムに全てを入れるか、どちらにすべきか決められません。

自分が考えらえるメリットとデメリットを以下に書いていきますので、あっているところや間違っているところなど、そしてどうすべきかにについてアドバイス頂けませんでしょうか。

###全ユーザーの履歴を保存したテーブルを作る
テーブルを作るとしたら、すでにあるcontents（コンテンツのテーブル）に加えて、histories（閲覧履歴のテーブル）を作ることになります。

▼ contents（コンテンツのテーブル）

content_id	val
1	val1
2	val2
3	val3
4	val4
5	val5
6	val6

▼ histories（閲覧履歴のテーブル）

|history_id|user_id|target_content_id|visit_date|
|:--|:--|
1|3|6|2020-01-05|
2|1|3|2020-01-04|
3|3|2|2020-01-03|
4|2|8|2020-01-02|
5|2|4|2020-01-01|

このメリットは、SQLでINNER JOINをかければ次のように閲覧履歴が簡単に取得できる点です。

/*（例）user_id=3の閲覧履歴はこのように簡単に取得できる*/
select
  c.content_id,
  c.val,
  h.visit_date
from contents c
inner join histories h on
h.target_content_id = c.content_id
where
  h.user_id=3

ですが、デメリットとしては、データの保存が煩雑そうに思える点です。

例えば保存する前にまず当人のレコードを数えて、それが10件以内ならそのまま保存し、10件以上なら一番古いレコードを削除してから保存する。という処理が煩雑に思えます。

###当人のレコードの１つのカラムに全てを入れる
上記に比べて当人のレコードの１つのカラムに全てを入れることもできると思います。

users（ユーザーのテーブル）のhistoriesに入れるということです。

▼ users（ユーザーのテーブル）

content_id	name	his_histories
1	aくん	"[{"target_content_id":3,"date":"2020-01-04"}]"
2	bくん	"[{"target_content_id":4,"date":"2020-01-01"},{"target_content_id":8,"date":"2020-01-02"}]"
3	cくん	"[{"target_content_id":6,"date":"2020-01-05"},{"target_content_id":2,"date":"2020-01-03"}]"

こちらはSQLでINNER JOINとはいかず、まずhis_historiesを取得してそのIDを配列にしてカンマ区切りの文字列(2,3,4,6,8...)にして、SQLでcontents（コンテンツのテーブル）を対象にINで検索するという流れになると思うので、取得が煩雑そうなのがデメリットかと思います。

ですが、メリットとしてはデータの保存が楽そうで、his_historiesを取得したらPHPで10件かどうか数えて処理新しい履歴と合体した結果をhis_historiesに入れなおせばいいので、SQLの実行はそのINSERTの１回で済みそうに思えます。

....

長くなってしまってすみません。
このように考えていて、全ユーザーの履歴を保存したテーブルを作るべきか、それとも当人のレコードの１つのカラムに全てを入れるか、なかなか決めることができずにいます。

こういったケースではどのようにデータを保存すべきなのでしょうか。

アドバイス宜しくお願い致します。

Answer 1

自分なら他の回答にあるように追加は無制限に行って参照時に上位１０件だけ取得する方法を取るだろうなと思います。
消してしまった情報は二度と見れないので、安全側に倒すなら全部とっておくという意味で。

ただその履歴が更新される頻度やデータ量、または参照する頻度の問題で、DBへのアクセスがボトルネックになる事が事前にテストしてわかっているのであれば、参照用の別テーブルやDBを分けておくなり、割り切ってどうにか１０件に調整するようにバッチやトリガで削除するなり、色んなメンテナンス方法は考えられるでしょうけど。

Comments

[mousuguharudesu]

テストなどまったくしておらずとりあえず質問した状態でした。アドバイスありがとうございました。

Answer 2

メリット/デメリットについては、一度SQLアンチパターンに目を通されることをお薦めします。

敢えてアンチパターンを採用する場合も個人的にはあると思いますが、こういった質問をされるレベルであれば、正規化に則り、アンチパターンは回避された方が良いかと思います。

Comments

[mousuguharudesu]

初心者なのでアンチパターン回避でいきます。ありがとうございます。

Answer 3

特定のcontent_idに対して、target_content_idやdateが不定数だったり
するならJSON型で持つメリットはありますが、見た感じそうでもなさそうです。

Comments

[yambejp]

mysqlのようにカラムがJSON型で保持できるなら多少メリットは
でてきますが、そうでなくなにか文字列型のカラムにJSONを
流し込むだけでは検索性がよくないのでやめたほうがいいでしょう

[mousuguharudesu]

いまいちJSONにすべきシーンが理解できていないので、みなさん仰るようにテーブルをつくりカラムに入れるのはやめようと思います。ありがとうございます。

Answer 4

例えば保存する前にまず当人のレコードを数えて、それが10件以内ならそのまま保存し、10件以上なら一番古いレコードを削除してから保存する。という処理が煩雑に思えます。

取得の時に上位10件だけとるという風にしているのなら、別に「削除してから」は不要かと思います。
履歴であれば何かしらの時に運営側が参照して調査に使うことがあるかもしれませんし、残しておいて差し支えはないと思います。

「どうしても削除したい」というのでしたら日次か週次か月次かのタイミングで上位10件以外を削除するようなバッチ処理を置いておけば良いと思います。
取っておきたいなら別テーブルに移動とかファイルに落とし込んでおくとか言うことも考えられますし。
あくまで「参照するのは10件だけ」という形にしておくのが無難かと思います。

Comments

[mousuguharudesu]

たしかにわざわざ消す必要がなかったですね。貴重なアドバイスをありがとうございました。