一昔前のユーザー登録はusernameとpasswordを入力して貰い、DBのデータと一致していればログイン成功の判定を出してましたが、
今風のWebサービスはgithub、googleアカウントログインなどの外部認証サービスを使用することも少なくありません。

この記事では、そういった外部認認証を使用することが前提のユーザーテーブルの設計を軽く紹介します。

ベーシックのユーザーテーブル構成

usernameとpasswordを使用して認証する、一番ベーシックなテーブル構成、外部認証の使用は想定しない。

テーブル: users

id: 主キー
username: ユーザーネーム
password: パスワード
register_time: ログイン時間

id	username	password	register_time
1	Tomas	12345	1636800366716
2	peter	654321	1636800316716

改良版ユーザーテーブル構成

usersテーブルはベーシックのままで、外部認証用のテーブルを追加します。

github認証用のテーブル: github_auth

id: 主キー
user_id:ユーザーテーブルid
uid: githubのID
access_token: 外部認証用のtoken
access_expire: tokenの有効時間

id	user_id	uid	access_token	access_sxpire
1	1	12345	1636800366716	1636800366716
2	2	654321	1636800316716	1636800316716

以上のテーブル構成は外部認証の条件には満たしますが、
認証パターン増やすたびに、それに使用されるテーブルも増えていくため、非常に面倒です。

OAuth2.0対応ユーザーテーブル構成

ユーザー基本データ管理用のテーブル: users

id: 主キー
nickname: 表示名
avatar: 表示アイコン

認証データ管理用のテーブル: user_auths

id: 主キー
user_id: usersの外部キー
identity_type: ログインタイプ(メール、 Githubなど)
identifier: 該当ログインタイプの識別子(メールアドレス、 githubユーザー名など)
credential:クレデンシャル(外部サービス発行されたtoken,認証コードなど)

この構成の特徴は、usersテーブルに認証関連のデータを持たない、表示用のニックネーム、アイコン情報などの基礎情報のみ保有します。
認証関連の全てのデータはuser_authsに保管されます、usersテーブルとは１対多の関係になります。

users

id	nickname	avatar
1	Tomas	avatar_url
2	peter	avatar_url

user_auths

id	user_id	identity_type	identifier	credential
1	1	email	tomas@example.com	password_md5
2	1	phone	1234567890	password_md5

ユーザーからログインのリクエストが来た際に、まずはログインタイプを判断をします。
携帯番号登録を例に挙げると、select * from user_auths where identity_type='phone' and identifier='携帯番号'、もしデータ存在する場合、認証コードとデータのcredentialと比較し、一致すれば、user_id使ってusersからデータ取得して返します。

github認証使用する場合、select * from user_auths where identity_type='github' and identifier='github_id',もしデータあればログイン成功し、新しいtokenで更新します。