Webアプリ開発において退会ユーザーの取り扱い（データベース）

Question

Webアプリを開発する上で退会ユーザーの取り扱いについて、
データベースの構成としてどのようにするかで悩んでおります。
なお、データベースはMySQLです。

** テーブル **

• user（全ユーザーテーブル）
• deleted_user（退会ユーザーテーブル）

** 仕様 **

• ユーザー登録はメールアドレス（一意のID）とパスワードで管理

-> 同じメールアドレスでは登録できない

• ユーザーが退会した場合はデータベースでis_deletedカラムで退会フラグを立てて管理
• ユーザーが退会した場合はメールアドレスのカラムを空にして、再度登録できるようにする
• ユーザーが退会した場合はdeleted_userテーブルに登録

-> その時、Userテーブルの外部キーとしてuser_idを格納する
-> メールアドレスもdeleted_userに格納

• 退会ユーザーが再度登録するときは新規ユーザーとみなします（以前の情報は引き継がれない）
• 退会ユーザーテーブルの役割としては、アクティブユーザーと退会ユーザーの切り分けをしやすくするためです。

また、退会理由と要望を記述してもらったときに別テーブルの方がnullカラムが存在しなく良いと判断した為です。

###問題点
メールアドレスが2つのテーブルに存在することになるので情報管理が少々ややこしくなるのではないかと危惧しています。

###聞きたいこと
ユーザー情報をメールアドレスの一意管理の場合はこのようなテーブル構成以外に
どのような構成が適切でしょうか？

アドバイスをいただければと存じます。
宜しく御願い致します。

Comments

[takito]

「退会ユーザーテーブル」の用途を教えてください。また、退会者が再登録するときにシステムとしてどのように振る舞いたいか（あくまでも新規なのか、出戻りとして認めるのか）も補足していただくと、よりアドバイスが得られやすいと思います。

[sequence]

ご指摘ありがとうございます。質問文に追記致しました。ご確認いただければと思います。

Answer 1

すでにＢＡが付いてますが、質問した手前私なりの考えも書いておきます

私は、ユーザー管理は一元であるほうがよいと思います

アクティブユーザーと退会ユーザーの切り分けをしやすくする

そうなることのメリットはなんでしょう？と考えると、クエリひとつでどちらのリストも取り出せるので「人間がわかりやすい」という点だけのような
むしろ、テーブル間の整合性を意識しなければならず手間が増えるように思います

ykt68さんのおっしゃるように、退会以外の状態が増えたらテーブルを増やすのか・・・と考えると、状態情報を入れたカラムを持つ方がよさそうです

退会理由と要望を記述してもらったときに別テーブルの方がnullカラムが存在しなく良い

「退会理由」や「要望」という情報は、ある個人に必ず紐づいておくべき情報でしょうか？
逆を言えば、「あなたあのときこういう理由で退会してこんな要望を言ってましたよね」という確認作業が発生しますか？
システム的にそういう機能がある、のであればおっしゃるような管理でよいと思います

あまりそういう機能を見たことは無いので、私の感覚的にはそれこそ別テーブルとして退会日、年齢、性別、地域などの「有効利用するために必要となりそうな情報」と一緒に保存し、メールアドレスのような特定情報は残さなくてよいと思います

あと個人情報保護の観点で

-> メールアドレスもdeleted_userに格納

メールアドレスが特定個人を表す情報になっていないなら、個人情報となりえないですが、個人が特定できてしまう形式のアドレスの場合もあり性別や地域などと組み合わされば個人情報となりえるので、退会後にメールアドレスをどこかに残しておくのはあまりよくないと思います
（退会時には通常、ユーザが登録した情報や履歴等の完全な削除が望ましい）

以上ご参考まで

Comments

[sequence]

ご回答ありがとうございます。
質問をしていただいたのに、早々にBAを決めてしまい申し訳ありません。

> そうなることのメリットはなんでしょう？と考えると、クエリひとつでどちらのリストも取り出せるので「人間がわかりやすい」という点だけのような
むしろ、テーブル間の整合性を意識しなければならず手間が増えるように思います

なるほどです。
当初はuserテーブルとdeleted_userテーブルの整合性を取ろうと考えておりましたが、
次の指摘を受けて、その必要もないと感じました。

> あまりそういう機能を見たことは無いので、私の感覚的にはそれこそ別テーブルとして退会日、年齢、性別、地域などの「有効利用するために必要となりそうな情報」と一緒に保存し、メールアドレスのような特定情報は残さなくてよいと思います

確かに、わざわざ退会したユーザーを特定する必要がないですね。
takitoさんがおっしゃっているように有効活用できるデータを確定させて、
それと共に保存しておく方法がよいかもしれませんね。

> メールアドレスが特定個人を表す情報になっていないなら、個人情報となりえないですが、個人が特定できてしまう形式のアドレスの場合もあり性別や地域などと組み合わされば個人情報となりえるので、退会後にメールアドレスをどこかに残しておくのはあまりよくないと思います

こちらに関しては、やはり特定出来る情報を他のテーブルに残すのはよろしくないのですね。
今一度、実現したい要件を明確にし、テーブル設計をおこないたいと思います。

ありがとうございました。

Answer 2

こんにちは。

以下の(1)、(2)は、質問文から要件を想像したうえでの
「私ならこうするかも」
という案の一つに過ぎませんので、あくまで、ご参考に留めていただければと思います。

(1) user テーブルにそのユーザーの状態を示すカラムを追加

ユーザーの状態として、現状では
・アクティブ
・退会済
の２つしかないのかもしれませんが、将来的に、増える可能性はないでしょうか？

たとえば今後、サイトのフォームか何か経由で新規に登録してきたユーザーを、
システム側として正規のユーザとみなす前に、何らかの審査をすることになった場合、
・審査中
という状態が増えるかもしれません。

このようなことも想定して、user テーブルに status という整数型のカラムを追加して、
statusカラムの値をユーザーの状態に応じて、
・審査中: 10
・アクティブ（審査済）: 20
・退会済 : -1
といったように、私なら設計するかもしれません。
この場合、退会済を示すのに、is_deletedカラムに1を入れる代わりに、status に -1 を入れます。

なお上記の説明で、10,20,-1といった数字は思いつきで根拠はなく、実際には、
このような整数型のstatusカラムを追加する場合、ユーザーの取り得る状態名
（アクティブ、退会済、など）と、対応する値の決め方について、どうするのが分かり
やすいか一考の必要があるでしょう。

また上記の意図のstatusカラムを採用する場合、整数型でなくても
・審査中： UNDER_EXAMINATION
・アクティブ： ACTIVE
・退会済： CANCELED
といった文字列が入るのでもよいかと思います。

(2) 退会ユーザーのためのテーブル

ユーザーのマスターテーブル user の他に、退会ユーザーテーブル deleted_user を
別途設ける目的として、退会処理の履歴を保存しておくことを想像しました。
（何らかのハッキング的な意図で、同じメールアドレスで、登録と退会を５回も１０回も
繰り返すユーザーもいるかもしれませんし。）

したがって、deleted_userテーブルは、それが履歴であることが分かるように、
deleted_user_history のような名前にして、カラムとしては、

・id: 整数（主キー、AUTO_INCREMENT）
・user_id: ユーザーID(メールアドレス)
・date: (退会処理日時)

のようにするかなと思います。

以上参考になれば幸いです。

Comments

[sequence]

ご回答ありがとうございます。
ステータスはエンハンスを考えたらintやvarcharにしておくのがbetterかもしてないですね。

また、自分の中でdeleted_userテーブルは履歴の要素は含んでいましたが、
ハッキングを発見し易くするという観点はなかったです。

一旦、テーブルはわけて考えてみようと思います。

ありがとうございました。

Answer 3

ユーザー登録はメールアドレス（一意のID）とパスワードで管理
-> 同じメールアドレスでは登録できない
ユーザーが退会した場合はデータベースでis_deletedカラムで退会フラグを立てて管理

PKが別にある（もしくはauto_incでつくる）として、同じメールアドレスでは登録できない（但しis_deleteのフラグが立っていないものがある場合に限る）とすればアクティブユーザー同士でメールアドレスは被らないかと思います。

ユーザーが退会した場合はメールアドレスのカラムを空にして、再度登録できるようにする

上記理由から空にする必要もなくなると思います。

ユーザーが退会した場合はdeleted_userテーブルに登録
-> その時、Userテーブルの外部キーとしてuser_idを格納する
-> メールアドレスもdeleted_userに格納
退会ユーザーが再度登録するときは新規ユーザーとみなします（以前の情報は引き継がれない）

これらは必要なくなるor解決できると思います。

退会ユーザーテーブルの役割としては、アクティブユーザーと退会ユーザーの切り分けをしやすくするためです。

is_deleteフラグがあるのでテーブルが分散するよりアプリとしてはすっきりすると思います。

勘違いしていたら申し訳ないです・・・。

Comments

[sequence]

ご回答ありがとうございます。
大変申し訳ございません。
deleted_userテーブルに分ける理由として、もう一つございました。
＝＝＝＝＝
退会理由と要望を記述してもらったときに別テーブルの方がnullカラムが存在しなく良いと判断した為です。
＝＝＝＝＝
ということです。

これを加味しても、一緒にしてしまった方がよろしいでしょうか？

お手数おかけ致しますが、宜しく御願い致します。