そーだいなるらくがき帳

そーだいが自由気侭に更新します。

ユーザ情報を保存する時のテーブル設計

はじめに

※この発言は個人の見解であり、所属する組織の公式見解ではありません

用法用量を守り、個人の責任で業務に投入してください

要件

User情報を保存するときにどのようなテーブル設計を行うか

今北産業で頼む

  • テーブルに状態を持たせず状態毎のテーブルを作る
  • 状態が変わればレコードを消して別のtableに作る
  • tableの普遍的な情報は別に持たせる

僕の考えた最強のDB設計

PostgreSQLをベースの雑なER図を作った。 これを元に話を進める。

table構成

users

親tableであり、すべてのユーザはここに属する。 基本はINSERTのみでUPDATE、DELETEを考慮しない。

user_detail

userに付随する詳細の情報がここに登録される。 一般的にusersにカラムを増やしたいような内容はここに登録する。 なぜusersにカラムを増やさないのか？ それはusersは親tableであり、親tableの更新は常にデッドロックのリスクがあるから。 こちらはMySQLの例だが外部キー制約とデッドロックについては下記の話がわかりやすい

※ 2023/01/30 元のサイトがなくなってしまったので以下のリンクはWebアーカイブです。

web.archive.org

もちろん子tableの更新の際に適切に親tableのxlock(排他ロック)を取ればデッドロックは防げる。 それではトランザクションの管理が1段階複雑になるし、そもそも適宜トランザクションを利用する必要がある。 しかしここでは適宜トランザクション利用することは人類には難しいという大前提で話をする。 トランザクションを適宜発行することが難しいと考えた場合、子tableのみを更新するようにすれば子tableのトランザクションだけを意識すれば良い。 特定のtableのみを1回更新する処理であればRDBMSが適宜対応してくれるのでロックをとる必要も無い。 その他の項目については自分たちに必要なカラムを追加してくれれば良い。

propertiesとoptionについては今回の本質ではないが説明する。 この2つのカラムはJSONB型を採用している。 PostgreSQLの性質として式INDEXの機能があるため参照の速度はJSONB型を利用しても問題ない。 これにより簡単にスキーマレスの設計を実現することができる。 ただし大きな問題点として 部分更新に弱い と 値に対する制約が弱い いう問題がある。 出来ない ではなく 弱い と表現した理由は部分更新も値に対する制約もjsonb_replaceや || などで行うことができるし（ともに9.5から）CHECK制約を駆使すれば値に対する制約もすることは可能。 あくまで可能なだけで部分更新や値に対する制約が必要な場合は適切にカラムに切り出し、または正規化を行った方が良い。

propertiesは主に住所や電話番号など 本来は必ず登録するユーザ情報 を想定している。 本当に必ず登録するならカラムに切り出し、NOT NULL制約をつけるべきである。 しかし我々は楽がしたい、そんな時の逃げ道としてこういう使い方もあるという話である。 ただし、私がインターネットで相談を受けたら 絶対にカラムに分けましょう と回答するが実際にはJSONを入れたいときは使うかもしれないと言う例だ。 あくまで例として紹介したが10回中9回はカラムにしたほうがいい。

optionはその名の通り 必須ではない項目 である。 本来のスキーマレスなJSONB型を使いたいケースは多くはこちらのケースだ。 しかし本来、そのようなケースであれば下記のようなuser_optionsを作るべきだ。

user_id option_name value
1 仕事1 格ゲー
1 仕事2 篠崎愛
1 趣味 プログラミング

しかしこれは EAV (エンティティ・アトリビュート・バリュー) と呼ばれるアンチパターン であることは気をつけなければならない。

qiita.com

クロス集計（俗に言うPivot）はOracleDBのような関数としてはPostgreSQLにもMySQLにも無く（SQL標準では定義されてるけど）CASEやGROUP BYで とても頑張る 必要がある。 一応、PostgreSQLの拡張である tablefunc を有効化すればクロス集計をSQLで書くことは出来るが簡単ではない。 そこで設計の選択肢としてJSONB型を検討するのは妥当な選択肢の一つとなるのだ。 このような設計は自分は実際に何度か採用したことがある。 この違いを紹介するために敢えてpropertiesとoptionを紹介した。

user_token&user_auth_logとuser_active&user_leave

user_token&user_auth_logはよくあるtableである。 このようなtableの際に問題になるのはusersのレコードを削除すると外部キー制約としてこれらのtableが紐付いているため、usersのレコードを削除出来ないことだ。 そのため、読者のみんなも泣く泣く外部キー制約を外したり、usersに delete_flag というカラムを追加したことがあるのではないだろうか？ それを防ぐための仕組みがuser_active&user_leaveである。 これはつまりユーザの状態に合わせてtableを作り、状態が変わればtableを移行させてやることで対応することができる。 SQLやシステム的にも有効なアカウントを見るときはuser_activeだけを見れば良い。 必要な情報は多くはuser_activeとuser_detailをJOINすることで実現するだろう。 JOINのコストはお互いに主キーなので多くのケースは1対1だ。 ここが今回の本質である。

追記（2018/05/01 20:57）

まさにそのとおりだが説明してなかったので追記する。 多くのケースはusersにレコード作った場合に、その後はuser_activeに登録される。 ただしユーザ登録にメール認証などがある場合は最初はuser_tmpにレコードが作られ、認証が終わったらuser_activeにレコードを移動させる。 このようにactive、leave以外の状態が新たに増えた場合は user_状態名 のテーブルを新たに追加する。

この設計の問題

この設計は状態毎にtableができるが親tableのusersは基本的に減ることが無い。 そのためusersはどんどん肥大化していく。 そしてusersをいざ消したいというときに関連する子tableが多いので削除の手順が煩雑になりがちでusersにdelete_flagカラムを作るという本末転倒な状況が稀によくある。 これの対策としては外部キー制約のCASCADEを利用することだが多くの場合はlog系のtableを削除する時に処理時間がかかりシステムを圧迫する。 1段階踏み込んだ対策は1対1のtableのみCASCADEし、log系のtableはRESTRICTにしておき、先に消すか SET NULL を設定する方法だ。 どちらも銀の弾丸とは言い難く、logは別途残したい場合は同じテーブル構成のoldスキーマなどを作ってそちらに移すなど運用になってしまう。 usersを消したいという要望は多くはサービスが大きくなっており、シャーディングを検討するフェーズだったりするので更に問題が大きくなる。 この場合の答えはケースバイケースとしか言いようがないでここでは割愛するがこのような問題はあるため、類似のケースで例えばissueやBlogのような場合で親tableが肥大化する場合は最初に検討したほうがいい。

その他

今回は取り扱っていないがありそうなケースを簡単に補足する

user_detailの更新履歴を取りたい

user_detail_old_logという名のtableを作ってそちらに前回の情報をINSERTしてからuser_detailを更新する。 この場合はuser_detailにもidをつけてuser_detail_old_logは子tableにしたほうが扱いやすいケースが多い

usersのカウントが重い

SELECT count(*) が重い案件。 トリガーでサマリーテーブルを作る。

yoku0825.blogspot.jp

activeやleaveなどの状態別も同様である。 トリガーを使うかどうかには諸説ある。

ざっとだが書いた。 読者の皆様のご意見を待っている。

ありがたいご意見をもらった（2018/05/01 21:33）

概ね指摘は次のようなもの。

状態が排他に制御できない

状態の変更履歴が持てない

パフォーマンスの問題

詳細は各Tweetのリプ履歴とか見るとめちゃめちゃ参考になる。 フィードバックは尊い（圧倒的感謝

追記（2018/05/02 12:44）

退会した人のデータは物理削除してほしい

説明を割愛してたけど削除はすべき。

背景

昼間