如何设计支持多身份认证源的Web应用用户数据库架构？

这是个特别常见的场景，像Stack Overflow这类平台都是这么玩的！我给你一套灵活又简洁的数据库Schema设计方案，完全适配你的需求：

我推荐用两张表来实现：一张存储你应用的核心用户信息，另一张专门管理用户和各身份提供商的关联关系。

1. 用户主表 users #

这张表只存你自己应用需要的核心用户数据，完全不碰密码相关内容：

• id：主键（推荐用UUID，比自增ID更安全，避免暴露用户规模）
• email：可选（但建议保留，可用于账号合并逻辑，需加唯一约束）
• display_name：用户在你应用内的显示名称（可从第一个关联的身份源同步）
• created_at：用户账号创建时间
• updated_at：用户信息更新时间

2. 身份关联表 user_auth_providers #

这是实现多身份源关联的核心，用来记录用户和每个身份提供商的绑定关系：

• id：主键
• user_id：外键，关联 users.id（多对一关系：一个用户对应多个身份源）
• provider：枚举类型（比如 'google', 'facebook', 'twitter'），明确标识身份提供商
• provider_user_id：身份提供商返回的用户唯一ID（比如Google OAuth返回的sub字段）
• email：可选，存储该身份源返回的邮箱，用于核对账号归属
• access_token：可选，存储身份提供商的访问令牌（如果需要后续调用其API，比如获取用户头像）
• refresh_token：可选，用于刷新过期的访问令牌
• expires_at：令牌过期时间
• created_at：关联记录创建时间

关键约束：给 provider 和 provider_user_id 添加联合唯一约束，避免同一个用户在同一个身份源下重复绑定，也防止不同用户绑定同一个身份源账号。

场景1：用户首次通过Google登录 #

1. 用户点击Google登录按钮，跳转到Google OAuth授权页，授权成功后返回provider_user_id、邮箱、显示名称等信息
2. 你的后端查询user_auth_providers表，用provider='google' + provider_user_id=xxx查找是否存在关联记录
3. 如果不存在：
   • 在users表创建一条新用户记录，用Google返回的显示名称和邮箱填充
   • 在user_auth_providers表创建关联记录，绑定新用户ID和Google的身份信息
4. 如果存在：直接通过关联的user_id找到对应用户，完成登录流程

场景2：用户已有Google账号，再关联Facebook #

1. 用户在应用设置页触发Facebook OAuth流程，授权后返回Facebook的provider_user_id和邮箱
2. 后端先检查user_auth_providers表：
   • 如果该Facebook账号未绑定任何用户：用当前登录的user_id创建新的关联记录
   • 如果该Facebook账号已绑定其他用户：提示用户“此账号已绑定到其他账户”（可选支持账号合并，比如通过匹配邮箱来合并）

• 用UUID作为users.id：避免自增ID暴露用户数量，更适合分布式系统
• 账号合并逻辑：如果users表的email字段加了唯一约束，当新身份源返回的邮箱已存在时，可以提示用户是否合并到已有账号
• 令牌存储：如果不需要调用第三方API，可省略access_token、refresh_token等字段，减少存储开销
• 主身份源标识：可以给user_auth_providers加is_primary字段，标记用户默认的登录身份源，方便后续在界面展示

内容的提问来源于stack exchange，提问作者Magic Internet Money