投稿者を消しても投稿自体は残す際のデータベース設計

Question

商品などのレビュー内容(reviews)とその投稿者情報(users)を別々のテーブルに保管してあったとして、ユーザーが退会などでいなくなった場合でも、投稿した内容自体は消さずに名前部分等を（退会済みユーザー）などのように表記しようと考えています。

その際に、レビュー表示時に投稿者が NULL のときの条件分岐処理をアプリケーション側に直接書いてしまうか、「退会済みユーザー」というユーザーを作り、ユーザーを削除した際は代わりの投稿者として割り当ててしまう方法が思いつきます。

前者は全ての表示部分に処理を加える必要があり、後者はユーザー一覧表示時などに「退会済みユーザー」という名前のダミーユーザーを除外する必要があります。
このようなケースでは一般的にどういった方法がとられるのでしょうか？

あるいはユーザーの退会処理をソフトデリートにしてしまって削除フラグが立っているものに対してデータベース側でうまく処理した上でSELECTする方法などあるのでしょうか？

制作物はウェブサイトでデータベースはMySQLを想定しています。
よろしくお願いします。

Answer 1

「誰が書いたか」という情報はあとあとまで必要になる可能性があります。
極端な話ですが、投稿内容が名誉毀損だったり著作権侵害だったりと刑法に触れるものだったときに捜査当局からの照会に回答しなければならないシーンがあるかもしれません。

すると、ソフトデリート（おそらく一般的にいう「論理削除」のことですよね）という扱いが適当かと思います。もちろん、お気付きの通りユーザー一覧表示のときなどにフィルタリングする必要があります。

そのほか、メールアドレスだとかユーザーネームだとかの一意性をうまく扱う（論理削除したユーザーのユーザーネームを再利用できるようにする）方法について以前記事を書きましたので紹介しておきます。

論理削除と一意性制約を両立させる方法・DB製品別

Comments

[workr]

皆さんの意見の集約した形となりますが、物理削除を避け、論理削除のためのフラグ用カラムとしてdeleted_at(TIMESTAMP)を作り、LEFT JOIN した結果投稿者がNULLとなっていた場合の処理はビュー側で行うことにしました。ありがとうございます。

Answer 2

ソフトデリート（論理削除）が有力と思います。
削除フラグはもとより、退会日まで管理しておくべきでしょう。
その上で、SELECT時に削除フラグが立っていれば、代替の名前を表示するようにするのが良いと思います。

SELECT CASE WHEN DELFLAG=1 THEN '退会済みユーザー' ELSE USERNAME END AS USERNAME ～

追記
上記の例ではわかりやすくフラグで書いてみましたが、入会日・退会日を保持し、退会日がNULLじゃなければ退会済みユーザーという管理の方がいいかもしれませんね。

Answer 3

一般的かどうか未熟者なのでわかりませんが、自分が参加したプロジェクトでは、退会したユーザーはdelフラグを立たせて（論理削除）、view側（もしくはController側）でdelフラグのあり・なしを判断してdelフラグがあれば名前表記を「退会済ユーザー」と表記するようにしていました。
よってユーザー名は退会しても保持したまま、ということです。

参考になれば幸いです。

Answer 4

投稿者情報(users)に有効なユーザーなのか、退会したユーザーなのか等のカラムを設けておく、ユーザーが退会したらusersから削除するのではなくそのカラムを使用して退会したユーザーなのか判断する、そうしておけば、今回のような場合も該当ユーザーが入会中なのか退会済みなのか判断できる、、というのがよく使われるのではないでしょうか

Answer 5

単純に正規化の問題ですね
ユーザーidを残して、退会によってユーザー名を別途「退会済みユーザー」的な
なにかに変更すればよいでしょう。

Answer 6

select 
 r.naiyo
,coalesce(u.name,'退会済みユーザー') as username
from reviews as r
left join users as u on r.userid = u.id

みたいにしてはどうですか？

また、念のためにreviewsテーブルにusernameってカラムを持たせとけば
後で仕様が変わっても（やっぱ名前だそうってなっても）問題はないかと思います。

また、ソフトdeleteの場合は、

select 
 r.naiyo
,coalesce(u.name,'退会済みユーザー') as username
from reviews as r
left join (select * from users where deleteflg =1) as u on r.userid = u.id

とする程度で同じ出力になります。

Comments

[yuba]

ttyp03さんの回答にも共通することですが、SQL問い合わせで日本語のメッセージを生成するのはお勧めできないやり方です。

簡単な話、サービスの多言語化ができなくなりますし、文章表現やUIといった層の処理をデータ処理の層に混ぜ込んでしまうのは密結合といってメンテナンス性を落とす要因です。