はじめに
email & twitterなどのsocialアカウントでのログインの実装をしようと思ったのですが，各サービス用の認証情報のためのRDBのスキーマをどう設計するかで詰まりました． 現状のベストプラクティスなどを探してみたのですが，あまり情報が見つからなかったので，自分がやった方法を備忘録としてまとめます． 一旦，emailとtwitterでの認証を用意するという前提で設計しました．
結果
いきなりなのですが，以下が最終的に決定したスキーマになります
userテーブルが基本的なユーザ情報を格納するテーブルです．
認証情報は，サービスごとにuser_auth_serviceというuserのidを外部キーとして持つテーブルを作り，そこに入れるという感じです．
userがどの方法で認証を行うかはuserテーブルの中に保存しておきます．
メリットとしては以下があるのではと考えています
- 新たな認証方法を追加しやすい
- 新たな認証用のテーブルを追加するだけでよく，既存のテーブルに変更を加える必要がない（と思う）
- ユーザの退会がしやすい
- ユーザの退会の際にユーザ情報を消そうとすると外部キー制約に引っかかりますが，この方法ならその影響を受けずに認証に関わる部分を一発で消せます
一方で以下のデメリットがあるような気がします
- 複数の認証情報を持つようなケースに対応しづらい
- emailと電話番号両方というようなケースに対応する場合はuser_auth_multiとかのテーブルを作るかauth_methodを増やすとかになるんでしょうか．．．
- userと認証情報をjoinして列挙したりしづらい
- デッドロックしないように配慮が必要そう MySQL 外部キー制約のデッドロック | 優技録
検討したけど無理そうだった方法
サービスごとの認証情報を同じテーブルで持つ方法
php - Database structure for social login implementation? - Stack Overflow
認証情報を同じテーブルで持ち，providerというカラムでどのプロバイダ用のものかを判定する方法です
以下のような方法で微妙そうだなとなりました
- EAVはアンチパターン
- providerごとにキーの性質が違うときつい
- providerごとのキーがバッティングしない可能性がゼロではない
- ユーザがどの方法で認証するかを判定するためにレコードを走査する必要がある
userテーブルにサービスごとのカラムを付け足す方法
空白箇所が増えるなど無駄が多そうなのでやめました
サービスごとにユーザのテーブルを作る方法
外部キー制約をかけるのが辛かったりユーザの列挙が辛かったりその他様々な理由でダメそうでした
最後に
今は良さそうと思っていても，実装するうちに致命的な欠点などが見つかりそうで怖いです
また，サービスのアーキテクチャはよく公開されているのを見かけるのですが，長く運用されているDBのスキーマの変遷の歴史や，ベストプラクティスはあまり見ないので，とても気になるなぁ〜と思いました
